trying to create a search bar

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
beginner123
Forum Commoner
Posts: 70
Joined: Fri Feb 24, 2012 9:56 am

trying to create a search bar

Post 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';
}

?>

User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: trying to create a search bar

Post 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)
beginner123
Forum Commoner
Posts: 70
Joined: Fri Feb 24, 2012 9:56 am

Re: trying to create a search bar

Post 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))
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: trying to create a search bar

Post 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();
beginner123
Forum Commoner
Posts: 70
Joined: Fri Feb 24, 2012 9:56 am

Re: trying to create a search bar

Post 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>
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: trying to create a search bar

Post by Mordred »

Print the query before giving it to mysql_query, then copy-paste it into phpmyadmin and see what happens there.
beginner123
Forum Commoner
Posts: 70
Joined: Fri Feb 24, 2012 9:56 am

Re: trying to create a search bar

Post by beginner123 »

how do i print the query?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: trying to create a search bar

Post by Mordred »

Code: Select all

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