Page 1 of 2
help needed with sql syntax
Posted: Mon Dec 01, 2003 12:16 am
by pelegk2
i have a table with a col that hold the date+time
i want for a certain month X to get for each tday only the most last updates recored!
is it possible?
then how?
thanks in advance
peleg
Posted: Mon Dec 01, 2003 12:21 am
by infolock
you could do this easily by doing something like :
Code: Select all
$sql = "select something from my_table where date = '2003-10-18' order by date";
$result = mysql_query($sql);
hope this helps.
maybe i didnt explain my self u i meant for all the days at
Posted: Mon Dec 01, 2003 12:25 am
by pelegk2
maybe i didnt explain my self u i meant for all the days at a certain month!
Posted: Mon Dec 01, 2003 12:33 am
by infolock
oh, then try this :
Code: Select all
$sql = "select something from my_table where (date like '2003-10-%') order by date";
$result = mysql_query($sql);
thanks but what do i do if the data is in mktime format?>
Posted: Mon Dec 01, 2003 12:38 am
by pelegk2
ok i explain my self again
Posted: Mon Dec 01, 2003 12:42 am
by pelegk2
i want in all the days of the month
only the most updates for each day
Posted: Mon Dec 01, 2003 1:02 am
by infolock
sorry, just having trouble understanding what it is you are asking, but i think i understand now.
the above
does query for every day in that month, but if you are wanting it to give you the maximum # of updates PER day from a field called updates, then you could do it like this:
Code: Select all
$days=array();
$updates=array();
$sql = "select sum(something),date from my_table where date >= '2003-10-01' and date <= '2003-10-31' order by date";
$result = mysql_query($sql);
$row = mysql_fetch_array($sql);
while ($row=mysql_fetch_array($sql))
{
$days[]=$row['date'];
$updates[]=$row['something'];
/* or you could just do this :
echo $row['date'];
echo '<br />';
echo $row['something'];
echo '<br />';
*/
}
// otherwise, echo it out like this.
for($i=0; $i<=count($days); $i++)
{
echo 'Date : '.$days[$i].' -- Total Updates : '.$updates[$i];
echo '<br />';
}
that more what you are looking for ?
nope sorry if i didnt explai ok i explain again
Posted: Mon Dec 01, 2003 1:08 am
by pelegk2
i have a table
every time a or updated
the col with the date+time in mktime format is updated
now what i need to extract is :
for a certain month X
i want in each day only the most updated row by time!
say if in 15/10/2003 i had the time : 18:35 and 19:47
so for tht day i will get the 19:47 row beacuse its the most updated row for that day!
thanks in advance
peleg
Posted: Mon Dec 01, 2003 2:07 am
by JAM
Code: Select all
select day_field, max(time_to_sec(time_field)) from table group by days_field order by days_field
Not tested, so...
But the general idea was; get the max existing time (i'm using time_to_sec to get '22:23:00' to be '80580' for example).
Tweak it around abit, to get the desired effect.
what does the
Posted: Mon Dec 01, 2003 2:13 am
by pelegk2
group by days_field
gives u?
Posted: Mon Dec 01, 2003 2:21 am
by JAM
If you don't group by you select max() on all rows.
Grouping by days, makes you select max() on day-to-day basis...
Hope that was a correct explaination, but someone else, please correct me if I'm lost.

u said
Posted: Mon Dec 01, 2003 2:26 am
by pelegk2
makes you select max() on day-to-day basis...
but i want to make a day based comperison and not between the days
1 more question
how do i convert a mktime format in my databse at query time to seconds as u told me?
thanks in advance
and alot for your help
peleg
Posted: Mon Dec 01, 2003 2:27 am
by Nay
Let me put it in another example:
You have:
Monday
Monday
Tuesday
Sunday
Wednesday
Wednesday
Wednesday
With group by, it should return:
Monday
Tuesday
Sunday
Wednesday
Or was that DINSTINCT? I'm not 100% sure either but I think group by and distinct does the same thing.
-Nay
ok i check it
Posted: Mon Dec 01, 2003 2:37 am
by pelegk2
but how it will work on a date that is in format of :
1070267738(unix_timestamp) ?
Posted: Tue Dec 02, 2003 11:51 am
by JAM