help needed with sql syntax
Moderator: General Moderators
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
help needed with sql syntax
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
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
you could do this easily by doing something like :
hope this helps.
Code: Select all
$sql = "select something from my_table where date = '2003-10-18' order by date";
$result = mysql_query($sql);- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
maybe i didnt explain my self u i meant for all the days at
maybe i didnt explain my self u i meant for all the days at a certain month!
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);- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
ok i explain my self again
i want in all the days of the month
only the most updates for each day
only the most updates for each day
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:
that more what you are looking for ?
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 />';
}- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
nope sorry if i didnt explai ok i explain again
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
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
Code: Select all
select day_field, max(time_to_sec(time_field)) from table group by days_field order by days_fieldBut 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.
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
what does the
group by days_field
gives u?
gives u?
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
u said
but i want to make a day based comperison and not between the daysmakes you select max() on day-to-day basis...
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
- pelegk2
- Forum Regular
- Posts: 633
- Joined: Thu Nov 27, 2003 5:02 am
- Location: Israel - the best place to live in after heaven
- Contact:
ok i check it
but how it will work on a date that is in format of :
1070267738(unix_timestamp) ?
1070267738(unix_timestamp) ?