Finding the oldest date in a table

robives

I am tring to find the oldest date in a table where the date column name is "completed"

// find the oldest item
$query = "SELECT completed
FROM ipn_tblsaleshistory";
$result = mysql_query($query)
or die("couldn't execute query");

I have selected the table and could use a while loop but am hoping there is a more elegant way - I have tried looking up SELECT on the PHPBuilder manual using search but get the error
Not Found
The requested URL /manual-lookup.php was not found on this server.
Thanks in advance
- Rob

szz

Yes, there is a more elegant way to do this:

SELECT completed
FROM your_table
ORDER BY completed ASC
LIMIT 0,1

SELECT TOP 1 completed
FROM your_table
ORDER BY completed ASC

robives

Thanks for your help - I've tried the following

$query = "SELECT completed
		  FROM ipn_tblsaleshistory 
		  ORDER BY completed ASC
		  LIMIT 0,1";

$oldest = mysql_query($query)
      or die("couldn't execute query");

....

echo "oldest = $oldest";

I get the output

oldest = Resource id #3

any ideas what I am doing wrong?
TIA
- Rob

leatherback

Yeah,

try this:

$query = "SELECT completed
		  FROM ipn_tblsaleshistory 
		  ORDER BY completed ASC
		  LIMIT 0,1";

$result = mysql_query($query)
      or die("couldn't execute query");
$oldest = mysql_result($result);

echo $oldest;

bradgrafelman

I thought the 'row' number was a requirement for the function [man]mysql_result/man? Like so...

$query = "SELECT completed 
          FROM ipn_tblsaleshistory 
          ORDER BY completed ASC 
          LIMIT 0,1"; 

$result = mysql_query($query) 
      or die("couldn't execute query"); 
$oldest = mysql_result($result, 0); 

echo $oldest;

At any rate, you need to use one of the [man]MySQL[/man] functions to retrieve the data, [man]mysql_query/man returns a Resource, as shown in the echo().

robives

Thanks - I tried that but I get no output at all from that. I know there are plenty of items in the table so I should get something

thanks
- Rob

robives

My last post was to Leatherback, Bradgrafelman's post has solved the problem. Thanks all for your help
- Rob

bradgrafelman

No output? Let's try debugging...

$query = "SELECT completed 
          FROM ipn_tblsaleshistory 
          ORDER BY completed ASC 
          LIMIT 0,1";  

$result = mysql_query($query)  

      or die("couldn't execute query");  

echo "There were <b>" . mysql_num_rows() . "</b> rows found.<br><br>\n";

$oldest = mysql_result($result, 0);  

echo '$oldest is: ' . $oldest;

Let us know what the output is now.

EDIT: Ah, that explains it! :p Glad we could help.

leatherback

hehe,

Shouldn't use functions I normally don't use..
I hardly ever need only one item / record & mostly use mysql_fetch_array.

Thx. Brad.

Good to hear it works now.

J
:o

robives

Having extracted the date from the mySQL table I am having problems using it. I am setting up a set of drop down lists so I have

$toyear = date("Y");
$tomonth = date("m");
$today = date("d");

to set the three drop downs for the current date.
I have extracted the oldest date in the table into the variable $oldest using the code

$query = "SELECT completed
          FROM ipn_tblsaleshistory
          ORDER BY completed ASC
          LIMIT 0,1"; 

$result = mysql_query($query) 
      or die("couldn't execute query"); 
$oldest = mysql_result($result, 0);

but it doesn't work - upon examination of the variables the current date is in one format
eg 1117922400
and $oldest is in a second format
eg 2005-05-06 15:48:37

How do I convert between them? Thanks for your help :-)
- Rob

leatherback

Have a look at the date_format function in mysql for the oldest date:

select *, DATE_FORMAT(dDATECOLUM, '%W %M %Y') ...

How did you get the current date?

the date("d") should give you something from 01 to 31
J.

robives

...so this is a mySQL function?
I would use it in the SELECT statement? Is that correct - sorry to be dense, I'm new to this.

I selecetd the current date using the PHP date() function

thanks
- Rob

leatherback

Yes. The first set I showed is an sql command. So the record is converted to a yyyy - mm - dd formatted string during data retrieval from your dataset (There is a decent reference manual for mysql here)

I am surprised about the second time issue. As I said, date(d) should give you one value. Can you post the code which generated the 1117922400 value?

robives

$lastweek = mktime (0,0,0,date("m"),date("d")-7,  date("Y"));
$formatoldest = mktime($oldest);

echo ("lastweek = $lastweek formatoldest = $formatoldest");

gives the following output:

lastweek = 1117922400 formatoldest = 1125745491

I then use these in

$fromyear = date("Y",$lastweek);
$frommonth = date("m",$lastweek);
$fromday = date("d",$lastweek);

to setup some drop down lists.
the output from $lastweek works fine but if I replace $lastweek with $formatoldest the dropdowns list with an incorrect date.

robives

I found the problem thanks I have replaced
$formatoldest = mktime($oldest);
with
$formatoldest = strtotime($oldest);

thanks for your help

leatherback

I am a bit confused as to the problems you are facing.

You first tell me that date is not giving you the correct outp]ut. Now you show that you actually use it in a number of functions.

The same is probably true for the $formatoldest value.

You have to check what you get from your database query (Which format) and then determine the correct steps to get you the value you need. Since we do not know what you want to do, it is a bit difficult to say.

So maybe you can reformulate your question, in a new thread, since the question you posed in this thread has been solved, ot my opinion.

Give us:
- The data you have (resultant of the query)
- The data you need / what you want to do.