[Resolved] help with querying a table

knucklehead

hello!
i have a table that looks like this:

month    |  day      |  hour   |  text
_______________________________________________

january  |  monday   |  20:00  |  some text....
january  |  tuesday  |  22:00  |  some text....
january  |  thursday |  21:00  |  some text....
january  |  sunday   |  19:00  |  some text....
february |  monday   |  21:00  |  some text....
february |  sunday   |  19:00  |  some text....
etc.

i want to select stuff from table so the output looks like this:

JANUARY:

monday @ 20:00h
some text...

tuesday @ 22:00h
some text...

thursday @ 21:00h
some text...

sunday @ 19:00h
some text...

FEBRUARY:

monday @ 20:00h
some text...

tuesday @ 22:00h
some text...

etc.

of course, everything has to be dynamic, so there shouldnt be
echo "<b>JANUARY:</b>";
and similar stuff on the page...

...so how should query look like?
thanks in advance!

ps: sorry for posting this post twice but im really in hurry!

hollyspringer

SELECT * FROM your_table ORDER BY month, day, hour

leatherback

-- See you rpost in newbies forum and PLEASE do NOT double post --
Most of us will browse all the forums anyway.

knucklehead

ok, first of all i'm really sorry for double posts... and the other thing is that i think i wasn't clear enough... i don't know how to explain my problem but i will give my best...
so...

i don't need to sort stuff because that's already sorted but nevermind that now. instead i need to select all records with same 'month' value and display them in one table. then i need to select all records with SECOND 'month' value and display them in OTHER table.

leatherback posted this:

$query="Select * from TABLE order by month, day, hour"; 

$result = mysql_query($query); 

$ThisMonth = ""; 
while($row=mysql_fetch_array($result)) 
 { 
  if($ThisMonth != $row[month]) 
   { 
   $ThisMonth = $row[month]; 
   echo "<b>".$ThisMonth.":</b><br>" 
   } 
   echo $row[day]."<br>"; 
 }

but output of that looks like:

[B]january:[/B]
monday @ 20h
some text...

[B]january:[/B]
tuesday @ 22h
some text...

[B]january:[/B]
thursday @ 21h
some text...

[B]january:[/B]
sunday @ 19h
some text...

[B]february:[/B]
monday @ 20h
some text...

[B]february:[/B]
tuesday @ 22h
some text...

etc etc etc

BUT, that's not what i want! instead of this output i need to display month name ONLY ONCE (just like shown above in my first post).
so, generally i know what i need i just dont know how to ask for it... it would be something like:
for every record in table that have same month name display table with header which contains that month name and cell with all other records in that month

so, call me stupid but my brain is not functioning anymore because i was on work all day and i have a headache but i have to do this so i could get pay...

thanks in advance! very much!

and, yes, sorry for double posts one more time! it won't happen again 🙂

leatherback

Hi Knucklehead,

Are you sure you typed the code correct? (Especially the ! in the if statement?)
Because that i swhat I designed the code to do.

It checks the current month name, if it is the same as the previous records'month name, it skips that section. Otherwise it will print the month name.

leatherback

Hi, tried the code for you. It works just fine:

#
# Table structure for table `maand`
#

CREATE TABLE maand (
  month tinytext,
  day tinytext,
  hour tinytext,
  texts tinytext
) TYPE=MyISAM;

#
# Dumping data for table `maand`
#

INSERT INTO maand VALUES ('january', 'Monday', '22', 'testeeeh');
INSERT INTO maand VALUES ('january', 'tuesday', '23', 'ngmaar testeee');
INSERT INTO maand VALUES ('february', 'tuesday', '4', 'alle hens testeee');






$query="Select * from maand order by month, day, hour"; 

$result = mysql_query($query); 

$ThisMonth = ""; 
while($row=mysql_fetch_array($result)) 
 { 
  if($ThisMonth != $row[month]) 
   { 
   $ThisMonth = $row[month]; 
   echo "<b>".$ThisMonth.":</b><br>" ;
   } 
   echo $row[day]."<br>"; 
 }

Output:

february:
tuesday
january:
Monday
tuesday

knucklehead

achh! thanks for so much helping man 🙂
yes, now i see at which lines of code i was especially stupid.. and that's

$ThisMonth = $row[month];

and of course i was stupid when i was formatting html tables...

ok, everything is just great now...

once again THANK YOU VERY VERY VERY MUCH!

leatherback

NP,

Please mark this thread as resolved, and save people some time when looking for unanswered questions (See link at bottom of page)

knucklehead

np...

im so ashamed of myself now but i really have to thank you one more time 🙂