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
– Ian Kenney, Commented Jun 29, 2013 at 22:00
Just a guess but it may be you need to urldecode $ref (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
– Ian Kenney, Commented Jun 29, 2013 at 22:09

dimaninc · Accepted Answer · 2013-07-01 20:57:02Z

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 1, 2013 at 20:57

answered Jun 29, 2013 at 22:00

dimaninc

8507 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

CodeAngry Over a year ago

@user2246674 It's wrapped in % as in %%%s%%. Doh!

dimaninc Over a year ago

when you use LIKE you should pass the string with wildcards with % instead of *

user2246674 Over a year ago

sprintf("SELECT * FROM items WHERE reference LIKE '%s'", mysql_real_escape_string($ref));?

dimaninc Over a year ago

sprintf("SELECT * FROM items WHERE reference LIKE '%%%s%%'", mysql_real_escape_string($ref));

QuestionerNo7 Over a year ago

@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!

|

Collectives™ on Stack Overflow

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

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related