Results 1 to 9 of 9
  1. #1
    Member
    Join Date
    January 18th, 2005
    Posts
    58
    Hey all,

    Can anyone tell me what is wrong with this code here:
    <pre class="ip-ubbcode-code-pre"> $result = mysql_query("SELECT prodName, prodDesc, prodSku, prodBuy, prodImage, prodPrice, prodRetail, prodSale, prodPixel FROM jcwhitney_items_optimized WHERE prodKeywords LIKE '%$query' LIMIT $items, $itemsToShow") or die(mysql_error()); </pre>

    I recieve this error:
    "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 '' at line 1"

    Does it matter that 'prodKeywords' is [BLOB - 54 Bytes] field?

    Basically all i want to do is a basic search through that table based on the prodKeywords field.

    thanks in advance

    If you feel like you're in control you're not going fast enough - Mario Andretti

  2. #2
    Member Speedy's Avatar
    Join Date
    January 18th, 2005
    Location
    Lancashire UK
    Posts
    165
    Instead of listing all the table fields, use a wildcard entry by using * as below.

    $result = mysql_query("
    SELECT * FROM jcwhitney_items_optimized
    WHERE prodKeywords
    LIKE '%$query'
    LIMIT $items, $itemsToShow")
    or die(mysql_error());

    Depending on how you have written the script, it looks like you have not mentioned what the database name is. So this would work aswell:

    $result = mysql_db_query(DATABASE-NAME-HERE, "
    SELECT * FROM jcwhitney_items_optimized
    WHERE prodKeywords
    LIKE '%$query'
    LIMIT $items, $itemsToShow")
    or die(mysql_error());

    Many variations in PHP and MySQL can produce the same results.


    &lt;&lt; - Web Designers do it with Objects and Style Sheets! - &gt;&gt;

  3. #3
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    ÄúsTrálíĺ
    Posts
    1,372
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by AcuraImport:
    "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 '' at line 1"<HR></BLOCKQUOTE>
    Look at the ''.

    The error is most likely around your '%$query'.
    Try escaping the % ('\%$query'), and check that $query does indeed have a value.

  4. #4
    Full Member
    Join Date
    January 18th, 2005
    Posts
    88
    Instead of '%$query'
    try '%'. $query .

    use dot operator for concatenation.

  5. #5
    Full Member
    Join Date
    January 18th, 2005
    Posts
    88
    Mixing "double quotes" and 'single quotes' in query is always prone to bugs.

  6. #6
    Newbie
    Join Date
    January 18th, 2005
    Posts
    13
    <pre class="ip-ubbcode-code-pre">
    $strSQL = sprintf("SELECT *
    FROM jcwhitney_items_optimized
    WHERE prodKeywords LIKE '%%%s'
    LIMIT %d, %d", $query, $items, $itemsToShow);

    $result = mysql_query($strSQL) or die(mysql_error()); </pre>

    The %% makes a single percent sign while %d is replaced by a digit argument and %s is replaced with a string (In this example %s is replaced by $query, the first %d is replaced by $items and the second replaced by $itemsToShow).

    This function is very powerful and really fixes many quote and resolves many string issues.

    If you don't understand it, please follow the link I've provided below and if you still dont understand afterwards, you can pm me or reply again.

    PHP's sprintf function

  7. #7
    Intergalactic Trader IGshop's Avatar
    Join Date
    January 18th, 2005
    Posts
    87
    It seems to me you need to escape the single quotes in the LIKE statement: \'%$query\'

    What's happening now is that the variable $query is being interpreted literally and not as a variable.

    Jim

  8. #8
    Newbie
    Join Date
    January 18th, 2005
    Posts
    13
    That may not be the case since he is enclosing the query in double quotes. If someone were to place a single quote inside of $query (i.e. LIKE 'Women's Shoes'), you can see what type of problem that would cause so make sure to also use the add_slashes command on your $query variable.

    $query = addslashes($query);

  9. #9
    ABW Ambassador swampy_webber's Avatar
    Join Date
    January 18th, 2005
    Location
    West Virginia
    Posts
    698
    I think these guys are right on the money. What always helps me is to print the actual query and see if I see the problem.

    I just do a print "$result"; and that way I see it exactly as mysql will...

    Don't know if this helps or not but it usually let's me catch typos quickly.


    Just a suggestion
    Life is like a dogsled team. If you ain't the lead dog, the scenery never changes.

    -- Lewis Grizzard

  10. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. looking for php/mysql work
    By spiderninja in forum Midnight Cafe'
    Replies: 0
    Last Post: January 31st, 2007, 11:12 PM
  2. PHP & MySQL Search Question
    By fish4017 in forum Midnight Cafe'
    Replies: 21
    Last Post: July 24th, 2005, 11:42 AM
  3. Php Mysql - Repeating..??
    By AddHandler in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: May 7th, 2005, 03:25 PM
  4. Grouping with PHP and MySQL
    By Doug247 in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: March 24th, 2005, 10:07 AM
  5. PHP & mySQL but....
    By perfectG in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: November 6th, 2002, 06: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
  •