need help with search bar

[beginner123]

so i have created a search bar that displays any products entered eg guitars, it will display all the guitars from the database.
But i want to add a search bar for price (or maybe a drop down menu with different price ranges)

i have 2 search bars and right now its working if you enter a product and a price but its will only work if you enter the exact price

for example i search guitar and price of 500 - if thats in the database it will display.

But i want it to display any products that are close to the price (thats why i thought a drop down menu would be better but i'm not sure how what would work)

here my code:

Code: Select all

<?php

mysql_connect ( 'localhost', 'root', "", 'k00127082')
or die (mysql_error());mysql_select_db ("k00127082");
 
$term = $_POST['term'];
$secondterm = $_POST['secondterm'];
$sql = mysql_query("select * from products where product_name like '%$term%' AND price like '%$secondterm%' ");

?>

<table width="600" border="1">
<tr>

<th>Product Name </th>
<th>Description </th>
<th>Quantity on Hand</th>
<th>Price</th>
<th>Image</th>
</tr>



<?php

while ($row = mysql_fetch_array($sql))
{   

   $propID = $row['id'];
   $product_name = $row['product_name'];
   $product_description = $row['product_description'];
   $quantity_on_hand = $row['quantity_on_hand'];
   $price = $row['price'];
   $image = $row['image'];
   $formattedPrice = number_format($price, 2, '.', ',');
   
echo '<tr>';
echo "<td>$product_name</td>";
echo "<td>$product_description</td>";
echo "<td>$quantity_on_hand</td>";
echo "<td>€$formattedPrice</td>"; 
//echo "<td><a href='datadrilldown.php?propID=$propID'><img src='images/$image' /></td>";
echo "<td><a href='datadrilldown.php?propID=$propID'>$image</a></td>";
echo '<tr>';
   
}
echo '</table>';

if(!$row = mysql_fetch_array($sql))
{
   echo 'We do not have that product';
}

?>

[seandisanti]

instead of 'price like ....' decide how close you want it to be, for example, within 15, and then do something like...

Code: Select all

$variance=15;
$sql = mysql_query("select * from products where product_name like {$term} AND price between ({$secondterm}-{$variance}) and ({$secondterm}+{$variance})";

[beginner123]

ok thanks for the help but my code is still not working. I get an error on the line - while ($row = mysql_fetch_array($sql))
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\musicwebsite\search.php on line 70

Code: Select all

<?php

mysql_connect ( 'localhost', 'root', "", 'k00127082')
or die (mysql_error());mysql_select_db ("k00127082");
 
$term = $_POST['term'];
$secondterm = $_POST['secondterm'];
$variance=50;
$sql = mysql_query("select * from products where product_name like {$term} AND price between ({$secondterm}-{$variance}) and ({$secondterm}+{$variance}");

?>

<table width="600" border="1">
<tr>

<th>Product Name </th>
<th>Description </th>
<th>Quantity on Hand</th>
<th>Price</th>
<th>Image</th>
</tr>

<?php

while ($row = mysql_fetch_array($sql))
{   

   $propID = $row['id'];
   $product_name = $row['product_name'];
   $product_description = $row['product_description'];
   $quantity_on_hand = $row['quantity_on_hand'];
   $price = $row['price'];
   $image = $row['image'];
   $formattedPrice = number_format($price, 2, '.', ',');
   
echo '<tr>';
echo "<td>$product_name</td>";
echo "<td>$product_description</td>";
echo "<td>$quantity_on_hand</td>";
echo "<td>€$formattedPrice</td>"; 
echo "<td><a href='datadrilldown.php?propID=$propID'>$image</a></td>";
echo '<tr>';
   
}
echo '</table>';

if ($sql === FALSE)
echo "Mysql error: " . mysql_error();


?>


and heres the code for the search bars. I changed price from a text box to drop down menu:

Code: Select all

  <form action ='search.php' method='post'>
                      Search for product:<input type ="text" name="term" />
                       price:<select name="secondterm" id="secondterm">
                        <option value="100">1-100</option>
                        <option value="500">100-500</option>
                        <option value="1000">500-1000</option>
                        <option value="2000">1000-2000</option>
                        </select>
                       
                      <input type="submit" name="submit" value="Search" />
                    </form>

[beginner123]

any idea how to fix the error?

[egami]

$sql = mysql_query("yourstuffhere") or die ("ERROR: ".mysql_error());

[beginner123]

not sure if i put that code in the correct place but here is the error i got:
Mysql error: 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 '' at line 1

and i still get an error saying Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\musicwebsite\search.php on line 70

[egami]

Let's try this.

Code: Select all



$query = "SELECT * FROM products WHERE product_name LIKE '$term' AND price >= ('$secondterm' - '$variance') AND price <= ('$secondterm'+'$variance') ");
$result = mysql_query($query) or die (mysql_error());

if ($result) { 
  while ($row = mysql_fetch_assoc($result)) 
  {
    echo '<pre>' print_r($row); echo '</pre>';
  }
} else { 
  echo "Something is wrong with the query... ";
}

 

[beginner123]

it doesnt display anything. the page goes blank

Code: Select all

<?php

mysql_connect ( 'localhost', 'root', "", 'k00127082')
or die (mysql_error());mysql_select_db ("k00127082");
 
$term = $_POST['term'];
$secondterm = $_POST['secondterm'];
//$sql = mysql_query("select * from products where product_name like '%$term%' AND price like '%$secondterm%' ");
//$sql = mysql_query("select * from products where product_name like '%$term%' AND (price >= $secondterm-10 AND price <= $secondterm+10)");
$variance=50;
$query = mysql_query("select * from products where product_name like {$term} AND price between ({$secondterm}-{$variance}) and ({$secondterm}+{$variance}");
$result = mysql_query($query) or die (mysql_error());
?>

<table width="600" border="1">
<tr>

<th>Product Name </th>
<th>Description </th>
<th>Quantity on Hand</th>
<th>Price</th>
<th>Image</th>
</tr>

<?php
if ($result) {
while ($row = mysql_fetch_array($result))
{   
   echo print_r($row);

   $propID = $row['id'];
   $product_name = $row['product_name'];
   $product_description = $row['product_description'];
   $quantity_on_hand = $row['quantity_on_hand'];
   $price = $row['price'];
   $image = $row['image'];
   $formattedPrice = number_format($price, 2, '.', ',');
   
echo '<tr>';
echo "<td>$product_name</td>";
echo "<td>$product_description</td>";
echo "<td>$quantity_on_hand</td>";
echo "<td>€$formattedPrice</td>"; 
echo "<td><a href='datadrilldown.php?propID=$propID'>$image</a></td>";
echo '<tr>';
   
}
}
else{
echo "something went wrong";
}
echo '</table>';

?>


[seandisanti]

Let's try this.

Code: Select all



$query = "SELECT * FROM products WHERE product_name LIKE '$term' AND price >= ('$secondterm' - '$variance') AND price <= ('$secondterm'+'$variance') ");
$result = mysql_query($query) or die (mysql_error());

if ($result) { 
  while ($row = mysql_fetch_assoc($result)) 
  {
    echo '<pre>' print_r($row); echo '</pre>';
  }
} else { 
  echo "Something is wrong with the query... ";
}



i'm fairly new to this, but wouldn't

WHERE product_name LIKE '$term'

compare product_name to the literal string '$term' rather than the value contained therein?

Code: Select all

$query = "SELECT * FROM products WHERE product_name LIKE '" . $term . "' AND price >= ({$secondterm} - {$variance}) AND price <= ({$secondterm}+{$variance}) ");


also swapped the single quotes around numeric votes for brackets

[beginner123]

thanks it works now