[SOLVED] MYSQL Select minus 6 hours, SQL working but not in PHP.

[PHPKevin]

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

[premiso]

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?

[PFMaBiSmAd]

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?

[PHPKevin]

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.

[Philip]

<?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?

[PHPKevin]

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

[premiso]

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.

[PFMaBiSmAd]

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.

[PHPKevin]

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

 

[PFMaBiSmAd]

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);

[PHPKevin]

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