Page 1 of 1

trying to create a search bar

Posted: Mon Oct 22, 2012 2:10 pm
by 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';
}

?>


Re: trying to create a search bar

Posted: Tue Oct 23, 2012 3:45 am
by Mordred
1. product_name like '%$term%' is grossly inefficient, it means "walk all rows in the table", which will get expensive for large tables (if yours won't grow that much, you can certainly live with it). Read on mysql fulltext search.
2. price is a number, so instead of treating it like text (price like '%$secondterm%'), treat it like a number: AND (price >= secondterm-10 AND price <= secondterm+10)
3. You have SQL injection with both parameters, read up on SQL injection (there's an article about it in my sig as well)

Re: trying to create a search bar

Posted: Tue Oct 23, 2012 9:53 am
by beginner123
ok so i changed the line to this:

Code: Select all

$sql = mysql_query("select * from products where product_name like '%$term%' AND (price >= secondterm-10 AND price <= secondterm+10)");
I know you said using '%$term%' is inefficient but it works right now so I am focusing on the price part and I'm getting this error:
warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\musicwebsite\search.php on line 68

which means the error is in the statement

Code: Select all

while ($row = mysql_fetch_array($sql))

Re: trying to create a search bar

Posted: Tue Oct 23, 2012 10:06 am
by Mordred
You have a syntax error in the sql statement. In the lines above do this (only while developing, do *not* output SQL errors in production!)

Code: Select all

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

Re: trying to create a search bar

Posted: Tue Oct 23, 2012 10:52 am
by beginner123
ok so i dont have any errors now but nothing comes up when i search

Heres all the code again:

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 >= $secondterm-10 AND price <= $secondterm+10)");

?>

<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 the code for the search bar:

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>

Re: trying to create a search bar

Posted: Wed Oct 24, 2012 4:23 am
by Mordred
Print the query before giving it to mysql_query, then copy-paste it into phpmyadmin and see what happens there.

Re: trying to create a search bar

Posted: Fri Oct 26, 2012 10:50 am
by beginner123
how do i print the query?

Re: trying to create a search bar

Posted: Mon Oct 29, 2012 5:58 am
by Mordred

Code: Select all

$s = "...";
echo "<hr>Query:<br>$s<hr> ";
$sql = mysql_query($s);