[Resolved] help with SELECT and selecting the latest date.

aron

hi all i need to print the date of last updated on my page like this

site last updated 12.30pm 17th Dec 05

now i have tables in my database

property
tblpages

these both store dates in this format

field = date

2005-04-19 10:39:41

but i want to select the latest date from each table then i want to select the latest date from the 2 records and print the above.

site last updated 12.30pm 17th Dec 05

anyone able to help. please.
cheers aron.

mrhappiness

What's the relation between those tables?

If one exists use it for JOINing them, if not use either

SELECT IF(MAX(p.datefield) > MAX(t.datefield),
  MAX(p.datefield), 
  MAX(t.datefield)) maxdate
FROM property p, tblpages t

or select the maximum for each table and compare it within php

aron

just 2 seperate tables. 1 stores content from the site (for a contenet editor) tblpages

the other stores properties from the site (houses, beds bath etc..)
property

cheers.

mrhappiness

so which method did you choose?

what does not work?

aron

hmm. i duuno. comes up with an error.

i think i got it wrong. iv never done anything with dates before.

<?
$connection = mysql_connect("localhost","username","password") or die('Could not connect to the database server');
$db = mysql_select_db("propertysql", $connection) or die ("Unable to select database.");
$sql="SELECT IF(MAX(p.date) > MAX(t.date), MAX(p.date),  MAX(t.date)) maxdate FROM property p, tblpages t"
$sql_result_dev = mysql_query($sql,$connection) or die ("Could not connect to database"); 

?>

cheers aron

mrhappiness

$sql_result_dev = mysql_query($sql,$connection) or die (mysql_error());

output?

aron

Parse error: parse error, unexpected T_VARIABLE in /home/httpd/vhosts/visionate.com.au/subdomains/propertymarketing/httpdocs/datetest.php on line 5

<?
$connection = mysql_connect("localhost","usernmae","password") or die('Could not connect to the database server');
$db = mysql_select_db("propertysql", $connection) or die ("Unable to select database.");
$sql="SELECT IF(MAX(p.date) > MAX(t.date),MAX(p.date),MAX(t.date)) maxdate FROM property p, tblpages t"
$sql_result_dev = mysql_query($sql,$connection) or die (mysql_error());
?>

cheers aron.

aron

err. alwasy something simple. i forgot the ;

ok no error comes up now.

<?
$connection = mysql_connect("localhost","username","password") or die('Could not connect to the database server');
$db = mysql_select_db("propertysql", $connection) or die ("Unable to select database.");
$sql="SELECT IF(MAX(p.date) > MAX(t.page_date),MAX(p.date),MAX(t.page_date)) maxdate FROM property p, tblpages t";
$sql_result_dev = mysql_query($sql,$connection) or die (mysql_error());
?>

how do i print the date in the format of 2005-04-19 10:39:41

cheers aron

aron

how do i print the date in the format of 2005-04-19 10:39:41

 <? 
$connection = mysql_connect("localhost","username","password") or die('Could not connect to the database server'); 
$db = mysql_select_db("propertysql", $connection) or die ("Unable to select database."); 
$sql="SELECT IF(MAX(p.date) > MAX(t.page_date),MAX(p.date),MAX(t.page_date)) maxdate FROM property p, tblpages t"; 
$sql_result_dev = mysql_query($sql,$connection) or die (mysql_error()); 
?>

cheers aron

bradgrafelman

No need for double post...

$date = mysql_result($sql_result_dev, 0);

Try that. I'm assuming you're storing your dates using the MySQL datetime format, btw.

aron

ye that works but i need to format it so it displays in this format now.

12.30pm 17th Dec 05

cheers
aron.

bradgrafelman

Look at using [man]strtotime/man and [man]date/man.

I don't want to spoonfeed you a script, but the date format you specified in your previous post would be "g.ia jS M y". See if you can't figure the rest out. If not, post here again.

aron

coo thanks i got it

Last updated <? echo date("g.ia jS M y ",strtotime($date)); ?>

Last updated 5.06pm 19th Apr 05

thanks.

cheers

bradgrafelman

Nicely done.

Be sure to mark this thread resolved if it is.

EDIT: P.S. .... Australia or UK? :p

aron

australia.

cheers
aron