Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Apr 2006
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search query, searching all fields in table

    Hi,

    I'm trying to create a search bar on my webpage. My web page is a clothes shop and I want to be able to search by brand, shop name, dept etc... Each of these is a field in the table product.

    prodId int(10) No auto_increment
    shopName varchar(30) No
    prodName varchar(40) No
    dept varchar(20) No
    brand varchar(20) No
    type varchar(20) No
    image varchar(60) No
    price double(3,2) No 0.00
    NoInStock


    This is the code I am using:


    Code:
    Code:
    <?php 
    
    $host= 
    
    // Get the search variable from URL 
    $var = @$_GET['q'] ; 
    $trimmed = trim($var); //trim whitespace from the stored variable 
    
    // rows to return 
    $limit=10; 
    
    // check for an empty string and display a message. 
    if ($trimmed == "") 
    { 
    echo "<p><b>Please enter a search...<B></p>"; 
    exit; 
    } 
    
    // check for a search parameter 
    if (!isset($var)) 
    { 
    echo "<p><b>We dont seem to have a search parameter!<b></p>"; 
    exit; 
    } 
    
    include "db.php"; 
    
    
    $query = "select * from product where dept like \"%$trimmed%\" order by dept"; 
    
    $query = "select * from product where type like \"%$trimmed%\" order by type"; 
    
    $query = "select * from product where prodName like \"%$trimmed%\" order by prodName"; 
    
    $query = "select * from product where shopName like \"%$trimmed%\" order by shopName"; 
    
    $query = "select * from product where brand like \"%$trimmed%\" order by brand"; 
    
    //db 
    $result = mysql_query($query,$conn) or die(mysql_error()); 
    
    //get the number of rows in the result set; should be 1 if a match 
    if (mysql_num_rows($result) >= 1) { 
    
    $image = mysql_result($result, 0, 'image'); 
    $prodName = mysql_result($result, 0, 'prodName'); 
    $price = mysql_result($result, 0, 'price'); 
    
    
    
    $numresults=mysql_query($query); 
    $numrows=mysql_num_rows($numresults); 
    
    
    if ($numrows == 0) 
    { 
    echo "<h4>Results</h4>"; 
    echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
    when I use this code it only returns results for the last query in the list which in this case is

    $query = "select * from product where brand like \"%$trimmed%\" order by brand";

    So the only results it returns is when you enter a brand name in, when I enter in a shop name or dept name it says there are no results.

    How can I get it to return results for all of the fields. (Please try to make your answer as simple as possible because I'm really new to this!)

  • #2
    Regular Coder
    Join Date
    Jul 2006
    Posts
    112
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Well, your are replacing your query string each you set it equal to something so naturally the final value would be brand name. What you would want to do is set up a way of asking what field they would like to search using a drop down menu (like amazon.com has "Search...Books\Popular Music\DVDs") and then check what they selected and then something like this...

    PHP Code:
    switch($category)
    {
        case 
    'dept':
        
    $query "select * from product where dept like \"%$trimmed%\" order by dept";
        break; 
        case 
    'type':
        
    $query "select * from product where type like \"%$trimmed%\" order by type"
        break;
        ...
        default:
            print 
    "Error, no category selected\n";
    }

    $result mysql_query($query,$conn) or die(mysql_error()); 
    Or you could even use what they select as the variable in one statement like so...:
    PHP Code:
    $query "select * from product where \"$category\" like \"%$trimmed%\" order by \"$category\"";

    $result mysql_query($query,$conn) or die(mysql_error()); 
    It is crucial that you validate and sanitize all user input before you pass it to an SQL query! As your code stands now, you are leaving yourself wide open for an easy SQL injection attack. You should look into stored procedures or at least use a function similar to mysql_real_escape_string. For more information see here. Hope this information helps!

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Code:
    $query = "select * from product where dept like \"%$trimmed%\" OR type like \"%$trimmed%\" OR prodName like \"%$trimmed%\" OR shopName like \"%$trimmed%\" OR brand like \"%$trimmed%\" order by dept, type, prodName, shopName, brand";
    You may want to sort differently.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •