displaying multiple items under one date

michaelk

this is the current code of an updates page i have created at:
http://www.no-nothingrock.com/updates/index.php

<?

$db_name = "nonothingrockdb";
$table_name = "nnr_updates";

$connection = @mysql_connect("localhost", "username", "password")
  or die("couldn't connect to database.");

$db = mysql_select_db($db_name, $connection)
  or die("Couldn't select database.");

$sql = "SELECT id, DATE_FORMAT(date, '%W, %M %d, %Y') as date, time, author, updates FROM $table_name WHERE TO_DAYS(NOW()) - TO_DAYS(date) <= 30 ORDER BY id DESC";

$result = @mysql_query($sql,$connection)
  or die("couldn't complete query.");

while ($row = @mysql_fetch_array($result)) {

  $id = $row ['id'];
  $date = $row['date'];
  $time = $row['time'];
  $author = $row['author'];
  $updates = $row['updates'];

  $updates_block .= "

  <P CLASS=\"date\">$date</P>
  <P CLASS=\"main\">$updates</P>
  <P CLASS=\"time\">posted by $author | $time</P>
  <P>&nbsp;</P>

  ";
}

?>

what i'm looking to do is to get the date to display once, and every update that has been put in on that date to display.

the code now, displays the date with EACH update like so:

09-15-02
blah blah blah

09-15-02
text text text

09-14-02
blah blah blah

how would i get the first two updates to go under one heading ?

like:

09-15-02

blah blah blah

text text text

09-14-02

blah blha blah

i tried nesting while loops, but that didn't work exactly.
this was the code:

<?

$db_name = "nonothingrockdb";
$table_name = "nnr_updates";

$connection = @mysql_connect("localhost", "username", "password")
  or die("couldn't connect to database.");

$db = mysql_select_db($db_name, $connection)
  or die("Couldn't select database.");

$sql = "SELECT id, DATE_FORMAT(date, '%W, %M %d, %Y') as date FROM $table_name WHERE TO_DAYS(NOW()) - TO_DAYS(date) <= 30 ORDER BY id DESC";

$result = @mysql_query($sql,$connection)
  or die("couldn't complete query.");

while ($row = @mysql_fetch_array($result)) {

  $id = $row ['id'];
  $date = $row['date'];

  $updates_block .= "

  <P CLASS=\"date\">$date</P>

  ";

  $sql2 = "SELECT * FROM $table_name WHERE date = $date ORDER BY id DESC";

  $result2 = @mysql_query($sql2,$connection)
    or die("couldn't complete query.");

  while ($row2 = @mysql_fetch_array($result2)) {

$time = $row2['time'];
$author = $row2['author'];
$updates = $row2['updates'];

$updates_block .= "

<P CLASS=\"main\">$updates</P>
<P CLASS=\"time\">posted by $author | $time</P>
<P>&nbsp;</P>

";
  }

}

?>

any suggestions on how i can correctly script the desired effect ?

thanks.

peeter_talvistu

the problem with your code is in $date statement. it is a problem because you use date_format in the first statement

the double-while loop should work, but do it like this

$sql = "select date from nnr_updates";

$result = @($sql,$connection)
or die("couldn't complete query.");

while ($row = @mysql_fetch_array($result)) {

$date = $row['date'];

sql2 = "SELECT * FROM $table_name WHERE date = $date ORDER BY id DESC";

}

then all the usual stuff

also, if you would like to use date_format, do it in the sql2 (instead of * take all things out separately).

hope it works,

peeter

peeter_talvistu

ah, almost forgot, in the first sql use order by date too

peeter

michaelk

i tried that example, so something else must be wrong, i even simplified the code as much as possible to just get a result,

the page returns with no parse / mysql errors, but just displays no information.

<?

$db_name = "nonothingrockdb";
$table_name = "nnr_updates";

$connection = @mysql_connect("localhost", "username", "password")
  or die("couldn't connect to database.");

$db = mysql_select_db($db_name, $connection)
  or die("Couldn't select database.");

$sql = "SELECT date FROM $table_name ORDER BY date DESC";

$result = @mysql_query($sql,$connection)
  or die("couldn't complete query.");

while ($row = @mysql_fetch_array($result)) {

  $date = $row['date'];

  $sql2 = "SELECT * FROM $table_name 
    WHERE date = $date ORDER BY id DESC";

  $result2 = @mysql_query($sql2,$connection)
    or die("couldn't complete query.");

  while ($row2 = @mysql_fetch_array($result2)) {

$time = $row2['time'];
$author = $row2['author'];
$updates = $row2['updates'];
$id = $row2['id'];

$updates_block .= "

<P CLASS=\"date\">$date</P>
<P CLASS=\"main\">$updates</P>
<P CLASS=\"time\">posted by $author | $time</P>
<P>&nbsp;</P>

";

  }

}

?>

michaelk

okay.. i am almost there.

this is the result of the following code:
http://www.no-nothingrock.com/updates/updates2.php

<?

$db_name = "nonothingrockdb";
$table_name = "nnr_updates";

$connection = @mysql_connect("localhost", "username", "password")
  or die("couldn't connect to database.");

$db = mysql_select_db($db_name, $connection)
  or die("Couldn't select database.");

$sql = "SELECT date FROM $table_name ORDER BY date DESC";

$result = @mysql_query($sql,$connection)
  or die("couldn't complete query.");

while ($row = @mysql_fetch_array($result)) {

  $date = $row['date'];

  $updates_block .= "

<P CLASS=\"date\">$date</P>

  ";

  $sql2 = "SELECT * FROM $table_name WHERE date = \"$date\" ORDER BY id DESC";

  $result2 = @mysql_query($sql2,$connection)
    or die("couldn't complete query.");

  while ($row2 = @mysql_fetch_array($result2)) {

$time = $row2['time'];
$author = $row2['author'];
$updates = $row2['updates'];
$date = $row2['date'];
$id = $row2['id'];

$updates_block .= "

<P CLASS=\"main\">$updates</P>
<P CLASS=\"time\">posted by $author | $time</P>
<P>&nbsp;</P>

";

  }

}

?>

the problem now, is that it returns the two updates for today twice. instead of once. and also, where would i throw in the DATE_FORMAT and still be able to let the $date go through to the next function....

i'm not well versed at all in for loops, but would that at all apply to this situation ?

thanks.

// michael

peeter_talvistu

it displays it twice because there are to rows with the same date. to avoid this use group by date in the first sql (i don't know if this should be before or after the order by, but check the mysql manual).

as for the date. there should be some sort of a way to format date in array. i don't know it, however (i have problems with arrays and loops also). maybe someone else can help, or maybe you can find it in the manual. the longer way would be to make one more query to the database between the current two. like "select date_format (date, '%d.%m.%Y') as date from $table where date = $date group by date". then give this formated date value of $date2 and call for it (display it) before the next sql query.

hopefully this will help