Sort doesn't work with pagination

Question

Sorting is work perfectly before I combine it with pagination. But right now I am facing with the problem this when to pass value of sorting to pagination:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?page=1 ASC LIMIT 0, 10' at line 3

Below is my code:

<?php

session_start();
$host = 'localhost';
$user = 'root';
$pass ='password';
$name = 'dbname';
$con = new mysqli ($host,$user,$pass,$name);
?>

<html>
<h2>Your Redeemed Ticket (<?php echo $_SESSION['username'];?>):</h2>


<table border='1' align='center' cellpadding='10'>
    <tr align = "center">
    <td>
         <a href="user_history.php?sort=purchase_id">Purchase ID</a>
    <td>
        <a href="user_history.php?sort=ticket_type">Ticket Type</a>
    <td>
        <a href="user_history.php?sort=qty">Quantity</a>
    <td>
        <a href="user_history.php?sort=date">Date</a>
    </tr>


</html>
<?php



    // default sorting
    if(isset($_GET['sort'])){
        $sortOrder = $_GET['sort'] ? :0;
        /*Pagination*/
        $limit = 10;  
        if (isset($_GET["page"])) 
        { 
            $page  = $_GET["page"]; 
        } 
        else 
        { 
            $page=1; 
        }

        $start_from = ($page-1) * $limit;
        $username = $_SESSION['username'];
    $data = 
    "
    SELECT *
    FROM redeem 
    WHERE redeem_by = '$username' ORDER BY ".$sortOrder." ASC LIMIT $start_from, $limit
    ";
    $result = $con->query($data);

    if ($result === false)
            die (mysqli_error($con));
    while($rows = mysqli_fetch_array($result))
    {
        echo "<tr>";
        echo "<td align='center'>"; 
            echo $rows['purchase_id']; 
        echo "<td align='center'>";
            echo $rows['ticket_type'];
        echo "<td align='center'>";
            echo $rows['qty'];
        echo "<td align='center'>";
            echo $rows['date'];
        echo "</tr>";
    }
    echo "</table>";


    /*PAGE NUMBER DISPLAY*/
    $data2 = "SELECT COUNT(redeem_by) FROM redeem";  
    $rs_result = mysqli_query($con,$data2);  
    $row = mysqli_fetch_row($rs_result);  
    $total_records = $row[0];  
    $total_pages = ceil($total_records / $limit);  
    $pagLink = '';
    for ($i=1; $i<=$total_pages; $i++)
        {  
             $pagLink .="<a href='user_history.php?sort=".$sortOrder."?page=$i'>" .$i."/</a>"; 
        };  
    echo "<center>".$pagLink."</center>";  

    /*LOGOUT*/
    if(isset($_POST['logout']))
    {
        session_destroy();
        header ("location:login.php");
        exit();
    }
    }

?>

`?page=$i` must be `&page=$i`. You can only have one `?` in the URL. — deceze, May 25 '17 at 02:27
It looks like your sort order is vulnerable to SQL injection. If I wanted to sort by `; show tables; ` for example. Consider using prepared queries with `?` and passing the user input through that for safer queries — EnabrenTane, May 25 '17 at 02:27
Before going any further please protect yourself and your users against sql injection => https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Louis Loudog Trottier, May 25 '17 at 02:28
Thank you @deceze. Yes, this is just a dummy page for testing my pagination. I will be more alert regarding of sql injection — Incognitorrrr, May 25 '17 at 02:29

score 1 · Accepted Answer · answered May 25 '17 at 02:34

1

There are a few weird issues in your code, but the problem with your screwed up GET variable can probably be traced to here:

<a href='user_history.php?sort=".$sortOrder."?page=$i'>"

Obviously when you have url parameters, you delineate the starting point with the '?' but after that you need to seperate additional parameters with the '&'.

So it should be:

<a href='user_history.php?sort=".$sortOrder."&page=$i'>"

answered May 25 '17 at 02:34

gview

14,876
3
46
51

Its weird because i refer to others questions and probably used their solution. I am new to php. New things to learned. Thank you. – Incognitorrrr May 25 '17 at 02:43

Sort doesn't work with pagination

1 Answers1