some problems with search query

[kbloem]

Hi,

 

i have the following problem. I use a script to enter data in a mysql database and for now i only have three variable...

 

<h2>Invoeren</h2>

<form action="insert.php" method="post" class="cmxform">

  <fieldset>

<legend>Gegevens</legend>

<table border="2">

<tr>

<td>1. </td>

<td><label for="merk">merk:</label></td>

<td><input id="merk" name="merk" /></td>

</tr>

<tr>

<td>2. </td>

<td><label for="cpu">cpu:</label></td>

<td><select name="cpu" id="cpu" />

<option value="">Maakt niet uit</option>

<option value="amd">amd</option>

<option value="intel">intel</option>

</select></td>

</tr>

<tr>

<td>3. </td>

<td><label for="hdd">hdd:</label></td>

<td><select name="hdd" id="hdd" />

<option value="">Maakt niet uit</option>

<option value="160GB">160GB</option>

<option value="320GB">320GB</option>

<option value="500GB">500GB</option>

</select></td>

</tr>

</table>

    <input type="submit" value="Verstuur" />

</fieldset> 

</form>

 

Data goes in to the database correctly but now i use the following search script...

 

 

<h2>zoeken</h2>

<form action="search.php" method="post" class="cmxform">

  <fieldset>

<legend>Gegevens</legend>

<table border="2">

<tr>

<td>1. </td>

<td><label for="merk">merk:</label></td>

<td><input id="merk" name="merk" /></td>

</tr>

<tr>

<td>2. </td>

<td><label for="cpu">cpu:</label></td>

<td><select name="cpu" id="cpu" />

<option value="0">Maakt niet uit</option>

<option value="amd">amd</option>

<option value="intel">intel</option>

</select></td>

</tr>

<tr>

<td>3. </td>

<td><label for="hdd">hdd:</label></td>

<td><select name="hdd" id="hdd" />

<option value="0">Maakt niet uit</option>

<option value="160GB">160GB</option>

<option value="320GB">320GB</option>

<option value="500GB">500GB</option>

</select></td>

</tr>

</table>

    <input type="submit" value="Verstuur" />

  </fieldset>

</form>

 

The values go correct to the search script because i checked this with an echo function. The search.php loks like this

 

<?php

    $con = mysql_connect("localhost","admin","t1tan1um");

    if (!$con)

    {

    die('Could not connect: ' . mysql_error());

    }

 

    mysql_select_db("model", $con);

 

$qry = "SELECT * FROM eigenschappen WHERE 1=1";

if (isset($_POST['merk']) && $_POST['merk'] != 0) {

$qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'";

}

if (isset($_POST['cpu']) && $_POST['cpu'] != 0) {

$qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'";

}

if (isset($_POST['hdd']) && $_POST['hdd'] != 0) {

$qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'";

}

 

$result = mysql_query($qry) or die(mysql_error()); // lelijke foutmelding, zelf mooier maken of $result door een if statement halen

 

while($row = mysql_fetch_array($result))

 

{

echo $row['merk'] . " " . $row['cpu'] . " " . $row['hdd'];

echo "<br />";

}

?>

 

When i search for hdd wich is last in de search list the query only returns the records wich contain that value. When the condition has to be for example Intel cpu AND 500GB it returns everyting with 500GB but also every kind of CPU.

 

Anyone?

[Crono]

Find:

 

if (isset($_POST['merk']) && $_POST['merk'] != 0) {
$qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'";
}
if (isset($_POST['cpu']) && $_POST['cpu'] != 0) {
$qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'";
}
if (isset($_POST['hdd']) && $_POST['hdd'] != 0) {
$qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'";
}

 

Replace with:

 

if (!(isset($_POST['merk']) && $_POST['merk'] == 0)) {
$qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'";
}
if (!(isset($_POST['cpu']) && $_POST['cpu'] == 0)) {
$qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'";
}
if (!(isset($_POST['hdd']) && $_POST['hdd'] == 0)) {
$qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'";
}

 

Using the != operator with 0 can cause some problems so the solution above should fix it.