Loops and mySQL

[eludlow]

I hate writing loops, and I've been trying to work this one out for a while now. I know it's piss simple to write, and I should be able to do it, but brain often shuts down while writing loops, so I'm calling on you good people for help!

Bascially, I've an SQL table, each row having a unique "id" field, a simple integer. The first row in the table is 1, and when each new row is added, that number gets incremented. That's all fine and working.

What I want to produce is a simple php script that will write the latest five rows in the table to the screen, and then produce the rest of the rows on another page - a sort of archive.

How would you go about producing:
1) the loop to print JUST the first five
and
2) the loop to write all the rows after the first five.

Many thanks,
Ed Ludlow

[TTT]

$q = "SELECT id,whatever FROM table ORDER BY id DESC, LIMIT 0,5";
$q_temp = "SELECT id FROM table"; // count all rows, to be sure that there is more than 5 rows NOT only 5
if(mysql_num_rows($q) != 0) {
$idcollection= "";
$c = 0;
while($q1 = mysql_fetch_row($q)) {
echo $q1[1]."<br>";
$idcollection = $idcollection.$q1[0].",";
$c++;
}
$idcollection = substr($idcollection,0,strlen($idcollection)-1); // rip last comma
if($c == 5 && mysql_num_rows($q_temp) > 5) {
print "<a href='index.php?dontShowThese=$idcollection'>next page</a>";
}
}

//// next page
$q = "SELECT id,whatever FROM table WHERE id NOT IN ('$idcollection')";

[eludlow]

Thank you.

I've put this into the script:

$q = "SELECT date,newsitem,id FROM xanews ORDER BY id DESC, LIMIT 0,5";
$q_temp = "SELECT id FROM xanews";
if(mysql_num_rows($q) != 0) {
$idcollection= "";
$c = 0;
while($q1 = mysql_fetch_row($q)) {
echo $q1[1]."<br>";
$idcollection = $idcollection.$q1[0].",";
$c++;
}
$idcollection = substr($idcollection,0,strlen($idcollection)-1);
if($c == 5 && mysql_num_rows($q_temp) > 5) {
print "<a href='index.php?dontShowThese=$idcollection'>next page</a>";
}
}

But it's giving me an error on line:
if(mysql_num_rows($q) != 0) {

The error is:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

I can't see what's wrong with it - any ideaS?

Thanks,
Ed

[AstroTeg]

The variable $q needs to contain a valid MySQL result value. Typically, you can do that with:

$q = mysql_query([your query string to go here]);

You'd do this before you start the loop. You may also want to check into some error checking on $q so you don't bang against mysql_fetch_row() and have PHP show an ugly error.

[eludlow]

Thanks for the reply.

$q = mysql_query("SELECT date,newsitem,id FROM xanews ORDER BY id DESC, LIMIT 0,5");

$q_temp = "SELECT id FROM xanews";
if(mysql_num_rows($q) != 0) {

So I give it that, and it still throws the error at me?!

Ed

[AstroTeg]

Time to practice some troubleshooting skills...

I'd recommend doing something like:

echo mysql_error();

That will force PHP to dump the MySQL error. It could be possible you have an error with your SQL statement. What exactly is the error you're getting?

[eludlow]

Sorted it another way 🙂

Many thanks for your help anyway.

Ed