Two QUERY SELECT questions [Archive]

View Full Version : Two QUERY SELECT questions

Temper

07-13-2003, 09:48 PM

I have this garbled mess for code:

$query = "SELECT * FROM news ORDER BY id";
$result = mysql_query($query, $db) or die(mysql_error());
$numRows = mysql_num_rows($result);
for ($count = 0; $count < $numRows; $count++) {
$resultArray = mysql_fetch_array($result);

?>
<td width=500>
<? echo $resultArray["title"]; ?>
</td></tr>
<tr><td>
By  
<? echo $resultArray["uname"]; ?>
- Posted on <? echo , $resultArray["date"]; ?>
</td></tr><tr><td>
<? echo $resultArray["text"]; ?>
 
</td></tr>
<?
}
?>

I am not fluent in MySQL or PHP, so I can't resolve this problem by myself, but I'm assuming mostly everyone can do this. This code outputs all of the text that I submit into my MySQL database onto my main page. It is displaying my news posts in order by the id colum of my table. ( The ID's are increasing by one each post ). So what happens is everytime I submit a post to my database, the ID number goes up by one, and on my main page, the ids and the posts are being displayed in an increasing mannar down the page. (ex. posts go 1,2,3,4,5,6 down the page). Unfortunatly for me, that puts the most recent post that I've submitted at the bottom of the page, and the last post I submitted at the top.

Now for the question:
How do I make it so that the IDs show the most recent post, or how do I make the bigger IDs show at the top of the page?
I hope that's not too confusing to follow, I'm not good at explaining things.

Also:
How do I put a set number of post per page? (Ex. have 10 news posts per page, and then at the bottom I'd put a link to an archive).

Thanks in advance.

-Temper.

raf

07-13-2003, 11:07 PM

About your first question:

just ad DESC (as in Descending --> descending order) to the selectstatement
$query = "SELECT * FROM news ORDER BY id DESC";

About the second question:

you can use LIMIT for that. To display the first 10 records, use
$query = "SELECT * FROM news ORDER BY id DESC LIMIT 10";

To get records 11-20 use
$query = "SELECT * FROM news ORDER BY id DESC LIMIT 10, 10";

The first 10 = the offset --> recordset starts at 0 so 10 is the elevent row. Second 10 = number of records to retrieve.
The only thing you need to do, is link the offset-value you need to the querystring of the "next page" link.

More info on limit:
http://www.mysql.com/doc/en/SELECT.html
If yu need more info on recordset paging, run a search in the PHP forum or on google or so.

Temper

07-14-2003, 12:22 AM

Many thanks, you've solved my problem and sent me back on track to finishing this website. Thank you!
-Temper

raf

07-14-2003, 08:17 AM

That' nice. Glad i could help a bit. :thumbsup:

ReadMe.txt

07-14-2003, 10:10 PM

just as a tip, it's not necessary to count the rows then use a for loop, this code will work just as well:

$query = "SELECT * FROM news ORDER BY id";
$result = mysql_query($query, $db) or die(mysql_error());
if ($resultArray = mysql_fetch_array($result)) {
do {
?>
<td width=500>
<? echo $resultArray["title"]; ?>
</td></tr>
<tr><td>
By 
<? echo $resultArray["uname"]; ?>
- Posted on <? echo , $resultArray["date"]; ?>
</td></tr><tr><td>
<? echo $resultArray["text"]; ?>
 
</td></tr>
<?
} while ($resultArray = mysql_fetch_array($result));
} else {
//you can put a message about no rows found here
}
?>

raf

07-14-2003, 10:50 PM

Or

if (mysql_num_rows($result)== 0){
echo (" No rows found");
echo (" <a href=\"javascript:history.back();\">Back</a>");
}
else {
while ($resultArray = mysql_fetch_array($result)) {
echo ("<td width=\"500\"> $resultArray['title']</td></tr>");
echo ("<tr><td>By $resultArray['uname']- Posted on $resultArray['date']</td></tr>");
echo ("<tr><td> $resultArray['text'] </td></tr>");
}
}