Queer database behaviour

Since you clever people at Chronoforms got me insrtucted in the Joomla way of writing a query I thought you might help.
I have a very simple table called jos_1_diary. This has just 4 'columns'
rec_id integer
theline smallint
thedate date
thecount smallint

I wantto find the last date in the diary because I want to extend it
SO I put a bit of PHP as

  $db = & JFactory::getDBO();
// first lets get current max date
  $qry = "select max(".$db->nameQuote('thedate').
         ") from ".$db->nameQuote('jos_1_diary');
   echo "qry is $qry";
   $db->setQuery($qry);        
   $result = $db->Query();  
   echo "<p>max date is $result</p>";

The result of running this code is:

qry is select max(`thedate`) from `jos_1_diary`
max date is Resource id #116

My question is what is this 'resource id' it has nowt to do with my program.

When I run exactly the same query against the table in php myadmin
I get a sensible answer of 2010-03-08

Is this a joomla nasty ? Or is my program wrong ?
--
Dave

Hi Dave,

Your query doesn't actually return any columns so I guess that the ResourceID is some kind of record reference. Try

SELECT MAX(".$db->nameQuote('thedate').
         ") AS `date` FROM ".$db->nameQuote('jos_1_diary');

Bob

Thanks Bob for your help.
I am sure the qry should be a string so needs inverted commas around it.
But I then tried with your suggestion capital letters and all.
The result was as before.
So I tried with the 'as date' put in as a joomle protectd variable as

  $qry = "select max("   .$db->nameQuote('thedate').
           ") as "         .$db->nameQuote('date').
           " from "  .$db->nameQuote('jos_1_diary');

   echo "qry is $qry";
   $db->setQuery($qry);        
   $result = $db->Query();  
   echo "<p>max date is $result</p>";

with the output as here

qry is select max(`thedate`) as `date` from `jos_1_diary`
max date is Resource id #116

the translated query is just as I think you expect...but the result is silly.
It should give a singleton result
I will now try COUNT instead of MAX to see if other singletons fail for the same reason.

Sorry !!!
Forget it.
I cannot read straight.
The rules say 'single value result use 'loadResult();'
I had it in my head that 'result' was what I wanted.

Anyway thanks for your help Bob I get these silly (senior moments) at times.
--
Dave

Hi Dave,

Yes, sorry, missed that one myself.

Bob