php mysqli bind_params not working

Question

I'm trying to get a parameterized query working with the following code:

 $stmt = $mysqli->prepare("SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes
INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title  LIKE '%?%'");

$stmt->bind_param('s', $search);
$search = $_GET['search'];

$stmt->execute();
$result = $stmt->get_result();

However after executing the query I've checked my general_log table in my mysql database and the query just didn't change:

SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes
INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title  LIKE '%?%'

EDIT:

Finally got it working with the following code:

 $param = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes
INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title  LIKE ?");
$stmt->bind_param('s', $param);
$stmt->execute();
$result = $stmt->get_result();

Thanks everyone for the help!

As far as I know, the placeholder shouldn't be wrapped with quotes (`'`) — Ofir Baruch, Oct 08 '15 at 12:12
You're also binding `$search` to the statement before initialising it... and you don't really need to initialise/sanitise a new variable when using a bound parameter `$stmt->bind_param('s', $_GET['search'])` should probably do it (as long as you never pass `search[]` as a GET var) unless you need something like `$search = "%{$_GET['search']}%";` — CD001, Oct 08 '15 at 12:22
$stmt->bind_param('s', $_GET['search']) did not work. With brackets around it, it did work tho. — Justus Bosschieter, Oct 08 '15 at 12:31

score 2 · Accepted Answer · edited May 23 '17 at 12:22

2

Since you put the placeholder wrapped with ', it's been threaten as a regular string and not as a placeholder.

The right way would be to wrap the variable you're binding with %%:

$stmt = $mysqli->prepare("SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes
INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title  LIKE ?");

$stmt->bind_param('s', $search);
$search = '%'.$_GET['search'].'%';

$stmt->execute();
$result = $stmt->get_result();

Similar questions:

edited May 23 '17 at 12:22

Community

1
1

answered Oct 08 '15 at 12:15

Ofir Baruch

10,323
2
26
39

Still not working incoming prepare statement: SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title LIKE ? – Justus Bosschieter Oct 08 '15 at 12:23
Try to first declare the search variable and its value and afterwards to bind it. – Ofir Baruch Oct 08 '15 at 12:25
Nevermind it where the brackets around the variable. Look at my edit above! Thank you so much for the help! – Justus Bosschieter Oct 08 '15 at 12:27
Edited accordingly. You're welcome. If my answered helped you, please consider accepting it. – Ofir Baruch Oct 08 '15 at 12:30

score -1 · Answer 2 · answered Oct 08 '15 at 12:14

change blow code.

 $stmt->bind_param(':s', $search);

OR

SELECT formattributes.id as attr_id, forms.title as form_title, formattributes.label as attr_label, formattributes.description as attr_description FROM formattributes
INNER JOIN forms ON forms.id = formattributes.form_id WHERE forms.title  LIKE '%:s%'

 $stmt->bind_param(':s', $search);

php mysqli bind_params not working

2 Answers2