0

Please could someone help me? I want to select all values in mysql table where the column that i want to check get a value is mix with aingle or array of values....... So to be more clear I have a table to store all messages from many sender to one or many reciever....

my functions is 

public static function find_messagesTo_by_user_id($mess_to=0) {
          global $database;
          $mess_to = $database->escape_value($mess_to);
          $sql  = "SELECT * FROM ".self::$table_name;
          $sql .= " WHERE mess_to = '{$mess_to}'";
          $sql .= " AND mess_deleted = 0";
          $sql .= " ORDER BY mess_created_date DESC";
          $result_array = parent::find_by_sql($sql);
          return $resultrray;
      }

So 'mess_to ' has array and single value .... they are only numbers Like (1, 15, 25 ,26 ,27 , array(1,25, 27) , 31, 42, .......)

Please, i break my head on it :)

I waiting for any help?

user2871559
  • 1

3 Answers3

1

Building on @Maluchi's answer. Make sure your data looks like:

| mess_to         |
+-----------------+
| ,123,           |
| ,123,456,152,1, |
| ,456,567,       |
| ,3,             |

So surround each value in ,. then you can safely do: 

WHERE `mess_to` LIKE "%,{$mess_to},%"

This ensures that $mess_to = 1 will match only the 2nd row, and not the 1st as well.

You could also denormalize your data and make a table to JOIN on.

Halcyon
  • 57,230
  • 10
  • 89
  • 128
1

If I'm reading it correctly, $mess_to is passed into the function and could contain either a single value or it could be passed in an array.

When matching multiple values, the SQL should be looking for a comma-separated list. The where clause needs to be IN the list rather than EQUAL to the list.

Try:

public static function find_messagesTo_by_user_id($mess_to=0) {
      global $database;
      $mess_to = $database->escape_value($mess_to);
      $sql  = "SELECT * FROM ".self::$table_name;
      $sql .= " WHERE mess_to IN (" . implode(',', $mess_to) . ")";
      $sql .= " AND mess_deleted = 0";
      $sql .= " ORDER BY mess_created_date DESC";
      $result_array = parent::find_by_sql($sql);
      return $resultrray;
  }

See this line in particular:

$sql .= " WHERE mess_to IN (" . implode(',', $mess_to) . ")";

Code edited with geomagas's comments! (Thank you geomagas!)

Simon Shirley
  • 328
  • 3
  • 14
  • Good thinking! Two things though: 1) `IN` needs parentheses, not quotes and 2) you should `implode()` [recursively](http://stackoverflow.com/questions/3899971/implode-and-explode-multi-dimensional-arrays). – geomagas Oct 11 '13 at 16:26
  • Oh, and a third: `implode(',',array($mess_to))` would cover the single-value case (provided the above recursiveness). – geomagas Oct 11 '13 at 16:31
  • Please see previous comment. – geomagas Oct 11 '13 at 16:35
  • Not sure if my edits cover point 2. I also don't read the problem as passing in an array with arrays included. Maybe I'm reading it wrong. – Simon Shirley Oct 11 '13 at 16:43
  • Well, no it doesn't. Please see the examples in [this fiddle](http://phpfiddle.org/main/code/xuj-k24). – geomagas Oct 11 '13 at 17:36
0

asuming your column is like this

| mess_to |
+---------+
| 1       |
| 1,2,3,4 |
| 2       |
| 3       |

you can use the LIKE operator:

$sql .= " WHERE mess_to LIKE '%{$mess_to}%'";

this will match every row where mess_to has the string value of $mess_to (your column data type should be string for this to work).

Mateo Torres
  • 1,545
  • 1
  • 13
  • 22