[Resolved] selecting 1st and last of a query display php

[dreams4000]

Hey all,

I am wanting to do a query that is going to pull a list of dates/times.. I want to then subtract the 1st date from the last date so that I get a # of days/hrs between the two. I can do the subtract the two dates part, but my question is how do I nab the first and last dates found as the ones to subtract instead of any of the others? For example:

Oct 10
Oct 11
Oct 12
Oct 13
Oct 14
Oct 15

would all be pulled.. I want to subtract the Oct 10 from the Oct 15 date to get 5 days, but I have to have the query look at all the dates to know which dates are the earliest and which are the latest.
Not sure if this is something that would be done in php or in mysql.. any ideas? Thanks!

[dreams4000]

I have tried:

$sql2 = "SELECT ((MAX(UNIX_TIMESTAMP(checked_in)) - MIN(UNIX_TIMESTAMP(checked_out)))/86400) AND checked_in != '00000000000000' FROM sometable WHERE id = $id";

but it returns 1 as a value.. and the max checked_in is 20041008152859 and the MIN checked_out is: 20041003152859 which is 5 days difference so the value returned should be 5.. I am a bit lost.. any thoughts?

[devinemke]

if you are using mySQL 4.1.1 or higher:

SELECT DATEDIFF(MIN(date), MAX(date)) FROM table

if not:

$result = mysql_query('SELECT MIN(UNIX_TIMESTAMP(date)) AS min_time, MAX(UNIX_TIMESTAMP(date)) AS max_time FROM table');
$row = mysql_fetch_assoc($result);
$diff = $row['max_time'] - $row['min_time'];
$diff = $diff / 60 / 60 / 24;

[dreams4000]

Ok I am getting a mysql supplied arg not valid error off of that..

$sql2 = "SELECT MIN(UNIX_TIMESTAMP(checked_out)) AS min_time, MAX(UNIX_TIMESTAMP(checked_in)) AS max_time AND checked_in != '00000000000000' FROM sometable WHERE someid = $someid";

$result = mysql_query($sql2, $conn);
$row = mysql_fetch_row($result);
$diff = $row['max_time'] - $row['min_time']; 
$diff = $diff / 60 / 60 / 24;

echo $diff;

[devinemke]

obvious questions:
1. are you connected to the db?
2. are checked_out and checked_in DATE or DATETIME field types?
3. why the hell do have the checked_in != '00000000000000' clause?
4. is your table really called sometable?
5. is your primary key field really called someid?
6. is the variable $someid set?

use [man]mysql_error[/man] to see what's going on:

$result = mysql_query($sql2, $conn) or exit(mysql_error());

[dreams4000]

1. are you connected to the db? Yep, connections have worked fine till i tried your example.
2. are checked_out and checked_in DATE or DATETIME field types? Timestamp
3. why the hell do have the checked_in != '00000000000000' clause? because the MIN command tends to pick this up as the Min. I want those skipped if they have that in their field.
4. is your table really called sometable? nope, but the one i use is correct.
5. is your primary key field really called someid? nope, but this one is correct as well in the script i have.
6. is the variable $someid set? yes.. i can echo the var and get the output i want just fine.

[devinemke]

Originally posted by dreams4000
2. are checked_out and checked_in DATE or DATETIME field types? Timestamp

UNIX timestamp or mySQL timestamp?

what does mysql_error() have to say?

[dreams4000]

to give you an idea.. this works fine:

$sql2 = "SELECT ((UNIX_TIMESTAMP(checked_in) - UNIX_TIMESTAMP(checked_out))/86400) FROM sometable WHERE id = '2'";

and outputs 1 day since the difference is one day.. but in this, I am telling it to look only at one specific row.. In the one with all the min and max, I am trying to get it to look at a lot of rows, so id = 2 would be a different item that matched with more than one row and then I want to have it get the min and max as discussed so I can use those two dates instead of the specific two I used above.

[dreams4000]

I think I got it.. I moved the 000 thing to a different location. think it works now.. will play with it a bit more to make sure. Thanks for your patience and help with this!