... inline ... for ease:
At 23:53 31/03/2005, Juffermans, Jos wrote:
So far, I have found a few mistakes in your SQL:
$query .= " OR `variant` LIKE '%".$_GET['search']."%'"; Why do you have backtick-quotes around variant? Know that backticks are used to call system commands. You also use these backticks in the ORDER BY lines. My guess is the backticks are causing your problems.
Because they have to be there for MySQL to understand the query ? I sometimes have field names where part of the name is the same as a command, so without the ticks MySQL can't understand the queries. They're actually in the documentation for MySQL, so I don't get why it would cause problems. I always use them, and when I don't, I have problems. And the ORDER clause DOESN'T WORK without them... ever, when I try to do without them and have more than order clause.
$query .= " AND make.makeID='".$_GET['make']."'";
...
$query .= " AND make.makeID<>0";
If make.makeID is a number field, why is the value in quotes for the cases
where $_GET['make'] is set? Same with socket.socketID a few lines down.
Because it doesn't matter, function-wise, if they're there or not for numbers. And it's easier and faster to just put them in for everything, than remembering to exclude them for numeric fields, and include them for non-numeric. Yeah, it takes MySQL a little longer to have to convert from text to number and such, but it's a microscopic amount of time.
Unrelated but very important, putting form variables (either GET or POST) directly into a query is dangerous. If I create a copy of your form and fill $_GET['search'] with eg: a%';DELETE * FROM model;SELECT * FROM model WHERE something LIKE '%a
Your query will be like this: SELECT ...... FROM .... WHERE .... AND model.modelName LIKE '%a%';DELETE * FROM model;SELECT * FROM model WHERE something LIKE '%a OR VARIANT ....
You can't trust incoming variables.
Yeah, I know ... this code was from before I learned how to check and escape form fields though, just didn't put the error checking/correction into the code yet ... haven't worked on this thing for like a year cuz I got sick of it after putting the several hundred rows manually into the database. I wrote this script originally before I knew anything about using relational tables in SQL, I simply just changed the query code to use the relations instead, and that's when it died.
That said, the query worked when being put directly into a MySQL prompt, albeit extremely slow and far beyond what's normal. But I gave up trying to fix it as I couldn't find the problem, so I dumped the entire query part of the code, and started over with new code, from another script that uses the same tables, but have never caused me any problems ... and it works now. And almost 10 times faster than before. It can pull the entire 882 rows, and generate the entire page, in 12 seconds. And all I did was use a different query as basis, write completely new query generating code from scratch, and add some functionality to the form ...
I haven't added the LIKE part to the new code, but I've changed the field select boxes to multi-select instead of single-select, and the order picking now lets you choose 3 orders, instead of just 1, so the code's a bit different now. I know it looks like it does some double-checking of the same thing, but it's to be damn sure it doesn't run the code parts unless it has to. And the code works, and much, much faster than the old one. In fact it takes it longer to ship the data over my network than it takes the server to run the script. I don't get why it's so much faster now, as there's very little difference in the query, but it does, and it's all I really wanted. It still needs some fine-tuning to handle the addslashes and such, but I haven't gotten to it yet.
// array holding possible fields for sorting. It's used in 3 drop-downs in the query form.
$arrsort = array('Make','Model','Variant','Clock','Multiplier','FSB','FSBx','L1','L2','L3','Vcore','Vcache','Socket/Slot');
if ($_POST['do'] == 'search') {
$filterwhere = ''; if (! empty($_POST['make'])) {
if (is_array($_POST['make'])) {
$arrmake = array_values($_POST['make']);if (! in_array('all',$arrmake)) {
if (count($arrmake) > 1) {
$filterwhere = "cpu_maker.makeID IN('".implode("','",$arrmake)."') AND";
} else {
$filterwhere = "cpu_maker.makeID='{$arrmake[0]}' AND";
}
}
}
}
if (! empty($_POST['socket'])) {
if (is_array($_POST['socket'])) {
$arrsocket = array_values($_POST['socket']);
if (! in_array('all',$arrsocket)) {
if (count($arrsocket) > 1) {
$filterwhere .= " cpu_socket.socketID IN('".implode("','",$arrsocket)."') AND";
} else {
$filterwhere .= " cpu_socket.socketID='{$arrsocket[0]}' AND";
}
}
}
}
$filterorder = '';
for ($i = 0; $i < 3; $i++) {
$num = $i+1;
$sort = 'sort'.$num;
$order = 'order'.$num; switch ($_POST[$sort]) {
case 0:
$filterorder .= "cpu_maker.makeName";
break;
case 1:
$filterorder .= "cpu_model.modelName";
break;
case 2:
$filterorder .= "`variant`";
break;
case 3:
$filterorder .= "`clock`";
break;
case 4:
$filterorder .= "`multi`";
break;
case 5:
$filterorder .= "`fsb`";
break;
case 6:
$filterorder .= "`fsb2`";
break;
case 7:
$filterorder .= "`l1`";
break;
case 8:
$filterorder .= "`l2`";
break;
case 9:
$filterorder .= "`l3`";
break;
case 10:
$filterorder .= "`vcore`";
break;
case 11:
$filterorder .= "`vcache`";
break;
case 12:
$filterorder .= "cpu_socket.socketName";
break;
}
$filterorder .= ' '.$_POST[$order];
if ($i < 2) {
$filterorder .= ',';
}
}// build query
$query = "SELECT cpu_maker.makeID,makeName,cpu_model.modelID,modelName,fsb2,socketName,cpuID,variant,clock,multi,fsb,l1,l2,l3,vcore,vcache
FROM cpu_maker,cpu_model,cpu_socket,cpu_cpus
WHERE $filterwhere cpu_maker.makeID=cpu_model.makeID AND cpu_model.modelID=cpu_cpus.modelID AND cpu_socket.socketID=cpu_model.socketID
ORDER BY $filterorder";
$result = mysql_query($query) or die('Unable to run query<br>'.mysql_error().'<br><br>'.$query.'<br>');
$numrows = mysql_num_rows($result); }
The new form that goes with this code:
<form action="<?php echo($_SERVER['PHP_SELF']); ?>" method="post">
<input type="Hidden" name="do" value="search">
<table width="560" cellpadding="0" cellspacing="0" border="0">
<tr>
<td width="13" height="30" align="left"><img src="../images/menu_hdn_left.gif" width="13" height="30" border="0"></td>
<td align="center" valign="middle" background="../images/menu_hdn_back.gif" class="menu_hdn">Search for CPUs...</td>
<td width="13" height="30" align="left"><img src="../images/menu_hdn_right.gif" width="13" height="30" border="0"></td>
</tr><tr>
<td colspan="3" height="5"><img src="../images/bspace.gif" width="5" height="5" border="0"></td>
</tr><tr>
<td height="10" align="right"><img src="../images/silver_boxcrn_tl.gif" width="10" height="10" border="0"></td>
<td height="10" background="../images/silver_box_top.gif"><img src="../images/bspace.gif" width="2" height="10" border="0"></td>
<td width="10" height="10"><img src="../images/silver_boxcrn_tr.gif" width="10" height="10" border="0"></td>
</tr><tr>
<td width="10" align="right" background="../images/silver_box_left.gif" class="menu_boxleft"><img src="../images/bspace.gif" width="2" height="10" border="0"></td>
<td align="left" background="../images/backgrnd.gif">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top">
<table border="0" cellpadding="0" cellspacing="5">
<tr>
<td align="left"><span class="smlbldtxt">Search for:</span><br>
<input type="Text" name="search" size="48"<?php if (! empty($_POST['search'])) { echo(' value="'.$_POST['search'].'"'); } ?>></td>
<td valign="bottom"><input type="Submit" value="Search"></td></td>
</tr>
</table>
</td>
</tr><tr>
<td align="center" valign="top">
<table border="0" cellpadding="0" cellspacing="5">
<tr>
<td rowspan="3" align="left" valign="top"><span class="smlbldtxt">Make:</span><br>
<select name="make[]" size="5" multiple>
<option value="all">All
<?php
$itemquery = "SELECT makeID,makeName
FROM cpu_maker
ORDER BY `makeName` ASC";
$itemresult = mysql_query($itemquery) or die('Unable to get items<br>'.mysql_error());
while ($item = mysql_fetch_array($itemresult)) {
echo('<option value="'.$item['makeID'].'"');
if (is_array($arrmake) && in_array($item['makeID'],$arrmake)) {
echo(' selected');
}
echo('>'.$item['makeName']."\n");
}
?>
</select></td>
<td rowspan="3" align="left" valign="top"><span class="smlbldtxt">Socket/slot:</span><br>
<select name="socket[]" size="5" multiple>
<option value="all">All
<?php
$itemquery = "SELECT socketID,socketName
FROM cpu_socket
ORDER BY `socketName` ASC";
$itemresult = mysql_query($itemquery) or die('Unable to get items<br>'.mysql_error());
while ($item = mysql_fetch_array($itemresult)) {
echo('<option value="'.$item['socketID'].'"');
if (is_array($arrsocket) && in_array($item['socketID'],$arrsocket)) {
echo(' selected');
}
echo('>'.$item['socketName']."\n");
}
?>
</select></td>
<td align="left" valign="top"><span class="smlbldtxt">Sort by:</span><br>
<select name="sort1">
<?php
for ($i = 0; $i < count($arrsort); $i++) {
echo('<option value="'.$i.'"');
if ($_POST['sort1'] == $i || (! isset($_POST['sort1']) && $i == 0)) {
echo(' selected');
}
echo('>'.$arrsort[$i]."\n");
}
?>
</select></td>
<td align="left" valign="middle"><input type="Radio" name="order1" value="ASC"<?php if ($_POST['order1'] == 'ASC' || (! isset($_POST['order1']))) { echo(' checked'); } ?>> <span class="smlbldtxt">Ascending</span><br>
<input type="Radio" name="order1" value="DESC"<?php if ($_POST['order1'] == 'DESC') { echo(' checked'); } ?>> <span class="smlbldtxt">Descending</span></td>
</tr><tr>
<td align="left" valign="top"><span class="smlbldtxt">Then by:</span><br>
<select name="sort2">
<?php
for ($i = 0; $i < count($arrsort); $i++) {
echo('<option value="'.$i.'"');
if ($_POST['sort2'] == $i || (! isset($_POST['sort2']) && $i == 1)) {
echo(' selected');
}
echo('>'.$arrsort[$i]."\n");
}
?>
</select></td>
<td align="left" valign="middle"><input type="Radio" name="order2" value="ASC"<?php if ($_POST['order2'] == 'ASC' || (! isset($_POST['order2']))) { echo(' checked'); } ?>> <span class="smlbldtxt">Ascending</span><br>
<input type="Radio" name="order2" value="DESC"<?php if ($_POST['order2'] == 'DESC') { echo(' checked'); } ?>> <span class="smlbldtxt">Descending</span></td>
</tr><tr>
<td align="left" valign="top"><span class="smlbldtxt">Then by:</span><br>
<select name="sort3">
<?php
for ($i = 0; $i < count($arrsort); $i++) {
echo('<option value="'.$i.'"');
if ($_POST['sort3'] == $i || (! isset($_POST['sort3']) && $i == 3)) {
echo(' selected');
}
echo('>'.$arrsort[$i]."\n");
}
?>
</select></td>
<td align="left" valign="middle"><input type="Radio" name="order3" value="ASC"<?php if ($_POST['order3'] == 'ASC' || (! isset($_POST['order3']))) { echo(' checked'); } ?>> <span class="smlbldtxt">Ascending</span><br>
<input type="Radio" name="order3" value="DESC"<?php if ($_POST['order3'] == 'DESC') { echo(' checked'); } ?>> <span class="smlbldtxt">Descending</span></td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td width="10" background="../images/silver_box_right.gif" class="menu_boxright"><img src="../images/bspace.gif" width="2" height="10" border="0"></td>
</tr><tr>
<td height="10" align="right"><img src="../images/silver_boxcrn_bl.gif" width="10" height="10" border="0"></td>
<td height="10" background="../images/silver_box_bottom.gif"><img src="../images/bspace.gif" width="2" height="10" border="0"></td>
<td width="10" height="10"><img src="../images/silver_boxcrn_br.gif" width="10" height="10" border="0"></td>
</tr>
</table>
</form>
Rene
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
