Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

need help with search bar

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

need help with search bar

Postby beginner123 » Mon Oct 22, 2012 11:13 am

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

?>
beginner123
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am

Re: need help with search bar

Postby seandisanti » Wed Oct 24, 2012 8:52 am

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})";
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: need help with search bar

Postby beginner123 » Wed Oct 24, 2012 9:33 am

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
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am

Re: need help with search bar

Postby beginner123 » Fri Oct 26, 2012 7:50 am

any idea how to fix the error?
beginner123
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am

Re: need help with search bar

Postby egami » Fri Oct 26, 2012 8:25 am

$sql = mysql_query("yourstuffhere") or die ("ERROR: ".mysql_error());
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: need help with search bar

Postby beginner123 » Fri Oct 26, 2012 10:14 am

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
beginner123
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am

Re: need help with search bar

Postby egami » Fri Oct 26, 2012 11:01 am

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... ";
}

 
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: need help with search bar

Postby beginner123 » Fri Oct 26, 2012 11:33 am

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

?>
beginner123
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am

Re: need help with search bar

Postby seandisanti » Fri Oct 26, 2012 12:13 pm

egami wrote: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
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: need help with search bar

Postby beginner123 » Fri Oct 26, 2012 2:34 pm

thanks it works now :)
beginner123
New php-forum User
New php-forum User
 
Posts: 33
Joined: Fri Jan 20, 2012 10:39 am


Return to PHP coding => General

Who is online

Users browsing this forum: Baidu [Spider] and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.