having trouble with DB

[Anon]

hi, i'm quite new to php/mysql and i'm just trying to simple pull new from a DB, which i can do
but then i tried it so it theres more than 1 lot of news in one day it only shows the date once, so it SHOULD look like this

date
news bit 1

date 2
news bit 2

news bit 3

but instead of displaying the last news bit or the last in the DB it displays news bit 2 again. i hope you understand this, thanks for any help
this is part of the code,
note $count = 30 or the number of records in the table if its under 30.

$i = 0;
while($i <= $count)
{
$myRow = mysql_fetch_array($result);
$date = substr($myRow['postedDate'], 0, 10);
$time = substr($myRow['postedDate'], 11, 5);
$lastDate = 234;
if ($i > 0) {
mysql_data_seek($result, $i-1);
$lastDate = mysql_fetch_array($result);
$lastDate = $lastDate['postedDate'];
$lastDate = substr($lastDate, 0, 10);
mysql_data_seek($result, $i);
}

if ($date != $lastDate) {
?>
	<tr><td><img src="layout/dot_clear.gif" height="3" alt="" border="0"></td>
	<tr><td bgcolor="#2F3684" width="30"><img src="layout/icon_sf.gif" width="30" height="25" border="0"></td>
	<td bgcolor="#2F3684" valign="middle" width="100%"><span class="headerwhite"> <?php echo($date); ?> </span></td></tr>
<?php
}
?>
	<tr><td bgcolor="#191C3A" colspan="2"><span class="header"> <?php echo("[" . $time . "] " . $myRow['title']); ?> </span><br>
	<span class="plaintext"> <?php echo($myRow['story']); ?> </span><br><br>
	<span class="plaintext">//</span> <a href="mailto:[e-mail]" class="standardlink"> <?php echo($myRow['poster']); ?> </a><br>
	</td></tr>
<?php
$i++;

}

---

also on a different note, how do you guys do your news? do yous have it a a DB and have a page that you can easily add to it, or is there a better way of doing?

anyway thanks for any help

[djg_uk]

Hi Luke,

One way of doing this is to use the COUNT() and GROUP BY functions in SQL as a seperate query. From this data, you can create custom FOR loops to the size of COUNT() - 1.

For example:

$query = mysql_query("SELECT postedDate, COUNT(story) FROM news GROUP BY postedDate");
$query2 = mysql_query("SELECT postedDate, story FROM news");

Grab the row using mysql_fetch_row or whatever you prefer for your DB type.

for ($i=0;$i<mysql_num_rows($query);$i++) {
$query_result = mysql_fetch_row($query);
for ($j=0;$j<($query_result[1] - 1);$j++) {
echo ("$query_result[0]");
$query2_result = mysql_fetch_row($query2);
echo("$query2_result[1]");
}
}

Hope this helps.

Regards,

David

[Anon]

thanks david
if i understand right this should group them by date and i will be able to print out the date and then all the stories in the 'group'?

that sounds great but this line:
for ($i=0; $i < mysql_num_rows($query); $i++) {

gives error:
Warning: Supplied argument is not a valid MySQL result resource

which i guess is being caused by, $query = mysql_query("SELECT postedDate, COUNT(story) FROM news GROUP BY postedDate");
but sorry if i sound dumb 🙂 i have no idea why, as i said i'm new to it, and haven't used the count() or group by bits in SQL, so i am looking in the docs now to read up why its not working,
tanks for you help.