[RESOLVED] PHP / MySQL Date Troubles

rdj · Jan 19, 2009

I'm in the process of rewriting a web site from ColdFusion to PHP. I've run into some trouble trying to match and increment dates.

I have a table called 'TourShows' which hold data about live shows for a band. The fields include the ShowID, Venue, Location, Day. The day field is what I'm concerned about.

The code as it currently is shown works, however, the query runs in a continuous loop. It appears the date is not being incremented by 1 day like I want. Basically, I'm finding what today's date is, then quering the database to see if today's date matches with a show. If it doesn't, then I increment today by 1 day, then run the query again to find a match, this is done until a match is found, then the output is displayed. The code is shown below:

<?php
// Create new date for today
	$todaycount = date('Y-m-j');
	// Set counter
	$x = 1;

// Run query until the dates match
require('../db_info.php');
while($x = 1) {
$query = "SELECT ShowID, Location, Venue, date_format(Day, '%Y-%m-%e') AS f_day1
		  FROM TourShows
		  WHERE Day = $todaycount";
$result = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

// If query returns nothing, increment $todaycount by 1 day
if(!$result) {
	$todaycount = date('Y-m-j', strtotime('+1 Day'));
}
// If query does return a result, set $x to 0
else {
	$x = 0;
}
}

// Display results of query
while($row = mysql_fetch_array($result)) {
// Assign table variables
$location = $row['Location'];
$venue = $row['Venue'];
$day = $row['f_day1'];
	echo "<div align=\"left\"><span class=\"redbgtext\"><strong>location:/strong> $location<br><br>
			<strong>venue:</strong> $venue<br><br><strong>date:</strong>
		$day</span></div>";
}
mysql_close($connection);
?>

Any help would be greatly appreciated.

bradgrafelman · Jan 19, 2009

The date standard MySQL follows is YYYY-MM-DD, not YYYY-MM-D.
There's no need to use PHP to generate a date at all, as I'll explain below.

I would rewrite the logic of your script as such:

// Only one single query is needed!
$query = "SELECT ShowID, Location, Venue, date_format(Day, '%Y-%m-%e') AS f_day1
      FROM TourShows
      WHERE Day = (SELECT MIN(DATE(Day)) FROM TourShows WHERE Day >= CURDATE() LIMIT 1)";
$exec = mysql_query($query);

You could then check to see if 0 rows were returned, which would mean that no future/current tours are scheduled.

As you can see, my example above uses a subquery to select the minimum date from the data that is either today or in the future. This requires that you are using MySQL 4.1 or above, I believe.

rdj · Jan 19, 2009

Thanks for the quick reply. However, I am getting no results. I've created a date in the table so I know there is another show in the future. The page loads and I don't receive any errors, so I'm assuming the query is executing fine but just not finding any matching dates.

Do I need to format the date in the query for the CURDATE() function? I ran a check to see if any results were returned, but even that isn't showing up:

if(!$result) {
		echo "no future shows";
	}
	else { ...rest of code }

bradgrafelman · Jan 19, 2009

Is the query being executed successfully? Try adding an 'or die(mysql_error())' onto the end of the mysql_query() call to make sure MySQL isn't returning any error messages.

rdj wrote:
Do I need to format the date in the query for the CURDATE() function?

Which date? There shouldn't be any literal date in the SQL query itself at all.

rdj · Jan 19, 2009

The query appears to be executing, already added the die call to the query. The page loads like everything is fine, all formatting is correct and all that good stuff, it's just the php code that isn't displaying. I answered my question about the CURDATE() so no worries there.

I have the Day field stored at datetime in my database so that shouldn't be an issue either and my verison of MySQL is 5.0.41 so no problems there either...

I hard coded a date into the SELECT statement and the query works fine:

query = "SELECT ShowID, Location, Venue, date_format(Day, '%Y-%m-%d') AS f_day1
     			 	FROM TourShows
      				WHERE Day = '2005-03-23'";

bradgrafelman · Jan 19, 2009

D'oh! After I posted, I fired up my test SQL server and sure enough, the SQL wasn't right.

Try this instead:

$query = "SELECT ShowID, Location, Venue, date_format(Day, '%Y-%m-%e') AS f_day1
      FROM TourShows
      WHERE Day = (SELECT MIN(DATE(Day)) FROM TourShows WHERE Day >= CURDATE() GROUP BY Day LIMIT 1)";
$exec = mysql_query($query);

Also, is 'Day' a DATE column or a DATETIME column? If it is simply a DATE column, then you could remove the DATE() call inside the MIN() call in the subquery.

rdj · Jan 19, 2009

Still no luck with the change and adding the GROUP BY. Here's the entire script as it stands, much shorter than my first version (thankfully). Perhaps I'm missing something when outputting the results of the query.

<?php
		require('../db_info.php');
		$query = "SELECT Location, Venue, date_format(Day, '%Y-%m-%d') AS f_day1
     			 	FROM TourShows
      				WHERE Day = (SELECT MIN(DATE(Day)) FROM TourShows WHERE Day >= CURDATE() GROUP BY Day LIMIT 0,1)";
		$exec = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

	while($row = mysql_fetch_array($exec)) {
	// Assign table variables
	$location = $row['Location'];
	$venue = $row['Venue'];
	$day = $row['f_day1'];
		echo "<div align=\"left\"><span class=\"redbgtext\"><strong>location:</strong> $location<br><br>
			<strong>venue:</strong> $venue<br><br><strong>date:</strong>
			$day</span></div>";
	}
	mysql_close($connection);
?>

rdj · Jan 19, 2009

I went ahead and ran the SELECT MIN(DATE(Day)) FROM TourShows WHERE Day >= CURDATE() GROUP BY Day LIMIT 0,1) in my phpAdmin and it returned the result I was looking for (the last show, 2009-04-04). So we know that is correct and will output the correct result.

Running the entire query in phpAdmin returns an empty result. No rows match...

bradgrafelman · Jan 19, 2009

Er.. I copy and pasted your code (minus the require and plus my own DB connection information) and it worked fine with my sample data.

Can you show us the output of a 'CREATE TABLE TourShows' query so that we can see the structure of this table?

Also, do you have access to any GUI database interfaces, such as phpMyAdmin? If so, what happens if you paste the query into PMA? Are any rows returned?

bradgrafelman · Jan 19, 2009

Just saw your above edit that indicates PMA returned no rows.

At this point I'm at a loss as to what's going wrong. Can you tell us what version of MySQL the server is running? It may be that it doesn't support subqueries, but it should be spitting out an error message indicating such a problem.

Either way, you could always split this into two queries; run the subquery to find the next date, and then check the results. If MySQL returned 0 rows, then output a message that tells the user that there are no current/future shows. If it does return 1 row, then take the date from that query and run a second query (with a sime 'Day = (date)' as you did above in your example, where the date comes from the result of the first query).

rdj · Jan 19, 2009

The MySQL server version is 5.0.41 and yes, if subqueries weren't supported it should spit out an error, which it doesn't.

I will split this into 2 queries and see what I come up with.

rdj · Jan 19, 2009

I've split the queries into two parts, however I seem to be having trouble referencing the $day_check variable I created. Perhaps there's an easier way of doing this and I'm not very familiar with running 2 queries on the same page w/ PHP. Guess I'm used to coldfusion where you can name each query, but it seems it doesn't work that way in PHP so I'm having trouble referencing the query.

require('../db_info.php');
		$query = "SELECT MIN(DATE(Day)) AS f_day FROM TourShows WHERE Day >= CURDATE() GROUP BY Day LIMIT 0,1";
		$exec = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

	if($exec = NULL) {
		echo "no future shows";
	}
	else {
		while($row = mysql_fetch_array($exec)) {
		$day_check = $row['f_day'];
		}
	}
	mysql_free_result($exec);


	$query = "SELECT Location, Venue, date_format(Day, '%Y-%m-%d') AS f_day1
 			 	FROM TourShows
  				WHERE Day = ".$day_check;
	$exec = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

I'm just receiving a MySQL syntax error on line 3 of the second query.

bradgrafelman · Jan 19, 2009

One problem I see immediately is this:

if($exec = NULL) {

You're not comparing anything, you're assigning NULL to $exec.

Second problem is that mysql_query() never returns NULL; it either returns a resource or FALSE (meaning the query failed). What we're more interested in is if [man]mysql_num_rows/man returns 0 or 1.

Third, there's no need for a loop - the 'LIMIT 1' guarantees that you'll either get 0 rows or you'll get 1 row. So, inside that else() statement, get rid of the while() loop and just use [man]mysql_result/man. It's not necessarily an error, but it makes more sense.

Fourth, as it stands now, the second query will be executed whether a date was found in the first query or not. Move all of the processing for the second query into the else() statement of the if/else conditional where you check if a date was found or not.

Finally, a date such as 2009-04-04 is not a number, it's a string. As such, you have to surround this value with quotes in the SQL query (the second where, in the WHERE clause) just like you would have to in PHP.

rdj · Jan 20, 2009

So I was able to get this to finally work. Was in the middle of typing out another problem and I just forgot a period somewhere! The final code is as follows, but I've a couple PHP questions if you have the time Brad:

$query = "SELECT MIN(DATE(Day)) AS f_day FROM TourShows WHERE Day >= CURDATE() GROUP BY Day LIMIT 1";
		$exec = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

	$num = mysql_num_rows($exec);

	if ($num = 0) {
		echo "no future shows";
	}
	else {
		$day_check = mysql_result($exec, 0, "f_day");
		$query = "SELECT Location, Venue, date_format(Day, '%Y-%m-%d') AS f_day1
 			 	FROM TourShows
  				WHERE Day = '".$day_check."'";
		$exec = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

		while($row = mysql_fetch_array($exec)) {
		// Assign table variables
		$location = $row['Location'];
		$venue = $row['Venue'];
		$day = $row['f_day1'];
			echo "<div align=\"left\"><span class=\"redbgtext\"><strong>location:</strong> $location<br><br>
				<strong>venue:</strong> $venue<br><br><strong>date:</strong>
				$day</span></div>";
		}
	}

How exactly are the $query and $exec variables being stored? When running the first query the result is stored in $exec...after running the second query, is the first instance of $exec overwritten by the new data? Is there a way around this by perhaps naming the second query and result $query1 and $exec1 respectively?

Second, I noticed when I remove the last upcoming show (so the first query returns 0 rows), the $num = mysql_num_rows($exec); line sets the variable to 0, however the if ($num = 0) statement isn't finding that $num is equal to 0...most likely an error on my part...can you shed any light on that?

Finally, thanks for all your help. This has been bugging me for days and finally decided to break down and find a php forum to sign up on and ask for help. Very impressed with the timely responses and knowledge, I'm sure I'll be frequenting this place quite often now

bradgrafelman · Jan 20, 2009

rdj wrote:
When running the first query the result is stored in $exec...after running the second query, is the first instance of $exec overwritten by the new data?

Yes; it's just assigning a new value to a variable. Forget about the database fanciness involved, it's simply doing:

$var = 'foo';
do_something($var);
$var = 'bar'; // $var is now 'bar', plain and simple

rdj wrote:
Is there a way around this by perhaps naming the second query and result $query1 and $exec1 respectively?

Why? Why do you care what the old query was or what the old MySQL resource was for that query's result set? Once you extract the date from the result set into $day_check, all of that is useless to you - you shouldn't need to care about it for the rest of the process.

rdj wrote:
however the if ($num = 0) statement isn't finding that $num is equal to 0...most likely an error on my part...can you shed any light on that?

Yes; you have the same problem as above. In this statement:

if($num = 0)

you're telling PHP to assign the value 0 to $num; this value is then checked using boolean logic and, since 0 translates to boolean false, the if() always evaluates to FALSE and thus the 'else' block is always executed. Try using '==' instead, which is a comparison operator, not an assignment operator. More info on operators here: [man]operators[/man].

[RESOLVED] PHP / MySQL Date Troubles

Rrdj

Bbradgrafelman

Rrdj

Bbradgrafelman

Rrdj

Bbradgrafelman

Rrdj

Rrdj

Bbradgrafelman

Bbradgrafelman

Rrdj

Rrdj

Bbradgrafelman

Rrdj

Bbradgrafelman