Select from SQL database all rows that CONTAIN a certain text string in specific column

Question

I've been looking at other posts such as this and this but nothing works for my specific case.

I have a table like this:

|    Name    |    Reference    |    Etc...    |
|------------|-----------------|--------------|
|  John Doe  |                 |  Blah blah   |
|  Jane Doe  |   John Doe      |  Blah blah   |
| Mike Small |   Jane Doe      |  Blah blah   |
|  Steve Ex  |   John Doe      |  Blah blah   |
| Mary White |   Mike Small    |  Blah blah   |

I want to be able to find which Names are also References for another name and turn them into links so a user can click on them and get a list of the Names to which this is Reference.

For instance, if a user clicks on John Doe, he/she will get a table like this:

|    Name    |    Reference    |    Etc...    |
|------------|-----------------|--------------|
|  Jane Doe  |   John Doe      |  Blah blah   |
|  Steve Ex  |   John Doe      |  Blah blah   |

Currently I'm stuck here:

function find_items_by_ref($ref) {
  db_connect();
  $query = sprintf("SELECT * FROM items WHERE reference LIKE '%s'", mysql_real_escape_string($ref));
  $result = mysql_query($query);
  $row = mysql_fetch_array($result);
  return $row;
}

I've tried LIKE and CONTAINS and like '%' || TEXT || '%' etc., etc., but nothing works. Any ideas, please?

I'm trying out both possibilities in the answer given below. With the first one I'm having some trouble. When I want to echo the elements of the array obtained from the SQL Select I'm getting a Warning: Illegal string offset 'X' ... error. If I run var_dump on one of those retrieved arrays, this is what I get. I'm not quite figuring out what's wrong. Seems like a working array to me:

array (size=16)
0 => string '37' (length=2)
'id' => string '37' (length=2)
1 => string 'Steve Ex' (length=8)
'name' => string 'Steve Ex' (length=8)
2 => string 'John Doe' (length=8)
'reference' => string 'John Doe' (length=8)
3 => string 'Blah blah' (length=9)
'etc' => string 'Blah blah' (length=9)

OK. Solved it. And the issue was so stupid... guess who feels like a real ass. Anyway, the thing was that I was trying to pass a number if SQL rows as one row. I had copied, by mistake, my Select function that I was using for when user wanted to get info on a single row through its ID. The problem was right there all the time and I was missing it like an elephant hiding behind a tree. Anyway, there it is... sorry for that. The working code is:

function find_items_by_ref($ref) {
  db_connect();
  $query = sprintf("SELECT * FROM items WHERE INSTR(reference,'".mysql_real_escape_string($ref)."')>'0' ORDER BY name ASC");
  $result = mysql_query($query);
  $result = db_result_to_array($result);
  return $result;
}

Thanks to @dimaninc for his help!

what value of ref are you passing in ? Can you maybe include the value of $query in your question ? — Ian Kenney, Jun 29 '13 at 22:00
Just a guess but it may be you need to urldecode $ref (http://php.net/manual/en/function.urldecode.php). it may be it is encoding a space in the $ref `John Doe` to `John%20Doe` or `John+Doe` — Ian Kenney, Jun 29 '13 at 22:09

dimaninc · Accepted Answer · 2013-07-01T20:57:02.923

6

function find_items_by_ref($ref) {
  db_connect();
  $query = "SELECT * FROM items WHERE INSTR(reference,'".mysql_real_escape_string($ref)."')>'0'";
  $result = mysql_query($query);
  $row = mysql_fetch_array($result);
  return $row;
}

INSTR is faster than LIKE in this case

or if you need LIKE, you should use it correctly:

$query = "SELECT * FROM items WHERE reference LIKE '%".mysql_real_escape_string($ref)."%'";

edited Jul 01 '13 at 20:57

answered Jun 29 '13 at 22:00

dimaninc

765
5
16

1

@user2246674 It's wrapped in `%` as in `%%%s%%`. Doh! – CodeAngry Jun 29 '13 at 22:03
when you use LIKE you should pass the string with wildcards with `%` instead of `*` – dimaninc Jun 29 '13 at 22:04
`sprintf("SELECT * FROM items WHERE reference LIKE '%s'", mysql_real_escape_string($ref));`? – user2246674 Jun 29 '13 at 22:04
`sprintf("SELECT * FROM items WHERE reference LIKE '%%%s%%'", mysql_real_escape_string($ref));` – dimaninc Jun 29 '13 at 22:04
@dimaninc, perfect! This did the trick. And I never was able to make `mysql_real_escape_string` work anywhere but at the end of the `sprintf` string and after the comma, so thanks for that as well! – QuestionerNo27 Jun 30 '13 at 05:12
Thanks to everyone else who helped as well! – QuestionerNo27 Jun 30 '13 at 05:13
@dimaninc, I've got an issue implementing the code. What should the code spit out? Because I'm getting a weird array I can't parse as my normal arrays. When I `count()` the array, for example, I get 16 for all of them, even when some have 1, 2, but no more than 3 items. And then I'm getting error messages of `Warning: Illegal string offset...`. Any ideas?? – QuestionerNo27 Jun 30 '13 at 05:45
@dimaninc, also, for the LIKE option I'm getting an error from the beginning: `Warning: sprintf(): Too few arguments...` – QuestionerNo27 Jun 30 '13 at 05:50
@QuestionerNo27 check out my answer, i've made a mistake, now its fixed (its about LIKE construction) – dimaninc Jul 01 '13 at 00:27
@QuestionerNo27 and about array, what do you mean 'weird array'? by the way, `mysql_fetch_array()` is not the best way to fetch the record, you should better use `mysql_fetch_assoc()` or `mysql_fetch_object()`. try them both and compare the results, adding `var_dump($row);` after fetching the data – dimaninc Jul 01 '13 at 00:29
@dimaninc, sorry for lack of clarity. I'm trying to parse (using your 1st recommendation) just in the same way as I'm parsing my normal arrays that work. But here, instead, after passing it through a `foreach` statement and trying to `echo` its elements, I get the "Warning: Illegal string offset..." error associated to all my variables that are working on the aforementioned arrays. And when I `var_dump` the array, everything seems fine, but upon trying anything else, it can't output specific elements. If I try `count` on the returned result from the SQL selection I always get 16. – QuestionerNo27 Jul 01 '13 at 06:20
@dimaninc, I'm updating my question to include the output of `var_dump` on the result of one of these rows. Maybe that will shed light on the issue. – QuestionerNo27 Jul 01 '13 at 06:22
P.S. Using the fixed `LIKE` I'm no longer getting the "Too few arguments" warning but now I'm getting the same warning I get with the first option "Warning: Illegal string offset..." for each variable in the array. – QuestionerNo27 Jul 01 '13 at 06:38
that was my error too :), i was writing the answer being sleepy, now its ok :) – dimaninc Jul 01 '13 at 21:10

Select from SQL database all rows that CONTAIN a certain text string in specific column

1 Answers1