MySQL retrieval

[twQ]

Ok, all I wanna do is retrieve the entries from newest to oldest. Originally it retrieved from oldest to newest but that's not exactly what I'm wanting haha. The below code doesn't work, for no obvious reason I can find so please tell me where I steered wrong:)



mysql_connect('XXX',$dbuser,$dbpass);
@mysql_select_db($database) or die('Unable to select database');

$query="SELECT * FROM news";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();


That's in the head to retrieve the info and this is where it's output:


while ($num > 0) {
$date = mysql_result($result,$num,"date");
$author = mysql_result($result,$num,"author");
$title = mysql_result($result,$num,"title");
$body = mysql_result($result,$num,"body");

echo $title,'<br>',$date,'<br>by: ',$author,'<br><br>',$body;

$num--;
}


It outputs an error (I put it in the post, below) and then outputs the newest entry, but NOT the one before it (so the oldest). I'm sorry to waste time I'm sure it's a dumb error but even after multiple tutorials and Googles I can't find out what's wrong. I know someone here can point it out quick.

Thanks alot,
Tim

edit:


Warning: mysql_result() [function.mysql-result]: Unable to jump to row 2 on MySQL result index 2 in /home/content/f/a/s/fassist/html/test/news/index.php on line 34


edit 2: wait maybe I should take the number of rows I get and do 'WHERE id' is the num since it should match...is that my problem?

[Schmoopy]

Just a quick thing I noticed,

It should be:



$num=mysql_num_rows($result);


You're missing an underscore, try it with that. If it's still not working, please provide the exact error you get on the page.

To list data in order of date, you could always just alter the query, instead of using a while loop. Like:


SELECT * FROM news ORDER BY `date` DESC

[twQ]

Thanks but that's a no go, same error. Didn't know I was missing that though. Thanks:)

edit: I posted it above.

[Schmoopy]

Does this work?



mysql_connect('XXX',$dbuser,$dbpass);
@mysql_select_db($database) or die('Unable to select database');

$query="SELECT * FROM news ORDER BY `date` DESC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

if($num > 0) { // Data returned
while($row = mysql_fetch_array($result)) {
echo $row['title'] . '<br />' . $row['date'] . '<br />by: ' . $row['author']. '<br /><br />' . $row['body'];
}
}

[twQ]

Your right I did. I hate it when I change code around and forgit to move other parts. That's why I have DD though haha. Thanks I'll test in a bit.

Works thanks! If you could can you explain real quick why what I did didn't work?

[bluewalrus]

echo $title,'<br>',$date,'<br>by: ',$author,'<br><br>',$body;


I think you should be using "." not ",", but you don't even need to do that if you use double quotes because the variable will be parsed in that. Using \n will insert new lines in your source code as well.


echo "$title <br>\n $date <br>\nby: $author <br><br>\n $body";

[Schmoopy]

I'm not sure, it seems like it should work, but it's been a bit overcomplicated with the while loop. Perhaps closing the connection early was why it didn't work.

In any case, you should always try and use ORDER BY if you want to list data in a certain order, as it's far simpler than doing it in a different way.

@bluewalrus - Yea I thought that too, I did make the change when editing the original code.

Here it is rewritten in quotes:



echo "{$row['title']} <br /> {$row['date']} <br />by: {$row['author']}<br /><br /> {$row['body']}";

[twQ]

Alright, well atleast I know what's wrong now haha. Thanks for all the help.

Tim