Incorporate SetString in Prepared Statements SQL

Question

I'm trying to alter an SQL query to the LIKE-type which involves adding the '%' at the end. After some digging around I learned that I need to make use of SetString somehow. Like this (this seems to be JAVA):

pstmt.setString(1, notes + "%");

I'm not really sure how to incorporate this in my code though:

$query = 'SELECT DISTINCT ... LEFT JOIN ... ON ... WHERE bedrijfsnaam = ?';

$stmt->prepare($query);

$stmt->bind_param('sii', $bedrijfvariabele2, $startpoint, $limit);

$stmt->execute();

Not sure how to alter the setString to incoroporate with my PHP?

So far we've got this:

$query = 'SELECT DISTINCT ... LEFT JOIN ... ON ... WHERE bedrijfsnaam LIKE ?';

$stmt->prepare($query);

$stmt->bind_param('sii', "$bedrijfvariabele2%", $startpoint, $limit);

$stmt->execute();

This however, produces a blank page starting from the point of this code.

The final solution in my case was adding the % icon JUST before binding the paramaters.

$bedrijfvariabele2 .= "%";

$stmt->bind_param('sii', $bedrijfvariabele2, $startpoint, $limit);

Thanks a lot!

@crush, thanks, the other topic did indeed refer to Java. Any idea how to incorporate the setString in PHP? — user2704687, Jan 07 '14 at 20:32
@DigitalChris No, the first code line is pstmt.setString which is a JAVA call. Just change your query to WHERE bedrijfsnaam LIKE ? and in the bind_param do "%$bedrijfvariabele2%" — Rottingham, Jan 07 '14 at 20:34
The `.` operator is used for string concatenation in `PHP`. What Rottingham said should work. — crush, Jan 07 '14 at 20:36
@DigitalChris You make no sense. The OP used `.` as an object operator in his code above. He's coding in `PHP`. Therefore, `.` should *NOT* be used in the way in which he is using it. — crush, Jan 07 '14 at 20:42
@Rottingham, thanks, changing the query to LIKE and changing $stmt->bind_param('sii', $bedrijfvariabele2, $startpoint, $limit); to $stmt->bind_param('sii', "$bedrijfvariabele2%", $startpoint, $limit); gives no result and changing it to $stmt->bind_param('sii', $bedrijfvariabele2%, $startpoint, $limit); gives an error about an unexpected comma. Any thoughts? — user2704687, Jan 07 '14 at 20:43
On the very first line of code... `pstmt.setString(1, notes + "%");` — crush, Jan 07 '14 at 20:43
@crush that was an example of java code... he explains that. Also since the `.` IS the php concatenation operator, I thought that might be what you were referring to. — Digital Chris, Jan 07 '14 at 20:44
@DigitalChris He had repeated it at the bottom of his post too, where he said he had tried it in `PHP`. He recently edited that out. — crush, Jan 07 '14 at 20:44
@OP Ofcourse you will get a 'no comma' error, you have to wrap the value in "" so that PHP will auto concatenate. If you get no results, then it failed to match. If you have the % after the value, then it will only find matches that start with your literal value. If you put the % in front and after your value, then it is a 'contains' type check. You have have a $startpoint(offset) and $limit to make sure are not hindering your results — Rottingham, Jan 07 '14 at 20:46
@Rottingham, I failed to explain correctly: I do not only get no results, the rest of the page isn't loading anymore. It gets stuck. — user2704687, Jan 07 '14 at 20:48
You likely have syntax errors. Another joy of not error checking. Update your question with new code so it can be inspected — Rottingham, Jan 07 '14 at 20:49
@Rottingham, the same happens if I just wrap the variable inside double quotes. (Without editing the query to LIKE or adding the percentage icon). — user2704687, Jan 07 '14 at 20:49

score 1 · Accepted Answer · answered Jan 07 '14 at 20:42

1

You want to append the "%" to the string before binding:

$bedrijfvariabele2 .= "%";  //this will append % after $bedrijfvariabele2's value

$query = 'SELECT DISTINCT ... LEFT JOIN ... ON ... WHERE bedrijfsnaam = ?';

$stmt->prepare($query);

$stmt->bind_param('sii', $bedrijfvariabele2, $startpoint, $limit);

$stmt->execute();

answered Jan 07 '14 at 20:42

Digital Chris

6,177
1
20
29

Chris? If the query is WHERE fieldName = ? and you add % after the literal value, how will that work? (PS not being sarcastic, wondering if I'm missing something) – Rottingham Jan 07 '14 at 20:43
@Rottingham You set the var $bedrijfvariabele2 in line 1. It doesn't get used until bind_param() in line 4. See [this answer](http://stackoverflow.com/questions/1352002/using-wildcards-in-prepared-statement-mysqli) for bind_param() wildcard usage. – Digital Chris Jan 07 '14 at 20:51
This actually is the simplest solution for my problem, thanks! – user2704687 Jan 07 '14 at 20:55
@DigitalChris +1 didn't realize bind_param used the wildcard like that! – Rottingham Jan 07 '14 at 20:58

Incorporate SetString in Prepared Statements SQL

1 Answers1