Prev and Next buttons

Question

I'm new and still learning php&mysql. Search all day and tried different tutorials but nothing happen. So far I have only this with which I fetch text from my DB. How can I make prev and next buttons here?

if($q = mysqli_query($con, 'SELECT * FROM joke WHERE `id` = ' . mysqli_real_escape_string($con,$_GET['id']))){
    if($row = mysqli_fetch_array($q)){
        echo nl2br($row['text']);
        echo '<div id="data">Date ' . $row['date'] . "</div>';
    } else {
        echo 'Not found';
    }
} else {
    echo mysqli_error($con);
}

echo "</div>";
$query = "select * from joke order by RAND() LIMIT 1";
$result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
while ($row = mysqli_fetch_array($result, MYSQL_BOTH)){
    echo '<a href="single.php?id='.$row['id'].'"class="random">Random</a>';
}

What you're looking for is pagination. there are many many tutorials with examples. If one of them is confusing, post it here and tell us what you don't understand. — sachleen, Jan 26 '14 at 01:20
After reading a pagination tutorial, I would take a look at prepared statements (http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks) as the mysql_real_escape_string function is deprecated. — Dan, Jan 26 '14 at 01:21
This `mysqli_query` plus `mysql_real_escape_string` `=` `Breakdown` --- Use `mysqli_real_escape_string($_GET['id'])` instead. — Funk Forty Niner, Jan 26 '14 at 01:46
See my edited comment above. That's one of the things to do. — Funk Forty Niner, Jan 26 '14 at 01:48
And this `echo '
Date ' . $row['date'] . "
";` should probably be `echo '
Date ' . $row['date'] . '
';` You're starting your echo with a single quote, then ending with doubles. Or `echo "
Date " . $row['date'] . "
";` — Funk Forty Niner, Jan 26 '14 at 01:53

Tin Tran · Accepted Answer · 2014-01-26T01:57:54.100

1

if i understand you correctly you want to display the next and previous joke based on id,
so you want to select max(id) from joke where id < the current id
and ... select min(id) from joke where id > the current id

$currentId = mysqli_real_escape_string($con,$_GET['id']);
if($q = mysqli_query($con, 'SELECT *,
(SELECT IFNULL(max(id),-1) FROM joke WHERE `id` < '.$currentId.') as previousid,
(SELECT IFNULL(min(id),-1) FROM joke WHERE `id` > '.$currentId.') as nextid
  FROM joke WHERE `id` = ' . $currentId)){
    if($row = mysqli_fetch_array($q, MYSQL_BOTH)){
        echo nl2br($row['text']);
        echo '<div id="data">Date ' . $row['date'] . '</div>';
        if ($row['previousid'] > -1){
            echo '<a href="single.php?id='.$row['previousid'].'"class="random">Previous</a>';
        }
        if ($row['nextid'] > -1){
            echo '<a href="single.php?id='.$row['nextid'].'"class="random">Next</a>';
        }
    } else {
        echo 'Not found';
    }
} else {
    echo mysqli_error($con);
}

echo "</div>";
$query = "select * from joke order by RAND() LIMIT 1";
$result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
while ($row = mysqli_fetch_array($result, MYSQL_BOTH)){
    echo '<a href="single.php?id='.$row['id'].'"class="random">Random</a>';
}

edited Jan 26 '14 at 01:57

answered Jan 26 '14 at 01:30

Tin Tran

6,194
3
19
34

Great! Just one more question. When is is on first item there is also button `prev` and when I click I get error `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as previousid, (SELECT min(id) FROM joke WHERE `id` > ) as nextid FROM jok' at line 2`. Same is with last item and button `next`. Any idea how to fix this? – Gordon Jan 26 '14 at 01:40
yeah because when you're on the first item or last item, the previousid will be null, or the nextid will be NULL respectively, you can use `IFNULL(max(id),defaultValueHere)` instead of `max(id)`, you might want to juse default it to your current that's one possible solution. Another solution is do `IFNULL(max(id),-1)` then check to see if the previousid is -1 then don't echo the previous button. – Tin Tran Jan 26 '14 at 01:42
what can be `defaultValueHere` in this case. How to get it? – Gordon Jan 26 '14 at 01:44
`IFNULL(max(id),-1)` not showing the previous button or the next button makes more sense though, you can just do an if and check to see if it's -1 then don't show the button at all. – Tin Tran Jan 26 '14 at 01:46
For one thing, this `mysqli_query` plus `mysql_real_escape_string` `=` `Breakdown` @TinTran – Funk Forty Niner Jan 26 '14 at 01:50
whoops i just cut and pasted didn't see that. – Tin Tran Jan 26 '14 at 01:52
@Fred-ii-, i wrote that I fixed all mysql to mysqli. – Gordon Jan 26 '14 at 01:52
Ok. Could you edit your question in order to reflect the changes? @Gordon - And did you see my comment underneath your question about the quotes? – Funk Forty Niner Jan 26 '14 at 01:56

Prev and Next buttons

1 Answers1