Custom MySQL query?

  • Resolved Sharon

    (@shpemu)


    9 years, 3 months ago

    I am doing the best I can to get a good query in here, and I cannot get the query to understand repeating events. Here’s what I have so far:

    <?php
global $wpdb;
$start_time = current_time('timestamp');
$end_time = strtotime('+'.$showdays.' days', $start_time);
echo '<p>'.date('m/d/Y', $start_time).' - '.date('m/d/Y', $end_time).'</p>';

$sqlquery = 'SELECT * FROM '.$table_prefix.'ai1ec_events, '.$table_prefix.'posts
	WHERE '.$table_prefix.'ai1ec_events.end BETWEEN '.$start_time.' AND '.$end_time.'
	AND '.$table_prefix.'ai1ec_events.post_id = '.$table_prefix.'posts.ID
	ORDER BY '.$table_prefix.'ai1ec_events.start ASC';
$dates = $wpdb->get_results($sqlquery) or die(mysql_error());
//$wpdb->show_errors();
//$wpdb->print_error();
?>

    $showdays is the number of days that can be set from another screen. The default is “14”, so this code is asking that the page show 14 days of events starting today.

    There is a column in the table called “recurrence_rules”. I’m trying to understand how to incorporate this, since the query above ignores anything that repeats (the first instance is shown, so if the first instance is before today, yet another one happens within the 14 days I’m asking for, it gets ignored). A sample of the content of this column, IF it has anything, is this: FREQ=WEEKLY;WKST=SU;BYday=SU;UNTIL=20150907T235959Z;

    https://www.ads-software.com/plugins/all-in-one-event-calendar/

Viewing 15 replies - 1 through 15 (of 26 total)
  • Thread Starter Sharon

    (@shpemu)

    Anyone have any help they could offer on this?

    Thread Starter Sharon

    (@shpemu)

    I found the answer. If anyone is interested, let me know.

    Hello Sharon
    I’m looking for a way to get something similar.
    I would like to show the events with one tag on the homepage with a custom design.

    I found the solution on github but I can’t get to show the thumbnail image for the event.

    https://gist.github.com/lukaspawlik/045dbd5b517a9eb1cf95

    lukaspawlik – ai1ec-sample-usage.php

    the article show how to make a custom query to show events from all in one event calendar

    With this article I get it working but I would like to get the thumbnail image or the image from the post (but not all the content)
    I do not want to show all the content.

    I try to get help on the support forum from all in one event calendar but nobody help to me.
    I’m looking a lot on google also but the best information is on the lukspawlik post but I can’t get to show the image.

    I want to make it to show some post on the homepage.
    I get to show the tittle, date… but not the image

    Also I’ve seen the link for the available properties
    https://gist.github.com/lukaspawlik/c4a0e605414542e844dd

    Have you got to show the thumb image?

    Thread Starter Sharon

    (@shpemu)

    First, make sure you have support for featured images turned on in your theme. Check your functions.php file to see if this line is in there:

    add_theme_support( 'post-thumbnails' );

    See this link for more info.

    In your template ai1ec-sample-usage.php, you should have this line:

    <?php the_content(); ?>

    Wherever you want the thumbnail image, do this:

    <?php if ( has_post_thumbnail() ) {
    the_post_thumbnail();
} ?>

    There are specific sizes you can generate through the functions.php file as well, and then you can add more customization inside the parenthesis for this hook. If you would like more help, please feel free to reply here or message me privately.

    Thanks Sharon
    I have worked with your solution but it does not work for me.
    I have on my functions file on the theme support for featured images and I work with it on my diferent templates but I think this is not the way to get the post thumbnail from an event.

    I think it work on other way on all in one event calendar

    on the article where I get to show the post it uses:

    foreach ( $dates as $date ) {
		foreach ( $date['events']['allday'] as $instance ) {
			$post_title   = $instance->get( 'post' )->post_title;
			$post_name    = $instance->get( 'post' )->post_name;
			$post_content = $instance->get( 'post' )->post_content;
			$instance_id  = $instance->get( 'instance_id' );
			$content     .= '<div class="hp-event">
				<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
				.$post_title.'</a><br />'
				.$post_content.
				'<a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
			</div>';
		}

    this is the article
    https://gist.github.com/lukaspawlik/045dbd5b517a9eb1cf95

    and this is the AI1EC Event properties

    https://gist.github.com/lukaspawlik/c4a0e605414542e844dd

    but I can not get the thumbnail image

    Thread Starter Sharon

    (@shpemu)

    Can you supply an example of where this is, in action? Do you have a sample page?

    Can you supply the code for the complete template file?

    Sorry but i’m working it on local mode
    But this is the way:

    I create a file named ai1ec-sample-usage.php
    and I insert it on my theme folder

    on this file I insert this code:

    <?php
// inicio funcion eventos en home
function ai1ec2_display_events_func( $atts ) {
	global $ai1ec_registry;
	$content         = '<div id="content2"><img src="/wp-content/themes/scio/images/events-header.png" alt="" />';
	$time            = $ai1ec_registry->get( 'date.system' );
	// Get localized time
	$timestamp       = $time->current_time();
	// Set $limit to the specified categories/tags
	$limit           = array(
		//
		// this is demo data - please use your own filters
		//
		'tag_ids' => array (2368)
	);
	$events_per_page = 10;
	$paged           = 0;
	$event_results   = $ai1ec_registry->get( 'model.search' )
		->get_events_relative_to(
			$timestamp,
			$events_per_page,
			$paged,
			$limit
		);
	$dates = $ai1ec_registry->get(
			'view.calendar.view.agenda',
			$ai1ec_registry->get( 'http.request.parser' )
		)->get_agenda_like_date_array( $event_results['events'] );
	foreach ( $dates as $date ) {
		foreach ( $date['events']['allday'] as $instance ) {
			$post_title   = $instance->get( 'post' )->post_title;
			$post_name    = $instance->get( 'post' )->post_name;
			$instance_id  = $instance->get( 'instance_id' );
			$content     .= '<div class="hp-event">
				<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
				.$post_title.'</a><br /><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
			</div>';
		}
		foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br /><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div>';
		}
	}
	$content .= '</div>';
	return $content;
}
add_shortcode( 'display_events2', 'ai1ec2_display_events_func' );
// fin funcion eventos en home
?>

    after I create a page template on my theme and I use it to make on wordpress a page,

    on the page template I insert

    <div class"own-event">
           <?php include 'ai1ec-sample-usage.php'; ?>
            <?php
echo do_shortcode( '[display_events2]' );
?>
</div>

    the on the fronend I can see the result:

    this is one event title -Madrid-
    read more
    this the second event title -Madrid-
    read more
    this the 3 event title -Barcelona-
    read more
    this the 4 event title -Madrid-
    read more
    this the 5 event title -Barcelona-
    read more

    And I would like to show also the post_thumbnail

    Thread Starter Sharon

    (@shpemu)

    In the page template, I would think you would be able to use the thumbnail snippet of code I gave you previously. It will need to be inside a standard WP Loop though, I think. Sorry I can’t be of much more help, but I’ll think on this some more and get back to you if I can come up with some solution.

    Thread Starter Sharon

    (@shpemu)

    Wait, see if this will work:

    Set the post ID to $post_id inside your function. Then, use this:

    <?php echo get_the_post_thumbnail($post_id, ‘single-post-thumbnail’); ?>

    Thanks Sharon
    I’m working with your solution but I do not get nothing yet.

    you say “Set the post ID to $post_id inside your function”
    I’m not sure where I have to make that.
    I continue trrying with your solution to see if I get it.

    On my other proves…

    On the function the code get the elements like this

    $post_title = $instance->get( 'post' )->post_title;

    also I try with

    $singleee = $instance->get( 'post' )->single-post-thumbnail;

    or

    $singleee = $instance->get( 'post' )->the_post_thumbnail;

    But I do not get nothing. I think that the problem is how all in one event calendar get the image.

    I continue trying to get it.

    Thread Starter Sharon

    (@shpemu)

    If I read the AI1EC Event properties correctly, you should be able to do this:

    $post_id = $instance->get('post')->post_id;

    See if that works.

    if I insert
    $post_id = $instance->get('post')->post_id;

    and after I insert
    get_the_post_thumbnail($post_id, 'single-post-thumbnail');

    it does not show nothing

    if I insert $post_id to print the post_id it does not show nothing

    foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$post_id = $instance->get('post')->post_id;
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br />'.$post_id.'<br /><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div>';
		}

    But if I insert instance_id it print it

    foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$post_id = $instance->get('post')->post_id;
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br />'.$instance_id.'<br /><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div>';
		}

    so I tried changing the post_id with your way get_the_post_thumbnail($post_id, ‘single-post-thumbnail’);
    and then it show to me one image but from one normal post (not all in one event calendar) and only one image. Not one image for each event.

    foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$post_id = $instance->get('post')->post_id;
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br />'.$instance_id.'<br />'.get_the_post_thumbnail($instance_id, 'single-post-thumbnail').'<a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div>';
		}
    Thread Starter Sharon

    (@shpemu)

    Did you try:

    echo get_the_post_thumbnail($post_id, 'single-post-thumbnail');

    Or did you try:

    get_the_post_thumbnail($post_id, 'single-post-thumbnail');

    They will behave differently. If you tried both, you might try this inside your function:

    $post_id = $instance->get('post')->post_id;
$thumbnail_image = get_the_post_thumbnail($post_id, 'single-post-thumbnail');

    Then, try calling this in your template:

    echo $thumbnail_image;

    If that doesn’t work, you may need to try this:

    echo $thumbnail_image[0];

    …or possibly this:

    echo '<img src="'.$thumbnail_image[0];.'">';

    I’m only making educated guesses at this point, but I do hope that helps.

    Yes. I get it. thanks a lot Sharon for your help.
    I thought I was not possible for me. thanks.

    I say what I made.
    I find that with this code I could not get the post_id

    $post_name = $instance->get( 'post' )->post_id;

    So I found that this works ok.

    $post_id = $instance->get( 'post_id' );

    also I inserted this

    $thumbnail_image = get_the_post_thumbnail($post_id, 'the_post_thumbnail()');

    and call the image $thumbnail_image

    Ok then. this is to get the title, image(original size) and read more link

    foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$post_id = $instance->get( 'post_id' );
				$thumbnail_image = get_the_post_thumbnail($post_id, 'the_post_thumbnail()');
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br />'.$thumbnail_image.'<br/><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div><br/><br/>';
		}

    and this is to get a diferent size created on functions file on the theme

    foreach ( $date['events']['notallday'] as $instance ) {
				$post_title   = $instance->get( 'post' )->post_title;
				$post_name    = $instance->get( 'post' )->post_name;
				$post_id = $instance->get( 'post_id' );
				$thumbnail_image = get_the_post_thumbnail($post_id, 'mycustomsize');
				$instance_id  = $instance->get( 'instance_id' );
				$content     .= '<div class="hp-event">
					<a href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">'
					.$post_title.'</a><br />el post id'.$post_id.'<br/>el instance id'.$instance_id.'<br />el thumbnail image'.$thumbnail_image.'<br/><a class="read-more-link"  href="ai1ec_event/'.$post_name.'/?instance_id='.$instance_id.'">read more</a>
					</div><br/><br/>';
		}

    thanks a lot to Sharon Murphy for your help and also to lukaspawlik that made this article:

    https://gist.github.com/lukaspawlik/c4a0e605414542e844dd

    7 message up you can see all the steps (only you have to change the elements that I comment on this message)

    Yabadabadoooooooooo

    Sorry. The article of lukaspawlik is this
    https://gist.github.com/lukaspawlik/045dbd5b517a9eb1cf95

Viewing 15 replies - 1 through 15 (of 26 total)
  • The topic ‘Custom MySQL query?’ is closed to new replies.