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
Like Tree2Likes
  • 1 Post By Dubz
  • 1 Post By felgall

Thread: Help with sql statement in php code

  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    30
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Help with sql statement in php code

    Hi all so I added a prepare object to my code and it works if I say "Select * from products where proName = ?" but if I try to do any form of a like statement it won't work.
    The problem is on line 8
    Code:
    <?
    $name = $_GET['q'];
    $dbh=new PDO('mysql:host=localhost;dbname=db1','root','student');
    if(!$dbh){
    	die('Could not connect: ' . mysql_error());
    }
    $a = mysql_real_escape_string($name);
    $sql = "select * from products where proName like ('%".'?'."%')";
    $sth = $dbh->prepare($sql);
    $sth->execute(array($a));
    $b = $sth->fetchall();
    	
    if ($name != null){
    	foreach ($b as $row){
    		echo $row['proName'].' $'.$row['price'].'<br>';
    	}
    }
    ?>
    Thanks in advance!

  • #2
    Regular Coder
    Join Date
    Sep 2011
    Posts
    410
    Thanks
    18
    Thanked 26 Times in 26 Posts
    I assume you're trying to get everything from the products table where the column `proName` has a question mark somewhere in it? If so, this will work for you:
    PHP Code:
    $sql "SELECT * FROM `products` WHERE `proName` LIKE '%?%'"
    The main part is the modification after LIKE, the backticks and caps aren't required, only recommended. If that's not what you're asking for then you'll need to be a little more specific.

    Backticks declare the exact string that you are using rather than it assuming it, queries with columns such as 'from' or 'to' won't work without them because those are reserved words.
    The caps is just easier to read.
    CodyJava likes this.

  • Users who have thanked Dubz for this post:

    CodyJava (05-03-2014)

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,628
    Thanks
    0
    Thanked 648 Times in 638 Posts
    Quote Originally Posted by CodyJava View Post
    if I try to do any form of a like statement it won't work
    When using prepare/bind with LIKE the % gets added to the data in the bind statement - it does not go in the prepare statement.
    CodyJava likes this.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    CodyJava (05-03-2014)

  • #4
    New Coder
    Join Date
    Sep 2012
    Posts
    30
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dubz View Post
    I assume you're trying to get everything from the products table where the column `proName` has a question mark somewhere in it? If so, this will work for you:
    PHP Code:
    $sql "SELECT * FROM `products` WHERE `proName` LIKE '%?%'"
    The main part is the modification after LIKE, the backticks and caps aren't required, only recommended. If that's not what you're asking for then you'll need to be a little more specific.

    Backticks declare the exact string that you are using rather than it assuming it, queries with columns such as 'from' or 'to' won't work without them because those are reserved words.
    The caps is just easier to read.
    Hi dubz I'm not trying to find where it has a question mark. In the PHP manual they said you can use a question mark when using the prepare object. So line 10 is where it sets the question mark equal to $a

    Thanks hope this makes sense.

  • #5
    New Coder
    Join Date
    Sep 2012
    Posts
    30
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Ah okay thanks so much I should have tried that. Thanks again!

  • #6
    Regular Coder
    Join Date
    Sep 2011
    Posts
    410
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Quote Originally Posted by CodyJava View Post
    In the PHP manual they said you can use a question mark when using the prepare object. So line 10 is where it sets the question mark equal to $a
    So why not try using $a instead and see if it works?

    PHP Code:
    $sql 'SELECT * FROM `products` WHERE `proName` LIKE \'%'.$a.'%\''

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,628
    Thanks
    0
    Thanked 648 Times in 638 Posts
    Try:

    Code:
    $a = '%'.$name.'%';
    $sql = "select * from products where proName like ?";
    $sth = $dbh->prepare($sql);
    $sth->execute(array($a));
    or

    Code:
    $a = '%'.$name.'%';
    $sql = "select * from products where proName like :a";
    $sth = $dbh->prepare($sql);
    $sth->execute(array(':a' => $a));
    Note that you don't need mysqli_real_escape_string when you use prepare statements as you only need to escape data that is jumbled with SQL and prepare keeps them separate.

    You should never include PHP variable names inside the prepare statement as that defeats the purpose of using prepare - the only situation where you would ever need to is to specify the table name, everything else can either use ? or named parameters starting with a :
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

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