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