help needed with sql syntax

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
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

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
User avatar
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

Post by pelegk2 »

maybe i didnt explain my self u i meant for all the days at a certain month!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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);
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

thanks but what do i do if the data is in mktime format?>

Post by pelegk2 »

:(
User avatar
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

Post by pelegk2 »

i want in all the days of the month
only the most updates for each day
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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 ?
User avatar
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

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
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

Post by pelegk2 »

group by days_field
gives u?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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. ;)
User avatar
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

Post 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
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
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

Post by pelegk2 »

but how it will work on a date that is in format of :
1070267738(unix_timestamp) ?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Post Reply