CODEWITHSUNDEEP
phpmysql
WayneP
WayneP

Reputation: 139

Next and previous buttons

I know there are libraries etc that I could use to get this sorted but Im almost there with my code.

A little about the code and what it's trying to do. I have a mysql table where there are various news articles and grouped in categories of news.

I have managed to get a forward button working. So it looks for the next news article that is in the same category. This works and the code is below. 

//Gets the next story from the same story type in line. 
$query= "SELECT * FROM news WHERE storytype2 = '$storytype2' AND id > '$currentid'";
$result = mysql_query($query) or die ("Error in query: $query " . mysql_error());
$row = mysql_fetch_array($result); 
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
   echo "<td width=\"20%\"align=\"right\"><a href=\"news.php?id=".$row['id']."\"><img title=\"Next News\" src=\"webImg/forwardarrow.png\"/></a></td></tr>";
}else{
   echo "<td width=\"20%\"align=\"right\"></td></tr>";
}
//End of the next button

However, when I try do the same for the previous button. All I ever seem to get back is the first id of that category regardless of where my iteration is. For example, if I am on news article 10 and try to go to previous one which say has an id of 7 it will automatically show the first news article within that category, say id 4.

Below is the code. 

//Gets the next story from the same story type in line. 
$query= "SELECT * FROM news WHERE storytype2 = '$storytype2' AND id < '$currentid'";
$result = mysql_query($query) or die ("Error in query: $query " . mysql_error());
$row = mysql_fetch_array($result); 
$num_results = mysql_num_rows($result);

if ($num_results > 0){ 
   echo "<td width=\"20%\"align=\"left\"><a href=\"news.php?id=".$row['id']."\"><img title=\"Previous News\" src=\"webImg/backarrow.png\"/></a></td>";        
}else{
   echo "<td width=\"20%\"align=\"left\"></td>";
}
//End of the next button

What have I done wrong?

Thanks

Upvotes: 0

Views: 998

Answers (2)

ljacqu
ljacqu

Reputation: 2180

Without any further information, I don't think you can assume that the first row of your queries will be the ID you're looking for. Ordering by ID first will probably solve your problem; you can also limit your query to one row, since it's the only one you're looking at. Something like the following would probably solve your problem (where x is $storytype2 and y is $currentid:

SELECT * FROM news
WHERE storytype2 = x
 AND id < y
ORDER BY id DESC /* <-- THIS */
LIMIT 1

Use ORDER BY id ASC for the other case. Note that the MySQL family of PHP is deprecated and support thereof will disappear, if it hasn't yet. Please look into PDO or MySQLi.

Note also that you are inserting a variable into SQL code directly, which is never a good idea. I hope you have some good input checks on your variables.

Let's look at the PDO way to get the previous article ID:

$dbh = new PDO(..);
// Use ? where dynamic input will come
$sql = $dbh->prepare('SELECT * FROM news
  WHERE storytype2 = ?
  AND id < ?
  ORDER BY id DESC
  LIMIT 1');

// Fill the ? safely with PDO's execute function
$sql->execute(array($storytype2, $currentid));
$result = $sql->fetch(PDO::FETCH_ASSOC);

if($result && isset($result['id'])) {
   // Process previous ID
}

Upvotes: 0

Barmar
Barmar

Reputation: 780787

Neither of your queries is correct. Your "Next" code selects any row whose ID is higher than the current, not necessarily the next one; if you get the next one, it's just by accident.

You should use ORDER BY and LIMIT to control which row is selected:

Next:

SELECT * 
FROM news 
WHERE storytype2 = '$storytype2' AND id > '$currentid'
ORDER BY id
LIMIT 1

Previous:

SELECT * 
FROM news 
WHERE storytype2 = '$storytype2' AND id < '$currentid'
ORDER BY id DESC
LIMIT 1

Upvotes: 1

Related Questions