Parameterized Queries PHP/MySQL [duplicate]

Question

Possible Duplicate:
How do I create a PDO parameterized query with a LIKE statement in PHP?
PHP PDO prepared statement — mysql LIKE query

I'm trying to make a search engine for my website, and right now I'm just trying to make sure the connection is all and well. Here is my code thus far:

EDITED CODE (Still doesn't work, but here's where I'm at with the suggestions thus far):

$db = new PDO("mysql:host=".DB_SERVER.";dbname=".DB_NAME, DB_USER, DB_PASS);
$stmt = $db->prepare("SELECT * FROM table_1 WHERE name LIKE ? ORDER BY bid DESC");
$stmt->bindParam(1, "%{$_GET['s']}%", PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll();

I tried to see if the different methods of execute would do anything, but regardless of which way above I write it, I get the same result, nothing. I want the % wildcard in there so it does it'll search anywhere in name. On that note, am I using it correctly? The thing that confuses me most is when I type in the exact same query into PHPMyAdmin, the query runs through fine, so my guess is that I'm screwing up the PDO somewhere.

EDIT: PHPMyAdmin Query:

SELECT * FROM table_1 WHERE name LIKE '%Test%' ORDER BY bid DESC LIMIT 0 , 30

This returns 1 result, as it is expected to. What is different about my code and this query? :/

@Matt Regarding your edit: my edited answer should explain it. — middus
– middus, Commented Oct 23, 2011 at 22:35
@Matt No, now you added quotes around the ?. Don't. I just added the correct query to my answer for increased clarity. — middus
– middus, Commented Oct 23, 2011 at 22:37
Ya i realized I had that in the post, the code didn't; still doesn't work. — Matt
– Matt, Commented Oct 23, 2011 at 22:39
Note that you shouldn't quote parameters in prepared statements. If you do, it's a string, not a parameter (the ':search' in the commented portion of the sample code won't work, because it will be parsed as a string with the value ":search", not a parameter named "search"). Also, only change the sample code when it doesn't properly illustrate your question. "I don't know why my code isn't working." isn't a question. "Why isn't my code working?" is too vague to be useful. — outis
– outis, Commented Oct 23, 2011 at 22:42
@Matt Well that's weird, because that's exactly the example used in the doc php.net/manual/en/pdo.prepared-statements.php#example-943 — middus
– middus, Commented Oct 23, 2011 at 22:44

middus · Accepted Answer · 2011-10-23 22:39:36Z

5

I don't really understand what your question is, but I'm guessing you don't know how to add the %? If so, try this:

$stmt = $db->prepare("SELECT * FROM table_1 WHERE name LIKE ? ORDER BY bid DESC");
$stmt->bindValue(1, "%{$_GET['s']}%", PDO::PARAM_STR);
$stmnt->execute();
// fetch and win! :-)

A little explanation: PDO will quote and escape the parameter ? appropriately. This means, that if you are binding hello, PDO will substitute ? with 'hello' (note the quotes). Therefore, in order to have the % inside the quotes, you will have to add them to what is binded, in this case $_GET['s'].

edited Oct 23, 2011 at 22:39

answered Oct 23, 2011 at 22:27

middus

9,1291 gold badge34 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

Turns out it was working; the case sensitivity of the variables in my display table were the reason it wasn't working, not the query connection...

Collectives™ on Stack Overflow

Parameterized Queries PHP/MySQL [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related