Page 1 of 1

Why is this giving me grief?

Posted: Tue Sep 08, 2009 5:57 pm
by WithHisStripes
Heya - so I'm working on this site: http://west-valley-community-church.theportlandco.com

And in the right there should be three of the upcoming events, starting with the next event. But my code below, is acting unexpected. When I set the "LIMIT" to 3, it shows nothing, but when I set it to six, it shows three. I'm confused. Can someone help?

Code: Select all

        $get_upcoming_events = mysql_query("SELECT * FROM `wp_calendar` ORDER BY `wp_calendar`.`event_begin` ASC LIMIT 3");
        
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            if(strtotime(date('l F jS', strtotime($show_upcoming_events['event_begin']))) > strtotime(date('l F jS'))) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', strtotime($show_upcoming_events['event_begin'])) . " " . date('g:iA Y', strtotime($show_upcoming_events['event_time'])) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
                
            ";
            }
        }

Re: Why is this giving me grief?

Posted: Tue Sep 08, 2009 6:20 pm
by Eric!
Is it possible that you have some old events that are showing up first in your list and then the if-then for checking the time doesn't display them? Remember the time is the server's time, where ever that is. I think you'd have to post some of your data if you want more specific help. Perhaps just bypass the if-then inside your while statement and see if that is the problem.

FYI -- you can do the event date sorting too inside the query. See:
viewtopic.php?f=1&t=105909

Re: Why is this giving me grief?

Posted: Tue Sep 08, 2009 6:25 pm
by WithHisStripes
Hey Eric,
Thanks for the reply, though I'm not sure how that helps me to understand why it shows the wrong number of posts when I set the "LIMIT" to 3? Can you explain a little for me? Thanks!

Re: Why is this giving me grief?

Posted: Tue Sep 08, 2009 6:28 pm
by Eric!
You said there is nothing when you set the limit to 3, that is probably because either the query doesn't return something or the if-then inside your while is preventing it from printing. Without seeing your data I can't narrow it down any further.

When you set it to 6 it is probably returning 6 but your if-then is filtering out 3 of them...probably the first there that were also filtered out when you had the limit set to 3 in the first case.

(note, I edited the previous response with some info on MySQL date sorting)

try it like this both ways (limit 3 and limit 6)

Code: Select all

       $get_upcoming_events = mysql_query("SELECT * FROM `wp_calendar` ORDER BY `wp_calendar`.`event_begin` ASC LIMIT 3");
        
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', strtotime($show_upcoming_events['event_begin'])) . " " . date('g:iA Y', strtotime($show_upcoming_events['event_time'])) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
                
            ";
        }

Re: Why is this giving me grief?

Posted: Thu Sep 10, 2009 7:07 pm
by WithHisStripes
Okay that was really helpful. Here's what I ended up using:

Code: Select all

        <?php
        $get_upcoming_events = mysql_query("SELECT * FROM `wp_calendar` WHERE `event_begin` >= " . date('Y-m-j') . " ORDER BY `event_begin` ASC LIMIT 3");
        
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', strtotime($show_upcoming_events['event_begin'])) . " " . date('g:iA Y', strtotime($show_upcoming_events['event_time'])) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
                
            ";
        }
        ?>
Now I'm still getting an unusual issue with the the items not appearing in correct chronological order. It should go upcoming event first descending to events further in the future. I think it has something to do with recurring events taking priority, because if I change "ASC" to "DESC" it puts things in the right chronological order backwards.

Re: Why is this giving me grief?

Posted: Fri Sep 11, 2009 12:38 am
by Weiry
would i be right in assuming that you want to display the events in ascending order, but limit to the 3 rows at the end (to show the closest date first)
you would need to set your limit in your query to account for this.

Code: Select all

$rowQuery = "SELECT * FROM `wp_calendar`";
$numRows = mysql_num_rows(mysql_query($rowQuery));
$query = "SELECT * FROM `wp_calendar` ORDER BY `wp_calendar`.`event_begin` ASC LIMIT ".$numRows-3.",{$numRows}";
$get_upcoming_events = mysql_query($query);
hopefully something like that will return your last 3 entries in the table in ascending order (which i think is closest event first?)

Re: Why is this giving me grief?

Posted: Fri Sep 11, 2009 12:25 pm
by WithHisStripes
Yeah, that's what I'm trying to accomplish. So I replace my query string with yours, and I get a syntax error. I'm not familiar with this technique can you explain a bit what's going on?
Parse error: syntax error, unexpected '"' in /home/d363f86b/public_html/west-valley-community-church/wp-content/themes/west-valley-community-church/index.php on line 66

Code: Select all

        $rowQuery = "SELECT * FROM `wp_calendar`";
        $numRows = mysql_num_rows(mysql_query($rowQuery));
        $query = "SELECT * FROM `wp_calendar` ORDER BY `wp_calendar`.`event_begin` ASC LIMIT ".$numRows-3.",{$numRows}";
        $get_upcoming_events = mysql_query($query);
                
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', strtotime($show_upcoming_events['event_begin'])) . " " . date('g:iA Y', strtotime($show_upcoming_events['event_time'])) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
                
            ";
        }