PHPKevin Posted December 27, 2008 Share Posted December 27, 2008 Hi there, I have problem where I would like to check a MYSQL DB for new entries posted within the last 6 hours. The SQL script runs fine and returns the desired results. However, when all is put together into the PHP page I don't get any results. Any assistance would be greatly appreciated. Below is some of the code, "request" is the name of the DB and the second "request" is a datetime field. I am using "$time = date("Y-m-d H:i:s")" to format the datetime stamp. $sql = "SELECT DISTINCT * FROM request WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW() GROUP BY id"; $hour = mysql_query($sql); while ($noresponse = mysql_fetch_array($hour)) { echo $noresponse["email"]; } Many thanks, Kevin Quote Link to comment Share on other sites More sharing options...
premiso Posted December 27, 2008 Share Posted December 27, 2008 I would think your DISTINCT * is throwing it off. I think you need to define a single column for DISTINCT cause if you have a time field, which I know you do, any time that matches up will not be returned more than once. What column should be distinct? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted December 27, 2008 Share Posted December 27, 2008 If that exact query works when executed directly, there is probably something else wrong in your php script. Use some error checking to get mysql/php to tell you what is going on with the query - $hour = mysql_query($sql) or die('Query failed: ' . mysql_error()); And if that does not produce anything, tell us what "I don't get any results" means. Are you getting a blank page? Is echo $noresponse["email"]; outputting nothing? What does a "view source" of the page show? Quote Link to comment Share on other sites More sharing options...
PHPKevin Posted December 27, 2008 Author Share Posted December 27, 2008 Thanks for your Quick response's. I have narrowed down the DISTINCT clause as requested as well as included the suggested error checking. Unfortunately neither offered any additional info. It appears as though there is no result, which means the echo is outputting nothing. I have tested by doing a simple select * with no where clause and the desired results are displayed correctly. Only when I included the "WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW()", do I not get a result. I am using an .inc where I manage the DB connections and tests. This works 100% for the rest of the site. Below is a copy of the amended code. echo "Start"; $sql = "SELECT DISTINCT request , first_name FROM request WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW() GROUP BY id"; $hour = mysql_query($sql) or die('Query failed: ' . mysql_error()); while ($noresponse = mysql_fetch_array($hour)) { echo $noresponse["first_name"]; echo "<br>"; } Again, Many thanks. Quote Link to comment Share on other sites More sharing options...
Philip Posted December 27, 2008 Share Posted December 27, 2008 <?php echo 'Start<br />'; $sql = "SELECT DISTINCT request , first_name FROM request WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW() GROUP BY id"; $hour = mysql_query($sql) or die('Query failed: ' . mysql_error()); echo 'Num of rows: '.mysql_num_rows($hour).'<br />'; while ($noresponse = mysql_fetch_array($hour)) { echo '<pre>'; print_r($noresponse); echo '</pre>'; //echo $noresponse["first_name"]; echo '<br>'; } echo 'End'; What does output? Quote Link to comment Share on other sites More sharing options...
PHPKevin Posted December 27, 2008 Author Share Posted December 27, 2008 Hi KingPhilip, The output from your code is: Start Num of rows: 0 End The same SQL script run with PHPMyAdmin returns 2 rows: Showing rows 0 - 1 (2 total, Query took 0.0013 sec) I have been baffled by this one or a while now. Either the SQL statement gets altered somewhere or it just does not like the "WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW()". Any other select statement works except when this where clause is included. I am open to suggestions for other workarounds. In short I need to check the DB for any entries that are 6 hours old. This will be run as a cron job every hour. Cheers Quote Link to comment Share on other sites More sharing options...
premiso Posted December 27, 2008 Share Posted December 27, 2008 Why not try to query it via phpMyAdmin with the where clause in? I saw up above you said you did it without the where clause...that is why it returns 2 results. The where clause is limiting it. So run it through phpMyAdmin with that where clause and then modify it that way before you venture into getting it to work in PHP. If you cannot even get it to work in PHPMyAdmin WITH the where clause then it will never work in PHP with the where clause. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted December 27, 2008 Share Posted December 27, 2008 Post the exact query that works in PHPMyAdmin, post your table definition, and post an example of your data that should be returned by the query. Quote Link to comment Share on other sites More sharing options...
PHPKevin Posted December 27, 2008 Author Share Posted December 27, 2008 Hi PFMaBiSmAd, Below are the details you requested: PHPMyAdmin SQL that works: "SELECT DISTINCT request , first_name FROM request WHERE request BETWEEN NOW()- INTERVAL 6 HOUR AND NOW() GROUP BY id" Returned Data: request first_name 2008-12-27 09:57:36 Kevin 2008-12-27 09:58:03 Kevin 2008-12-27 12:26:55 Peter Desc request: id int(5) NO PRI NULL auto_increment first_name varchar(20) NO NULL last_name varchar(20) NO NULL email varchar(50) NO NULL number int(10) YES NULL vin varchar(20) YES NULL part_no varchar(20) YES NULL location varchar(50) YES NULL type varchar(50) YES NULL request datetime YES NULL respond datetime YES NULL supplier_id int(5) YES NULL supplier_company varchar(50) YES NULL supplier_name varchar(50) YES NULL supplier_email varchar(50) YES NULL item varchar(50) YES NULL price int(6) YES NULL note varchar(200) YES NULL Premiso, Sorry for the misunderstanding. The completed SQL works 100% in PHPMyAdmin. If I do a select without the "WHERE" then the PHP page also works as expected. Cheers Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted December 27, 2008 Share Posted December 27, 2008 Add the following two lines of code immediately after your first opening <?php tag - ini_set ("display_errors", "1"); error_reporting(E_ALL); Could you execute the following query through your php code - $sql = "SELECT NOW()"; $result = mysql_query($sql) or die('Query failed: ' . mysql_error()); $row = mysql_fetch_assoc($result); print_r($row); Quote Link to comment Share on other sites More sharing options...
PHPKevin Posted December 27, 2008 Author Share Posted December 27, 2008 Hi all, I think I have managed to find the problem. The Time Zone has been the issue all along. On the PHPMyAdmin side I was at the same Time Zone but when connecting via PHP to MYSQL I was ending up on anther time zone over 10 hours behind. This resulted in the PHP's request coming up empty while PHPMyAdmin returned the desired results. I am so sorry for the inconvenience but really grateful for all the efforts received. Many thanks Cheers, Kevin Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.