SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql SELECT... LIKE... $variable syntax problem?

    Hello! I have a problem trying to search on one of my db tables which uses ...
    phpMyAdmin (Version information: 3.3.9) and Apache/2.2.17 (Win32) mod_ssl/2.2.17 OpenSSL/0.9.8o PHP/5.3.4 mod_perl/2.0.4 Perl/v5.10.1
    MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
    PHP extension: mysql

    I am using the following php code to do the search...
    PHP Code:
            OpenDB();
            
            
    $element $_REQUEST["element"];
            
    $criteria mysql_real_escape_string($_REQUEST["criteria"]);
            switch (
    $element)
            {
            case 
    "datetime":
                
    $sql "SELECT * FROM `vehicles` WHERE datetime LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die(mysql_error());
                break;
            case 
    "fullname";                                                
                
    $sql "SELECT * FROM `vehicles` WHERE fullname LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die(mysql_error());
                break;
            case 
    "make":
                
    $sql "SELECT * FROM `vehicles` WHERE make LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die(mysql_error());
                break;
            case 
    "year":
                
    $sql "SELECT * FROM `vehicles` WHERE year LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die(mysql_error());
                break;
            case 
    "suburb":
                
    $sql "SELECT * FROM `vehicles` WHERE suburb LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die(mysql_error());
            } 
    and receive the following error message
    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
    I am truly perplexed! Can anyone see the wood for the trees?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Welcome to the SP forums

    Do a print_r($_REQUEST) and an echo of $sql to see if things are as you think they are.

  3. #3
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    echo

    This I did with the following result
    Code:
    SELECT * FROM `vehicles` WHERE fullname LIKE '%Christeen%'
    which I also entered via phpMyAdmin on my localhost xampp, with a positive result!? But I still get
    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
    when running the script!??

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    That query looks fine to me. Are you sure there is no other point where you are running a query that might give that error? You could change the 'or die' message (include some kind of identification) so that you're sure the error is coming from that particular query.

  5. #5
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As advised I put this code into the sql statement -
    PHP Code:
            case "fullname";                                                
                
    $sql "SELECT * FROM `vehicles` WHERE `fullname` LIKE '%".$criteria."%'";
                
    $row mysql_query($sql) or die("A MySQL error has occurred.<br />My Query: " $sql "<br /> Error: (" mysql_errno() . ") " mysql_error());
                break; 
    Curiously, exactly the same response...
    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
    No sign of my altered message???

  6. #6
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    further...

    So I took out the open db code to force another error and got this response...
    SELECT * FROM `vehicles` WHERE `fullname` LIKE '%Christeen%'
    A MySQL error has occurred.
    My Query: SELECT * FROM `vehicles` WHERE `fullname` LIKE '%Christeen%'
    Error: (1046) No database selected

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by JiminSA View Post
    Curiously, exactly the same response...

    No sign of my altered message???
    Like I thought, the error is caused somewhere else in your script. A query is executed AFTER the one you thought caused the error.

  8. #8
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are absolutely correct Sir! I put an echo at the end of the switch/case script and it displayed!
    Thanks so much for your prompt and helpful advice. I shall report back on my eventual findings at this thread, for the sake of continuity.

  9. #9
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Johannesburg South Africa
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conclusion...

    Yes, once again thank you for your help

    I found the faux-pas that was causing the error...

    After the search comes a display (in a while loop) of the main page - I was effectively destroying the sql select data used by the main page query...


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
  •