#1
  1. No Profile Picture
    The Dude Abides
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Feb 2000
    Location
    grass valley,ca
    Posts
    1,062
    Rep Power
    16
    Ok here's the situation: description field (product description) that needs to be searched by text input from site visitor.

    Problem: Even with the wildcards all over the place the search won't find results unless the search terms entered match the sequence of the keywords in the database.

    Example db field (varchar): Switch Three Way 15A 120V Back...

    Example search: switch way

    This search gives no results, while "switch three way" does??

    SQL statement: $query = mysql_query("SELECT * FROM $usertable WHERE $column LIKE '%$search%' ");

    I can't use multiple input fields.
    Suggestions?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    81
    Rep Power
    15
    Just split up your query input and perform a conjunction of clauses on the words. In a kind of bastardised Perl/PHP (sorry, don't do PHP, but I've used PHP functions where I know them):
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    @words = split(/ /, $search);
    $qry_str = "select * from $usertable where ";
    for( $i = 0; $i < $#words; $i++ ) {
    $qry_str .= "$column LIKE '%$words[i]%'";
    if( $i < $#words - 1 ) {
    $qry_str .= ' and ';
    }
    }
    $query = mysql_query( $qry_str );
    [/code]

    [This message has been edited by christucker2 (edited August 16, 2000).]

    [This message has been edited by christucker2 (edited August 16, 2000).]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    <?
    $searchlist=split("[ ]{1,}", $incoming); // incoming is form data

    $query="SELECT * from table_name where ";

    for($i=0; $i<count($searchlist); $i++)
    {
    $query.="column_name like '%$searchlist[$i]%' AND ";
    }
    $query=substr($query, 0, -4); // strip off last "AND ";

    // run the query...

    ?>
  6. #4
  7. No Profile Picture
    The Dude Abides
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Feb 2000
    Location
    grass valley,ca
    Posts
    1,062
    Rep Power
    16
    Chris and Kyuzo, thanx for the code. I've got it almost working but I'm not getting any results. I understand what you are doing up to a point, but I'm not sure what sql command should be in the while statement. I'm pretty sure that's where the problem resides, but I think I've been staring at this for too long...

    I put the code in up to the print statements. I've tried a wide variety of syntax changes, and other stuff just in case that was the problem, but all gave the same result, nada.

    <?

    //error message (not found message)

    $XX = "Sorry, No Records Found";

    $searchlist=split("[ ]{1,}", $search); // search is incoming form data

    $query = "SELECT * FROM $usertable WHERE ";

    for($i=0; $i<count($searchlist); $i++)
    {
    $query = "$column LIKE '%$searchlist[$i]%' AND ";
    }
    $query = substr($query, 0, -4); // strip off last "AND ";

    while ($row = mysql_fetch_array($query))//tried mysql_query
    {
    $variable1=$row["Description"];
    print ("<center>"); //begin print statements

    Thank you for your help
    chris

    [This message has been edited by thedude (edited August 16, 2000).]

    [This message has been edited by thedude (edited August 16, 2000).]
  8. #5
  9. No Profile Picture
    The Dude Abides
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Feb 2000
    Location
    grass valley,ca
    Posts
    1,062
    Rep Power
    16
    Thanks for all your help, but I got it with a little hacking. For anyone else with this situation the code below is ready to roll, all it needs is your print statements for the results and a function for no results.

    Enjoy!!


    <?
    //Putting all individual words in an array
    if ($search) // form input data
    {
    $search = trim($search); // delete surrounding spaces
    $search = explode(" ", $search); // make array of individual words
    }

    //Definition of the query
    $selectquery = "SELECT * FROM tablename ";

    //Cycle through the words-array if there are word(s) filled in
    if (isset($search))
    {
    $selectquery .= " WHERE ";
    for($i = 0; $i < count($search); $i++)
    {
    $selectquery .= "column_name LIKE '%" . $search[$i] . "%'";
    if ($i < count($search)-1)
    {
    $selectquery .= " AND ";
    }
    }
    }

    $result = mysql_query ($selectquery)
    or die ("Query failed");
    while ($row = mysql_fetch_array($result))
    {
    //put your print statements here
    ?>
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Bartlett, TN, USA
    Posts
    3
    Rep Power
    0
    I applied the code below to my own situation and it works well. Would you make a suggestion as to how to apply " ORDER BY fieldname" for sorting the results?

    thedude - thank you for posting this code tidbit.

    Scott.

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by thedude:
    Thanks for all your help, but I got it with a little hacking. For anyone else with this situation the code below is ready to roll, all it needs is your print statements for the results and a function for no results.

    Enjoy!!


    <?
    //Putting all individual words in an array
    if ($search) // form input data
    {
    $search = trim($search); // delete surrounding spaces
    $search = explode(" ", $search); // make array of individual words
    }

    //Definition of the query
    $selectquery = "SELECT * FROM tablename ";

    //Cycle through the words-array if there are word(s) filled in
    if (isset($search))
    {
    $selectquery .= " WHERE ";
    for($i = 0; $i < count($search); $i++)
    {
    $selectquery .= "column_name LIKE '%" . $search[$i] . "%'";
    if ($i < count($search)-1)
    {
    $selectquery .= " AND ";
    }
    }
    }

    $result = mysql_query ($selectquery)
    or die ("Query failed");
    while ($row = mysql_fetch_array($result))
    {
    //put your print statements here
    ?>
    [/quote]

  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    51
    Rep Power
    15
    basically, do what i put the --> around.

    <?
    //Putting all individual words in an array
    if ($search) // form input data
    {
    $search = trim($search); // delete surrounding spaces
    $search = explode(" ", $search); // make array of individual words
    }

    //Definition of the query
    $selectquery = "SELECT * FROM tablename ";

    //Cycle through the words-array if there are word(s) filled in
    if (isset($search))
    {
    $selectquery .= " WHERE ";
    for($i = 0; $i < count($search); $i++)
    {
    $selectquery .= "column_name LIKE '%" . $search[$i] . "%'";
    if ($i < count($search)-1)
    {
    $selectquery .= " AND ";
    }
    }
    }

    --> $selectquery .= " order by (field) (ASC/DESC) "; <---

    $result = mysql_query ($selectquery)
    or die ("Query failed");
    while ($row = mysql_fetch_array($result))
    {
    //put
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Bartlett, TN, USA
    Posts
    3
    Rep Power
    0
    Thank you for your suggestion. This is the code I used to make the sort work in my instance. This code also shows my attempt at checking the if (isset($search)) function for empty search results to provide a "Could not find any records message" to my users. I think the code is correct - it's just not working. Are there hidden nuances to using ISSET this way?

    ---

    //Putting all individual words in an array
    if ($search!="") // form input data
    {
    {
    $search = trim($search); // delete surrounding spaces
    $search = explode(" ", $search); // make array of individual words
    }

    //Definition of the query
    $selectquery = "SELECT * FROM $sitetable ";

    //Cycle through the words-array if there are word(s) filled in
    if (isset($search))
    {
    $selectquery .= " WHERE ";
    for($i = 0; $i < count($search); $i++)
    {
    $selectquery .= "column_name1 LIKE '%" . $search[$i] . "%'";
    if ($i < count($search)-1)
    {
    $selectquery .= " AND ";
    }
    }
    {
    $selectquery .= " ORDER BY column_name2";
    }

    $result = mysql_db_query($dbname,$selectquery,$conn)
    or die ("Query failed");
    while($row=mysql_fetch_row($result))
    {
    echo" Your HTML RESULTS here";
    }
    }
    else echo"Your search request yielded zero results. Please try your search again.";
    }
    else echo"<P><CENTER>You did not provide any words to search for. Please enter words and press the Link Search button.</P></CENTER>**<P>";


    [This message has been edited by svickrey (edited August 28, 2000).]

Similar Threads

  1. Highlight search result from mysql database search
    By Gurt in forum PHP Development
    Replies: 3
    Last Post: February 17th, 2004, 03:39 AM
  2. DreamWeaver MX and MySQL problem!!
    By genog in forum MySQL Help
    Replies: 3
    Last Post: February 12th, 2004, 06:57 AM
  3. Replies: 0
    Last Post: February 11th, 2004, 05:27 AM
  4. MySQL lock tables problem: mysql hangs?
    By Cyclefreak in forum MySQL Help
    Replies: 0
    Last Post: February 6th, 2004, 07:24 AM
  5. Database search highlighting problem - Stuck!
    By nro in forum PHP Development
    Replies: 5
    Last Post: January 30th, 2004, 08:44 PM

IMN logo majestic logo threadwatch logo seochat tools logo