-1

I don't understand why this doesn't work. I have the following contents in sowner : " 6 4 7 " without the quotes, but with all the spaces including in the beginning and the end. That's the sowner value of a row in the DB.

I have row id which I transform into a string of this form (id is 4) " 4 " then search for it inside sowner to see if it's there. No results returned.

Here is my code:

$sql = 'SELECT * FROM `services` WHERE `sowner` LIKE ?';
$stmt = $conn->prepare($sql);
if($stmt === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
}
$spacedid = " " . strval($row['id']) . " ";
$stmt->bind_param('s', $spacedid);
$stmt->execute();
$res3 = $stmt->get_result();

The purpose is to have an undefined number of people's ids in a string separated by spaces and only showing stuff to people if their id is inside the services sowner field.

I am positive it's the LIKE because if I remove the WHERE, all rows show up.

Also I'm using the same implementation in a on-type search suggestion form like this

SELECT * FROM `users` WHERE username LIKE ?
Cârnăciov
  • 1,169
  • 1
  • 12
  • 24
  • 2
    You should fix your data structure so you are not storing lists of things in a string. You should have a separate table, say `ServicesOwners`, with one row per service and owner. – Gordon Linoff Apr 23 '15 at 15:25
  • 1
    Gordon, having an association table like you recommend would make it *possible* for the database to enforce referential integrity, make it *simpler* to add and remove data, and make it *easier* to query. Why would we want to do that? I mean, that kind of takes the fun out of it, debugging the production problem when the list of values becomes longer than the maximum we've allowed for a character string. Doesn't it? – spencer7593 Apr 23 '15 at 15:31
  • `$sql = "SELECT * FROM services WHERE sowner LIKE CONCAT('%', ?)"`. If you need wildcard at the end, swap the arguments for `CONCAT`. – N.B. Apr 23 '15 at 15:31
  • Bill Karwin should include a chapter in his book about this. Oh, wait, he did. Chapter 2. [**SQL Antipatterns: Avoiding the Pitfalls of Database Programming**](http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?ie=UTF8&qid=1429803464&sr=8-1&keywords=bill+karwin) – spencer7593 Apr 23 '15 at 15:39
  • Possible duplicate of [Correct way to use LIKE '%{$var}%' with prepared statements? \[mysqli\]](http://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli) – Cârnăciov May 06 '17 at 15:52

1 Answers1

3

you need to add the wildcard operators to the like

so the statement becomes more like 

SELECT * FROM `users` WHERE username LIKE '% 6 %'

change the line 

$spacedid = " " . strval($row['id']) . " ";

to 

$spacedid = "% " . strval($row['id']) . " %";
Ian Kenney
  • 6,376
  • 1
  • 25
  • 44