ORDER BY and WHERE with variable and LIMIT

Question

I have a variable with the following SQL code and its giving me an error (Without the "ORDER BY name ASC", works fine the code):

$sql = "SELECT * FROM incidents WHERE active = 1 AND username = '".$_SESSION['username']."' LIMIT " . $limitNumber . "," . $pageCounter " ORDER BY name ASC";

Gives the the next error:

Parse error: syntax error, unexpected '" ORDER BY name ASC"' (T_CONSTANT_ENCAPSED_STRING) in...

Any ideas to solve this?

Regards.

EDIT:

Sharing all the code for context purposes:

<?php

include 'includes/dbh.inc.php';
include 'user_session.php';

// Limit quantity of results per page

$pageCounter = 10;

// Show number of rows in the table

$sql = "SELECT * FROM incidents WHERE active = 1 AND username = '".$_SESSION['username']."'";
$result = mysqli_query($conn, $sql);
$rowNumbers = mysqli_num_rows($result);

//while ($row = mysqli_fetch_array($result)) {
//  echo $row['id'] . " " . $row['username'] . " " . $row['phone'] . "<br>";
//}

// Number of pages available

$numberPages = ceil($rowNumbers/$pageCounter);

// Determine in which page is the user

if (!isset($_GET['page'])) {
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Determine limit per page

$limitNumber = ($page-1)*$pageCounter;

// Show results

$sql = "SELECT * FROM incidents WHERE active = 1 AND username = '".$_SESSION['username']."' LIMIT " . $limitNumber . "," . $pageCounter " ORDER BY name ASC";
$result = mysqli_query($conn, $sql);

echo "<table>";
 echo "<tr><th>Name</th><th>TMC Location</th><th>Non-TMC Location</th><th>Country Code</th><th>Origin Offset</th><th>To Offset</th><th>Start Time</th><th>End Time</th><th>Updated</th><th>Created</th><th>AlertC</th><th>Note</th><th>Group</th><th>Action</th><th>Worked By</th><th>Date</th><th>Incident ID</th><th>Username</th><th>Edit</th><th>Delete</th></tr>";

while ($row = mysqli_fetch_array($result)) {
    $id = $row['id'];
    $name = $row['name'];
    $tmclocation = $row['tmclocation'];
    $nontmclocation = $row['nontmclocation'];
    $countrycode = $row['countrycode'];
    $ooffset = $row['ooffset'];
    $toffset = $row['toffset'];
    $stime = $row['stime'];
    $etime = $row['etime'];
    $updated = $row['updated'];
    $created = $row['created'];
    $alertc = $row['alertc'];
    $rcoby = $row['rcoby'];
    $note = $row['note'];
    $rcogroup = $row['rcogroup'];
    $action = $row['action'];
    $workedby = $row['workedby'];
    $date = $row['rcodate'];
    $username = $row['username'];
    $incidentid = $row['incidentid'];
  echo "<tr><td>".$name."</td><td>".$tmclocation."</td><td>".$nontmclocation."</td><td>".$countrycode."</td><td>".$ooffset."</td><td>".$toffset."</td><td>".$stime."</td><td>".$etime."</td><td>".$updated."</td><td>".$created."</td><td>".$alertc."</td><td>".$note."</td><td>".$rcogroup."</td><td>".$action."</td><td>".$workedby."</td><td>".$date."</td><td>".$incidentid."</td><td>".$username."</td><td><a href='myincidents.php?id=" . $row['id'] ."'>Edit</a></td><td><a id='a_id' href='editor/delete.php?id=" . $row['id'] ."' onClick='return confirm(\"Are you sure?\");'>Delete</a></td></tr>";
}

echo "</table>";

// Display number of pages

for ($page=1; $page <= $numberPages ; $page++) { 
    echo '<a href="myincidents.php?page=' . $page . '">' . $page . ' </a>';
}

?>

You should get yourself a proper IDE, it will highlight syntax error, save you some times with debugging. — catcon, Nov 12 '19 at 04:59
Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Nov 12 '19 at 10:15

score 2 · Accepted Answer · answered Nov 12 '19 at 05:04

2

order by first before doing the limit.

$sql = "SELECT * FROM incidents WHERE active = 1 AND username = '".$_SESSION['username']."' ORDER BY name ASC LIMIT " . $limitNumber . "," . $pageCounter " ";

answered Nov 12 '19 at 05:04

Ed Bangga

12,879
4
16
30

Thank you Metal, i deleted the final 2 double quotes and it worked perfect, i will read some documentation about ORDER BY and LIMIT in the same statement. – Diego Duarte Nov 12 '19 at 05:10

BadPiggie · Answer 2 · 2019-11-12T05:09:18.823

0

In this part, $pageCounter " ORDER BY name ASC" You are missing . after $pageCounter...

AND ORDER BY should be after LIMIT,

Because we need to order first, then get results...

Try this...

$sql = "SELECT * FROM incidents WHERE active = 1 AND username = '".$_SESSION['username']."' ORDER BY name ASC LIMIT " . $limitNumber . "," . $pageCounter " ";

edited Nov 12 '19 at 05:09

answered Nov 12 '19 at 04:56

BadPiggie

5,471
1
14
28

Now, gives me the next error: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in C:\xampp\htdocs\rco_development\viewer_user.php on line 44 And this is the code in that line: while ($row = mysqli_fetch_array($result)) { Let me know, if its better to share all the code in this PHP page. – Diego Duarte Nov 12 '19 at 04:59
Show your full code... – BadPiggie Nov 12 '19 at 05:00
Done, sorry about that... – Diego Duarte Nov 12 '19 at 05:02

ORDER BY and WHERE with variable and LIMIT

2 Answers2