PHP Query Help - Serialized Data

[Morty222]

When I insert a new event into the table, I serialize almost all the data except a few fields I needed to keep separate.

Here is what my serialization looks like in the database:

The field this data is stored in is called host_array:

a:16:{s:5:"sport";s:8:"football";s:14:"tournamentname";s:21:"Grid Iron Challenge 2";s:11:"sanctioning";s:7:"Testing";s:4:"age1";s:2:"15";s:4:"age2";s:2:"16";s:4:"cost";s:3:"450";s:5:"sdate";s:10:"2010-03-05";s:5:"edate";s:10:"2010-03-10";s:5:"tcity";s:5:"Wayne";s:6:"tstate";s:2:"MI";s:5:"games";s:1:"5";s:13:"teamsaccepted";s:2:"15";s:16:"pslteamsaccepted";s:1:"5";s:6:"hotels";s:6:"Hotels";s:7:"details";s:7:"Details";s:6:"button";s:8:"Continue";}

I can unserialize this data and show it on the page without problems.

My question is, I want to query something from inside the serialized data.

Example:

I need to get all hosts who tstate within the serialized data equals MI. Any way of doing a query like this?

Thanks!

[ShawnCplus]

Take a look at how the serialization is built and emulate it for a LIKE clause

$search_field = 'tstate';
$search_field = 's' . strlen($search_field) . '"' . $search_field . '"';

$search_value = 'MI';
$search_value = 's' . strlen($search_value) . '"' . $search_value . '"';
// s:6:"tstate";s:2:"MI";
$search_string = $search_string . ';' . $search_value;

$query = 'SELECT blah FROM blah WHERE blah LIKE \'%' . $search_string . '%\'';