SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching for keywords in 2 fields rather than 1

    I have a search functinality on my site, and I got it working and I'm quite happy with it.
    I got it working where you compared the keyword to one field in a table, in this case the title field.
    But what I would like it to do is search for the keyword in the description field too, so I'm trying to do using the code below.

    PHP Code:
    $query "select stock_Id, stock_Name, stock_Description from stock where (stock_Name like \"%$trimmed%\") and (stock_Description like \"%$trimmed%\") order by stock_Id";
     
    $numresults=mysql_query($query);
     
    $numrows=mysql_num_rows($numresults); 
    and this is the bit that is outputting the results, part of it anyway. I will show the full code at the end if anybody needs it.

    PHP Code:
    while ($rowmysql_fetch_array($result)) {
      
    $title $row["stock_Name"];
      
    $description $row["stock_Description"];

      echo 
    " = <strong>$count</strong></p><ul id=searchUL><li id=searchLI><a href=#>$title</a> - ".substr($row['stock_Description'],0,150)."...</li></ul></p>";
      
    $count++;
      } 
    Its not returning an error, but it doesnt seem to be picking up the keywords in stock_Description.

    Here is the lot:

    PHP Code:
      // 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 == "")
      {
    ob_start(); 
    // ensures anything dumped out will be caught  
    // do stuff here 
    $url 'http://www.x.co.uk;
    // this can be set based on whatever  
    // clear out the output buffer 
    while (ob_get_status())  
    {     
    ob_end_clean(); 
    }  
    // no redirect 
    header( "Location: $url" ); 
      }

    // check for a search parameter
    if (!isset($var))
      {
      echo "<p>We dont seem to have a search parameter!</p>";
      exit;
      }

    //connect to your database ** EDIT REQUIRED HERE **
    //mysql_connect("localhost","username","password"); //(host, username, password)

    //specify database ** EDIT REQUIRED HERE **
    //mysql_select_db("database") or die("Unable to select database"); //select which database we'
    re using

    // Build SQL Query  
    $query "select stock_Id, stock_Name, stock_Description from stock where (stock_Name like \"%$trimmed%\") and (stock_Description like \"%$trimmed%\") order by stock_Id"// EDIT HERE and specify your table and field names for the SQL query

     
    $numresults=mysql_query($query);
     
    $numrows=mysql_num_rows($numresults);

    // If we have no results, offer a google search as an alternative

    if ($numrows == 0)
      {
      echo 
    "<p>Results</p>";
      echo 
    "<p>Sorry, your search: &quot;" $trimmed "&quot; returned zero results</p>";

    // google
     //echo "<p><a href=\"http://www.google.com/search?q=" 
      //. $trimmed . "\" target=\"_blank\" title=\"Look up 
      //" . $trimmed . " on Google\">Click here</a> to try the 
      //search on google</p>";
      
    }

    // next determine if s has been passed to script, if not use 0
      
    if (empty($s)) {
      
    $s=0;
      }

    // get results
      
    $query .= " limit $s,$limit";
      
    $result mysql_query($query) or die("Couldn't execute query");

    // display what the person searched for
    echo "<p>You searched for:<strong> &quot;" $var "&quot;</strong></p>";

    // begin to show results set
    echo "<p><strong>'$trimmed'</strong> search results";
    $count $s;

    // now you can display the results returned
      
    while ($rowmysql_fetch_array($result)) {
      
    $title $row["stock_Name"];
      
    $description $row["stock_Description"];

      echo 
    " = <strong>$count</strong></p><ul id=searchUL><li id=searchLI><a href=#>$title</a> - ".substr($row['stock_Description'],0,150)."...</li></ul></p>";
      
    $count++;
      }

    $currPage = (($s/$limit) + 1);

    //break before paging
      
    echo "<br />";

      
    // next we need to do the links to other results
      
    if ($s>=1) { // bypass PREV link if s is 0
      
    $prevs=($s-$limit);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt; 
      Prev 10</a>&nbsp&nbsp;"
    ;
      }

    // calculate number of pages needing links
      
    $pages=intval($numrows/$limit);

    // $pages now contains int of pages needed unless there is a remainder from division

      
    if ($numrows%$limit) {
      
    // has remainder so add one page
      
    $pages++;
      }

    // check to see if last page
      
    if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

      
    // not last page so give NEXT link
      
    $news=$s+$limit;

      echo 
    "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
      }

    $a $s + ($limit) ;
      if (
    $a $numrows) { $a $numrows ; }
      
    $b $s ;
      echo 
    "<p>Showing results $b to $a of $numrows</p>"

  2. #2
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Its not returning an error, but it doesnt seem to be picking up the keywords in stock_Description.
    If you output the query and run it in phpmyadmin (or whatever you use for SQL only interaction), do you get the results you expect?

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I am using phpmyadmin, thanks for the reply.

    Umm, I know this is going to sound stupid, but can I literally paste this below into to it and run:

    PHP Code:
    $query "select stock_Id, stock_Name, stock_Description from stock where (stock_Name like \"%$trimmed%\") and (stock_Description like \"%$trimmed%\") order by stock_Id"

  4. #4
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    no, after that line you can do: echo $query; then copy - paste the query in phpmyadmin

  5. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, posted this in:

    PHP Code:
    select stock_Idstock_Namestock_Description from stock where (stock_Name like "Antique" and (stock_Description like "Antique"order by stock_Id 
    And it came back with a syntax error

    #1064 - 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 'order by stock_Id LIMIT 0, 30' at line 1

  6. #6
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK sorry centered effect, did that the queery came out as:

    select stock_Id, stock_Name, stock_Description from stock where (stock_Name like "%Mahogany%") and (stock_Description like "%Mahogany%") order by stock_Id

    so pasted it in yes it outputted fine, it showed the one with mahogany in the title.

    But I then tried it with Antique, and it only outputted the one entry, when there is one entry with Antique in the title, and one entry with Antique in the description.

    And that is what seems to be happening on the site.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    where (stock_Name like "%Mahogany%") AND (stock_Description like "%Mahogany%")
    AND means that the word must be present in both columns.

    If you want to find all rows that have the word in stock_Name and/or stock_description, then you have to use OR:
    Code:
    where (stock_Name like "%Mahogany%" OR stock_Description like "%Mahogany%")

  8. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah i think i can see my problem, Im asking where its in the title and in the description, its not searching where its either in the title or the description an showing all results.

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    guido lol, your on the ball again, thank you, you just beat me to it, shows Im improving a little bit at least.

    Cheers

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by multichild View Post
    guido lol, your on the ball again, thank you, you just beat me to it, shows Im improving a little bit at least.

    Cheers
    Beat you by a second
    Yes, you did find the cause, very good.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guido2004,

    What if the user puts say 2 keywords together, and they dont exist together in the fields.

    For example: mahogany furniture

    I tried it and it returned no results, when both keywords exist on their own in the fields but not together...

    Or is it a case that this cant happen, but will have to put a rule by the side of the search option to let the user know.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by multichild View Post
    For example: mahogany furniture
    split them up with php
    Code:
    WHERE stock_Name LIKE '%mahogany%' 
       OR stock_Name LIKE '%furniture%' 
       OR stock_Description LIKE '%mahogany%'
       OR stock_Description LIKE '%furniture%'
    also, please don't use (unnecessary (parentheses))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, but the thing is I cant do that as I dont know what they are going to put in to search with.

    What I was trying to say is, what if they put a small sentence in, it doesnt do the same job as only putting one keyword in.

    Does the trim white space function put pay to a number of keywords, as it will join them up as one, or should I put a bit of help there to say only search using one keyword.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by multichild View Post
    Hi, but the thing is I cant do that as I dont know what they are going to put in to search with.
    whatever the user enters, split it up on the spaces, and generate the corresponding ORs as necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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