Need help when quering from a database.

[madjack87]

This is the current code that I have

This code is working correctly I just need help on how to make it do something different.

 

<?php
$result = mysql_query("SELECT * FROM events WHERE bar='Its All About Us' ORDER BY date");
while($row = mysql_fetch_array($result)){
echo"<h3>";
echo $row['date'];
echo"</h3><br/><p>";
echo $row['event'];
echo"</p><br/>";
}
?>

 

What I am trying to achieve is The Date as a header and then the events underneath the date. But If there is multiple entries for a specific date this is what happens

 

10/31/2009

Halloween party

10/31/2009

Costume party

 

and what I want is

10/31/2009

Halloween party

Costume party

 

Any help?

[play_]

try 'group by date'

[Bricktop]

Edit: play_ beat me to it 

[madjack87]

So using GROUP BY date

 

I now have to change my while loop correct? and when I used the GROUP BY it only showed one event underneath the date.

[madjack87]

Sorry about the lack of details in the last post I will try to be more detailed.

 

When using GROUP BY date, with the current while loop it will only display 1 event and 1 date so any other dates or events are not posted.

 

Also the way i have my SQL Table set up is like this

5 Its All About Us 2009-10-31 Racine Rage will be here!

2 Its All About Us 2009-10-31 Halloween Party!

3 Its All About Us 2009-10-07 Poker Starts a 7pm FREE to Play

4 Its All About Us 2009-12-31 New Years

 

What I want to happen is

 

2009-10-07

Poker Starts a 7pm FREE to Play

 

2009-10-31

Racine Rage will be here!

Racine Rage will be here!

 

2009-12-31

New Years

 

Using my original code I get

 

2009-10-07

Poker Starts a 7pm FREE to Play

 

2009-10-31

Racine Rage will be here!

 

2009-10-31

Racine Rage will be here!

 

2009-12-31

New Years

 

Any hints on what I should change?

My SQL Table?

Php code?

etc.?

[cags]

This is the approach I've taken in the past, not sure if it would be considered the best method. But it should certainly work for you in this situation.

 

<?php
$result = mysql_query("SELECT * FROM `events` WHERE `bar`='Its All About Us' ORDER BY `date`");
$curr_date = '';

while($row = mysql_fetch_array($result)){
  if($row['date'] != $curr_date) {
    echo '<h3>' . $row['date'] . '</h3>';
    $curr_date = $row['date'];
  }
  echo '<p>' . $row['event'] . '</p>';
}
?>

 

Note: You will probably need to change that slightly to get your formatting back, it's just an example of the logic.

[madjack87]

Worked like a charm.. Thanks for the help!