Search for a value in where clause from Serialized Array

Question

Hello,

The title could be confusing, but I think my doubt is clear.

I'll explain. I'm doing this query:

 $sql = 'SELECT * FROM calendar WHERE day = "'.$day.'" AND month = "'.$month.'" AND year = "'.$year.'" AND realizada = 0 AND colaborador = "What to put here?!"';

But the field "colaborador" is a serialized array.

One example, when I print_rthe value of my array after unserialize it's something like this:

Array ( [0] => l3gion [1] => Someone [2] => teste )

Imagine that I want to search for "l3gion" in the previous Query, how can I do this?

Thank you.

l3gion

Answer 1

The short answer: Don't serialize data into a database field. That's what normalization is for...

The long answer is it's going to be VERY difficult to do. You could do a search for colaborador LIKE '%"13gion"%'... You could write a regex to deal with it as well (And use MySQL's REGEXP expression)...

But the best solution is to not store serialized data in the database...

Answer 2

colaborador = "What to put here?!"

This code will deal with serialized and raw data, as well as array and plain string values.

if (is_string($colaborador)) {
    // prevent errors from showing
    ob_start();
    $data = unserialize($colaborador);
    // $data is now false or output buffer is non-empty if $colaborador was
    // not serialized
    $data = ob_get_length() > 0 || false === $data ? $colaborador : $data;
    // lose the possible error
    ob_end_clean();
} else {
    $data = $colaborador;
}
// use the first value as colaborador if an array
$sql = 'SELECT * FROM calendar WHERE day = "'.$day.'" AND month = "'.$month.'"
AND year = "'.$year.'" AND realizada = 0 AND colaborador = \'' .
(is_array($data) ? (empty($data) ? '' : $data[0]) : $data) . '\'';`

Or if you want to search all of them:

$sql = 'SELECT * FROM calendar WHERE day = "'.$day.'" AND month = "'.$month.'"
AND year = "'.$year.'" AND realizada = 0 AND colaborador = IN (\'' .
(is_array($data) ? (empty($data) ? '' : implode("', '", $data[0])) : $data) . '\')';`

Answer 3

If you need to query individual elements from your array, don't store the serialized array. Store each element on an individual row in a child table, and associate it with the primary key value of your calendar table:

CREATE TABLE colaboradoras (
  calendar_id INT NOT NULL,
  colaborador VARCHAR(20) NOT NULL,
  FOREIGN KEY (calendar_id) REFERENCES calendar(calendar_id)
);

INSERT INTO colaboradoras VALUES
(1234, 'l3gion'),
(1234, 'Someone'),
(1234, 'teste');

$sql = "SELECT * FROM calendar AS c JOIN colaboradoras AS o 
              ON c.calendar_id = o.calendar_id
        WHERE c.day = $day AND c.month = $month AND c.year = $year 
          AND c.realizada = 0 AND o.colaborador = 'l3gion'";

This is the normalized approach.

If you must store the serialized array, you might check out How FriendFeed Uses MySQL to index "schemaless" data. But that also involves creating new tables for the indexes.

If you really can't create any new tables or indexes, you can try to use LIKE or REGEXP but both of these solutions will be very inefficient and error-prone.

SELECT ... WHERE ... AND colaborador REGEXP '[[:<:]]s:6:\"l3gion\";'

You're screwed.