Nazir Jauhar Nazir Jauhar - 11 days ago 4
MySQL Question

PHP MySQL order table code error "cannot parse query"

I'm trying to get a table to sort via its header title. But I get the cannot parse query. Is it something I'm doing wrong in the MySQL?

I have to order a table of Jargon for work which contains thousands of records. So I'm just practising it at home to get it right. However at the moment I seem to be getting stuck on that MySQL bit. I followed the tips on a different website which had this code but still getting stuck a explanation of why this isn't working will help.

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="../demo.css" rel="stylesheet" type="text/css" />
</head>
<body>

<?php
function makeHeaderLink($value, $key, $col, $dir) {
$out = "<a href=\"" . $_SERVER['SCRIPT_NAME'] . "?c=";
//set column query string value
switch($key) {
case "Acronym":
$out .= "1";
break;
case "Full Name":
$out .= "2";
break;
case "What":
$out .= "3";
break;
default:
$out .= "0";
}

$out .= "&d=";

//reverse sort if the current column is clicked
if($key == $col) {
switch($dir) {
case "ASC":
$out .= "1";
break;
default:
$out .= "0";
}
}
else {
//pass on current sort direction
switch($dir) {
case "ASC":
$out .= "0";
break;
default:
$out .= "1";
}
}

//complete link
$out .= "\">$value</a>";

return $out;
}

switch($_GET['c']) {
case "1":
$col = "Acronym";
break;
case "2":
$col = "Full_Name";
break;
case "3":
$col = "What";
break;
}

if($_GET['d'] == "1") {
$dir = "DESC";
}
else {
$dir = "ASC";
}

if(!$link = mysql_connect("localhost", "username", "password")) {
echo "Cannot connect to db server";
}
elseif(!mysql_select_db("nazir_jargon")) {
echo "Cannot select database";
}
else {

if(!$rs = mysql_query("SELECT 'Acronym', 'Full Name', 'What' * FROM jargon1 ORDER BY $col $dir")) {
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {
echo "<table class=\"bordered\" cellspacing=\"0\">\n";
echo "<tr>";
echo "<th>" . makeHeaderLink("Acronym", "Acronym", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("Full Name", "Full_Name", $col, $dir) . "</th>";
echo "<th>" . makeHeaderLink("What", "What", $col, $dir) . "</th>";
echo "</tr>\n";
while($row = mysql_fetch_array($rs)) {
echo "<tr><td>$row[person_id]</td><td>$row[Acronym]</td><td>$row[Full_Name]</td><td>$row[What]</td></tr>\n";
}
echo "</table><br />\n";
}
}
?>
</body>



Answer

Remove single quote & * from the query so that query should look like below. I hope it will work.

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir

If it won't work,

Can you print

echo "SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir";

and let me know what you get?

Note: I think you wanted to use backticks(`), however mistakenly used single quote.


Edit 1

As you see

echo "SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir";

prints

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY ASC`

That means $col is not printing.. please check what is going wrong with $col

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY columnNameMissing ASC`
                                                      ^^^^^^^^^^^^^^^^^