returning a single mysql result

Hi

Im having trouble with something simple and its really annoying. It seems its easy to retreive a whole array of results and process them with php but all i want is a single result and i just cannot get it to work.

I'm retreiving a row from a table containing a date, and converting it to a unix timestamp. So the result will be just the number of the time, a single result.

This is the code i have, ive tried about a hundred different combinations, but this is the one thats currently sat in my script:

				$vf_query = "SELECT UNIX_TIMESTAMP('valid_from') FROM multi_promo WHERE promo_code = '$promo_code'";
				$vf_result = mysql_query($vf_query,$connection) or die(mysql_error());
				$row = mysql_fetch_assoc($vf_result);
				$valid_from_time = $row['valid_from'];

PHP:

any helpers would be much appreciated!

cheers

 

You need to change your query slightly..
$vf_query = "SELECT UNIX_TIMESTAMP('valid_from') AS valid_from_timestamp FROM multi_promo WHERE promo_code = '$promo_code'";

And then use

$valid_from_time = $row['valid_from_timestamp'];


OR

Just change the last line to

$valid_from_time = $row['UNIX_TIMESTAMP('valid_from')'];


but I find that messier

 

Thanks for your reply. I tried this, but it still doesnt work? the variable is empty!

				$vf_query = "SELECT UNIX_TIMESTAMP('valid_from') AS valid_from_timestamp FROM multi_promo WHERE promo_code = '$promo_code'";
				$vf_result = mysql_query($vf_query,$connection) or die(mysql_error());
				$row = mysql_fetch_assoc($vf_result);
				$valid_from_time = $row['valid_from_timestamp'];

PHP:

 

Add a line after the $vf_result line to make sure the query is returning results....

echo "Found: " . mysql_num_rows($vf_result) . " rows";

PHP:

 

yep, returning 1 row.?

 

straight after the $valid_from_time = $row['valid_from_timestamp']; line... put in

 echo "Got: " . (is_null($valid_from_time)? "NULL!"  : $valid_from_time);

PHP:

There must be something in it.. unless, its null

 

Got: 0

Does that mean zero? or its got nothign in it?

 

It means the timestamp function has returned 0...which means that whatever is in that field isnt convertable to a timestamp - or its 1st of jan 1970...

If you remove the unix_timestamp bit, and see what it returns then... it will shed more light on the problem

 

Ah Ben there you are, thanks for your help on this, much appreciated!

Ive output the 2 variables ($valid_from_time/$valid_to_time), removing the UNIX TIMESTAMP functions to a url using GET, and they are:

?2010-11-01 00:00:00&2010-11-30 00:00:00

Which is exactly whats in the db!

Sio i assume somethign wrong with the conversion as you say!

code:

				$vf_query = "SELECT valid_from FROM multi_promo WHERE promo_code = '$promo_code'";
				$vf_result = mysql_query($vf_query,$connection) or die(mysql_error());
				$row = mysql_fetch_assoc($vf_result);
				$valid_from_time = $row['valid_from'];

PHP:

 

the output is the same when i perform the query in phpmyadmin - 0

 

field set as timestamp in mysql

 

This works...

mysql> SELECT UNIX_TIMESTAMP('2010-11-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2010-11-01 00:00:00') |
+---------------------------------------+
|                            1288584000 | 
+---------------------------------------+

PHP:

 

Im not quite sure what the difference is apart from the single quote marks but this code works fine

				$vf_query = "SELECT UNIX_TIMESTAMP(`valid_from`) AS valid_from_time FROM `multi_promo` WHERE promo_code = '$promo_code'";
				$vf_result = mysql_query($vf_query,$connection) or die(mysql_error());
				$row = mysql_fetch_assoc($vf_result);
				$valid_from_time = $row['valid_from_time'];

PHP:

 

So its working as it should now?

 

Yes, working as it should now. I made sure i got the correct output directly from within phpmyadmin before adding the code to the script!

 

nice one

 

cheers for your help Ben

 

useful for me, thanks