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 7 of 7
  1. #1
    New Coder
    Join Date
    Sep 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does DB have to be full text for this kind of search query?

    hi, im making a search script, the code i'm using is:

    PHP Code:
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link rel="stylesheet" type="text/css" media="screen" title="Stylesheet" href="styles.css">
    </head>

    <body>
    <!--START OF DIV CODE-->

    <div id="container">

         <div id="header">banner</div>

         <div id="menu"><?php include('menu.html'); ?></div>

         <div id="content-container">

              <div id="content">

              <!-- ALL CONTENT -->

    <?



    echo"

    <table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\" width=\"100%\" id=\"AutoNumber1\">

      <tr>

        <td class=\"headers\" width=\"16%\" align=\"center\">File #</td>

        <td class=\"headers\" width=\"16%\" align=\"center\">Type Of Business</td>

        <td class=\"headers\" width=\"17%\" align=\"center\">Location</td>

        <td class=\"headers\" width=\"17%\" align=\"center\">Gross Revenue</td>

        <td class=\"headers\" width=\"17%\" align=\"center\">Business Price

        </font></td>

        <td width=\"17%\" align=\"center\">Terms</td>

      </tr>"
    ;


    //CONNECT TO MYSQL AND DATABASE
    $conn=@mysql_connect("localhost""USERNAME""PASSWORD") or die ("Err:Conn");
    mysql_select_db("majdkgf_capital1") or die("Err:Db");

    //CONVERT INFORMATION INTO VARIABLES
    $category=$_POST['category'];
    $keyword=$_POST['keyword'];

    //FORM THE QUERY
    $sql="select * from businesses_for_sale where '$category' LIKE '%$keyword%'";
    $result mysql_query($sql,$conn);

    //TEST IF THERE IS AN ERROR WITH QUERY
    if(!$result) die("query ".$query." failed with error ".mysql_error());


    $number=mysql_num_rows(mysql_query("$sql"));
    echo 
    "Search has returned $number results ";
    echo 
    "In the category: $category";

    while (
    $row mysql_fetch_array($result))
    {
    $filenu=$row['filenu'];
    $Business=$row['Business'];
    $Location=$row['Location'];
    $Gross_Sales=$row['Gross_Sales'];
    $Business_Price=$row['Business_Price'];
    $Terms=$row['Terms'];
    echo (
    "
      <tr>
        <td class=\"result\" width=\"16%\" align=\"center\"><a href=\"http://www.majd-gfx.com/capital-one/show_full_business.php?full_business_file_number=$filenu\">$filenu</a></td>
        <td class=\"result\" width=\"16%\" align=\"center\">$Business</td>
        <td class=\"result\" width=\"17%\" align=\"center\">$Location</td>
        <td class=\"result\" width=\"17%\" align=\"center\">$Gross_Sales</td>
        <td class=\"result\" width=\"17%\" align=\"center\">$Business_Price</td>
        <td class=\"result\" width=\"17%\" align=\"center\">$Terms</td>
      </tr>
    "
    );
     } 
     
     echo
    "</table> "?>

               <!-- END OF CONTENT -->
              </div>
         </div>
    </div>
    <!--END OF DIV CODE-->
    </body>
    </html>
    now my question is, on my db, do all the fields have to be full text?

    if not, this query is not showing any results even though im searching for exact match.....

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you would you want to change this:
    Code:
    $sql="select * from businesses_for_sale where '$category' LIKE '%$keyword%'";
    to this:
    Code:
    $sql="select * from businesses_for_sale where `$category` LIKE '%$keyword%'";
    //note: backticks `
    I would recommend using mysql_real_escape_string on the posted($_POST) variables.. and since you're doing a LIKE search you will also need to escape out of the _ and %.
    This is how you can escape out of the LIKE search string, I posted it on php.net but it never went on there.
    Code:
    preg_replace('/(%|_)/sim','\\\\$1',$str);

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    good advice. (i also made a few posts to php.net but non of them were added)
    personally, i'd use
    PHP Code:
    $sql="select var1, var2, var3 from businesses_for_sale where `" $category "` LIKE '%" $keyword "%'"
    where var1 etc are the actual columnnames that you need
    + instead of a replace on the wildcards, i'd simply do a regex and return an error if the input contained wildcards. If your tables get bigger, then these searches can realy slow your db down.

    about the fulltext: fulltextsearches and keywordsearches are something completely different. You have a keywordsearch so you don't need fulltext-indexes on none of these columns.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #4
    New Coder
    Join Date
    Sep 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    im REALLY sorry but im afraid i don't understand what you guys are talkin about, maybe a reference or smthn that would explain it a bit simpler? thank you

    EDIT: when i replace the ' with `, i get a mysql error, but when it's ' i just get 0 results....
    Last edited by Majd-GFX; 10-26-2004 at 03:42 AM.

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Majd-GFX
    im REALLY sorry but im afraid i don't understand what you guys are talkin about, maybe a reference or smthn that would explain it a bit simpler? thank you
    What exactly don't you understand?

    Quote Originally Posted by Majd-GFX
    EDIT: when i replace the ' with `, i get a mysql error, but when it's ' i just get 0 results....
    I simply don't believe that.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #6
    New Coder
    Join Date
    Sep 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok this is what i get when i put ` instead of ' in the query

    query failed with error Unknown column '%Auto Body Shop%' in 'where clause'

    now why is it taking $keyword as $category?

    NOTE: when i switch the two variables so that $keyword is before $category, i get same error statement.

    However, when they are ' and not `, i get (and i echoed it this way)

    Search has returned 0 results In the category: Business with the keyword: Auto Body Shop
    select * from businesses_for_sale where 'Business' LIKE '%Auto Body Shop%'

    NOTE: second part is the query used.


    thank you

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    post the exact code that you use. the query i posted will most certainly work. surrounding a column or tablename with backtick will never cause an error.

    I quickly created a table businesses_for_sale with column 'Business' and added a few records. One with value 'Auto Body Shop'

    Guess what:
    select * from businesses_for_sale where `Business` LIKE '%Auto Body Shop%'
    ==> returns 1 record
    select * from businesses_for_sale where Business LIKE '%Auto Body Shop%'
    ==> returns 1 record
    select * from businesses_for_sale where 'Business' LIKE '%Auto Body Shop%'
    ==> returns 0 records.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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