how to use placeholders in mysql queries from php

Question

I am trying to make a prepared query like this one:

$stmt=$mysqli->prepare("SELECT zonasrepartoid, calle, municipio, codigopostal FROM zonasreparto WHERE municipio like concat('%',:m,'%')")
                $stmt->bind_param(':m',$incity);
                $stmt->execute();

I have tried to use the % many times in different ways, but i have no more ideas of how to use it, just to make it work. If any one could tell me what is wrong i would appreciate that.

Thank you in advance!

This has been questioned in the past (and recently also) where someone votes as a duplicate then places an answer. @DanFromGermany — Funk Forty Niner, May 26 '14 at 15:52
@Fred-ii- I will avoid this in the future, thanks for pointing out — Daniel W., May 26 '14 at 16:15
@DanFromGermany You're welcome Dan. I just don't want you to get hit by any scrutiny or bashing of sorts. Am just saying that it's been said lately. I for one will probably end up closing a question "after posting an answer" if the OP is taxing the heck outta me, because it's "unclear" as to what the OP wants, and are often not doing things correctly, even with proper code given. Cheers — Funk Forty Niner, May 26 '14 at 16:19
Seems like the question's been reopened. @DanFromGermany strangely that. I noticed the other guy who voted to close, also placed an answer; ironic isn't it. — Funk Forty Niner, May 26 '14 at 16:24
my apologise, i usually get what i look for but my problem is that when I type the caracter '%' makes the searching complicated. — myhappycoding, May 26 '14 at 17:31

Daniel W. · Answer 1 · 2014-05-26T16:31:39.300

1

Surround your input by % instead of putting it into the query:

... WHERE municipio like ?")

$stmt->bind_param('s', '%' . $incity . '%');

The syntax of putting in named placeholders is supported by the PDO library, not MySQLi. With MySQLi, the first param indicates the type of variable (e.g. i = integer, s = string, etc).

edited May 26 '14 at 16:31

answered May 26 '14 at 15:50

Daniel W.

31,164
13
93
151

I noticed this was upvoted earlier, but can assure you the downvote did `not` come from me, should it be the case. ;-) – Funk Forty Niner May 26 '14 at 16:20
this answer is still essentially useless. – Your Common Sense May 26 '14 at 16:33
3

@YourCommonSense not as useless as your comment. – Daniel W. May 26 '14 at 16:40

score 1 · Answer 2 · answered May 26 '14 at 16:23

It makes me wonder where did you get that idea of using ":letter" placeholders with raw mysqli.

Nowhere in the manual can be seen such a syntax. The rest of your approach is okay.

$sql = "SELECT zonasrepartoid, calle, municipio, codigopostal FROM zonasreparto 
        WHERE municipio like concat('%',?,'%')"
$stmt=$mysqli->prepare($sql);
$stmt->bind_param('s', $incity);
$stmt->execute();

You really, really, really need to exercise with manual examples instead of blindly shooting with random syntax. That's your main and the only problem.

how to use placeholders in mysql queries from php

2 Answers2