Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    77

    SQL Search Command help

    Hello everyone,
    I am trying to do a SQL search on my database using the following query:

    PHP Code:
    $sql_query "SELECT parts.OEM, parts.OCC, parts.partType, parts.partSize, parts.numPoles, parts.photo, parts.mID from parts WHERE REPLACE(REPLACE(OEM,'-',''),' ','') LIKE CONCAT(\"%\",REPLACE(REPLACE(\"$term\",'-',''),' ',''),\"%\") OR REPLACE(REPLACE(OCC,'-',''),' ','') LIKE CONCAT(\"%\",REPLACE(REPLACE(\"$term\",'-',''),' ',''),\"%\") OR REPLACE(REPLACE(partType,'-',''),' ','') LIKE CONCAT(\"%\",REPLACE(REPLACE(\"$term\",'-',''),' ',''),\"%\") OR mID LIKE \"%$term%\""
    The variable $term contains the user input, and I want to search all database fields, regardless of spacing or hyphens. Can someone please double check this to make sure that the syntax is correct? I know that this post is vague now, but if the problem is obvious, that would be simple. The error that this command is returning upon execution is:

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/***/public_html/***/***/wp-content/themes/***/searchpage.php on line 36

    If someone could get this to work, I'm willing to send $10 PayPal. I've wasted so much time trying to figure it out. If you need more details, I would love to share.

    Thanks.

  2. #2
    Join Date
    Mar 2011
    Location
    México
    Posts
    66
    PHP gives you a vague error, to see a more detailed error try running your command in your phpmyadmin or similar (with the variable $term changed to a real example).

    Or you can add mysql_error to see the error:
    Code:
    mysql_query("YOURQUERY") or die("MySQL ERROR: ".mysql_error());
    Then please post us your error.
    Web designer, programmer & webmaster for 7+ years
    Hosting my 9 sites in Web hosting México
    CentOS|cPanel|R1Soft|Softaculous|Cloud Hosting|Green Hosting

  3. #3
    Join Date
    May 2011
    Location
    Columbus, Ohio
    Posts
    270
    Try the following:

    PHP Code:
    $strSqlTerm mysql_real_escape_string(preg_replace('/[- ;]/','',$term));

    $SQL  'SELECT p.OEM, p.OCC, p.partType, p.partSize, p.numPoles, p.photo, p.mID ';
    $SQL .= 'FROM parts AS p ';
    $SQL .= 'WHERE REPLACE(REPLACE(p.OEM,"-","")," ","") LIKE "%'.$strSqlTerm.'%" ';
    $SQL .= '   OR REPLACE(REPLACE(p.OCC,"-","")," ","") LIKE "%'.$strSqlTerm.'%" ';
    $SQL .= '   OR REPLACE(REPLACE(p.partType,"-","")," ","") LIKE "%'.$strSqlTerm.'%" ';
    $SQL .= '   OR p.mID LIKE "%'.$strSqlTerm.'%" ';

    $rsResults mysql_query($SQL); 
    To be honest, if you are going to be doing a lot of search across all of these fields, depending on how much data, I would consider creating another table for searching that contains these fields already stripped down, and the fields indexed.

    -Greg

  4. #4
    Join Date
    Jun 2005
    Posts
    77
    Thanks, this works out well, but I also want to search the p.mID field as well. This column contains the manufacturer names. Some of them have dashes. If someone types in the exact manufacturer type (some names have dashes), I would like it to match. How do I add to this SQL command?

  5. #5
    Join Date
    May 2011
    Location
    Columbus, Ohio
    Posts
    270
    Are you meaning that you want p.mID to match ONLY if they typed it in exactly the same (including the dashes)?

    If that is the case, on the last line of the SQL build, replace it with this:

    PHP Code:
    $SQL .= '   OR p.mID LIKE "%'.mysql_real_escape_string($term).'%" '
    (note this still will match part of the string, but the part that matches has to match exactly)

    If you were instead wanting it to match as the other fields do, where they will strip out spaces/slashes on both the term and the data in the field, then use:

    PHP Code:
    $SQL .= '   OR REPLACE(REPLACE(p.mID,"-","")," ","") LIKE "%'.$strSqlTerm.'%" '

  6. #6
    Join Date
    Jun 2005
    Posts
    77
    TwineDev, you've been wonderful so far with answering my questions. I have one more thing though: there are some parts with periods in them (in addition to dashes). How can I make the term match with periods just like the dashes? In other words, a part in the database is 1.2.3.4-5. The customer searches for 1234-5 or 12345. How can we make these search terms match the part number?

  7. #7
    If I were you I'd look into mysql's "Full-Text" indexing/searching

Similar Threads

  1. help me with an sql command
    By imryan in forum Programming Discussion
    Replies: 6
    Last Post: 05-14-2011, 04:08 PM
  2. Need SQL command for this
    By Jeff - Exceed in forum Hosting Security and Technology
    Replies: 3
    Last Post: 04-20-2006, 08:07 AM
  3. Search command
    By LP560 in forum Hosting Security and Technology
    Replies: 5
    Last Post: 07-18-2005, 12:02 PM
  4. SQL command Help! List DB
    By DigiCrime in forum Hosting Security and Technology
    Replies: 3
    Last Post: 11-07-2002, 03:50 PM
  5. Ensim SQL command
    By Acronym BOY in forum Hosting Security and Technology
    Replies: 5
    Last Post: 08-24-2002, 11:58 AM

Posting Permissions

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