This belongs on a PHP forum, but I've already struck out on three of
them. This is my last desperate attempt to figure out what the problem is.
The problem appears to be centered on this line...
ORDER BY '" . $_POST['reorder'] . "','" . $_POST['direction']."'";
....which gives me this error message:
Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2
F WHERE (C.Nationality is not null) AND (F.IDArea = \'eur\') ORDER BY
'','' - 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 '\'eur\') ORDER BY '',''' at line 10
Someone told me I can't use the word "order," because that's reserved
for some special use in MySQL. So I changed every instance of "order" to
"reorder," but I get the same result.
The whole idea is to fix my PHP switch so that it works with more than
one database table. I posted the entire code below, followed by the code
for a PHP switch that does work - though only for data from one table.
Any tips? Thanks.
<head>[DATABASE CONNECTION]</head>
<body>
<div class="formdiv">
<form action="remote.php" method="GET">
<select name="reorder">
<option value="cia_people.Name">Country, etc.</option>
<option value="cia_people.Pop">Population</option>
<option value="cia_people.Nationality">Nationality</option>
<option value="cia_people.NationalityPlural">Nationality:
Plural</option>
<option value="cia_people.NationalityAdjective">Nationalit y:
Adjective</option>
<option value="famarea2.IDParentReg">Geographic Region</option>
</select>
<input type="radio" name="direction" value="0">+
<input type="radio" name="direction" value="1">-
<input type="submit" name="submit" value="Submit">
</form>
</div>
<?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) FROM cia_people C, famarea2 F
WHERE C.IDArea = F.IDArea AND F.IDParent = "eur"
AND C.Nationality is not null');
if (($result) && (mysql_result ($result , 0) > 0)) {
// continue here with the code that starts
//$res = mysql_query ("SELECT * FROM type.....
} else {
die('Invalid query: ' . mysql_error());
}
switch($reorder)
{
case 1:
$reorder = 'cia_people.Name';
break;
case 2:
$reorder = 'cia_people.Pop';
break;
case 3:
$reorder = 'cia_people.Nationality';
break;
case 4:
$reorder = 'cia_people.NationalityPlural';
break;
case 5:
$reorder = 'cia_people.NationalityAdjective';
break;
case 6:
$reorder = 'famarea2.IDParentReg';
break;
default:
$reorder = 'cia_people.Name';
break;
}
switch($direction)
{
case 0:
$direction = 'ASC';
break;
case 1:
$direction = 'DESC';
break;
default:
$direction = 'ASC';
break;
}
$sql =
"SELECT F.IDArea,
C.IDArea,
C.Name,
C.Pop,
C.Nationality,
C.NationalityPlural,
C.NationalityAdjective
FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null)
AND (F.IDArea = \'eur\')
ORDER BY '" . $_POST['reorder'] . "','" . $_POST['direction']."'";
$res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' .
mysql_error());
echo '<table class="sortphp" id="tab_cia_people_peo">
<thead>
<tr><th>Country</th><th>X</th></tr>
</thead>
<tbody>';
//<!-- BeginDynamicTable -->
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo "<tr style=\"background-color:$c\" class='". $row['Name']
.."'><". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] .">
<td class='tdname' '". $row['Name'] ."'>". $row['Name'] ."</td>
<td> </td></tr>\n";
}
?>
</tr>
</tbody>
</table>
</body>
</html>
* * * * * * * * * *
The following script works - but just for one database table:
<head>[DATABASE CONNECTION]</head>
<body>
<div class="formdiv">
<form action="index.php" method="GET">
<select name="order">
<option value="1">Country, etc.</option>
<option value="2">Population</option>
<option value="3">Nationality</option>
<option value="4">Nationality: Plural</option>
<option value="5">Nationality: Adjective</option>
</select>
<input type="radio" name="direction" value="0">
Ascending
<input type="radio" name="direction" value="1">
Descending
<input type="submit" name="submit" value="Submit">
</form>
</div>
<?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) from cia_people');
if (($result) && (mysql_result ($result , 0) > 0)) {
// continue here with the code that starts
//$res = mysql_query ("SELECT * FROM type.....
} else {
die('Invalid query: ' . mysql_error());
}
{
$order = isset($_REQUEST['order']) ? intval($_REQUEST['order']) : 0;
switch($order)
/* The above line replaced thie following, which gave me an error message:
switch(intval($_REQUEST['order'])) */
{
case 1:
$order = 'Name';
break;
case 2:
$order = 'Pop';
break;
case 3:
$order = 'Nationality';
break;
case 4:
$order = 'NationalityPlural';
break;
case 5:
$order = 'NationalityAdjective';
break;
case 6:
default:
$order = 'Name';
break;
}
if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
// if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
$direction = ' DESC';
} else {
$direction = '';
}
/* $res = mysql_query('SELECT * FROM cia_people ORDER BY ' . $order .
$direction); */
$res = mysql_query('SELECT IDArea, Name, Pop, Nationality,
NationalityPlural, NationalityAdjective
FROM cia_people as c
WHERE c.Nationality is not null
ORDER BY c.' . $order . $direction);
echo '<table class="sortphp" id="tab_cia_people_peo">
<thead>
<tr><th>Country</th><th class="gray">Population</th><th>Nationality</th>
<th class="gray">Nat. (Plural)</th><th>Nat. (Adjective)</th></tr>
</thead>
<tbody>';
//<!-- BeginDynamicTable -->
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo "<tr style=\"background-color:$c\"><". $_SERVER['PHP_SELF']
..'?id='. $row['IDArea'] ."><td class='tdname'>". $row['Name'] ."</td>
<td class='gray'>" . number_format($row['Pop']) . "</td>
<td class='tdtext'>". $row['Nationality'] ."</td>
<td class='tdtextgray'>". $row['NationalityPlural'] ."</td>
<td class='tdtext'>". $row['NationalityAdjective'] ."</td></tr>\n";
}
}
?>
</tr>
</tbody>
</table>
</body>
</html>
PHP Gurus: SQL Syntax Error -
11-11-2004
, 12:55 AM
Linear Mode
