querying serialized arrays

webster76

im trying to get data from a serialized field, i have never used the before and im struggling with it now.
serialized it looks like this:

a:1:{s:7:"Friends";a:2:{i:0;s:1:"4";i:1;s:1:"5";}}

unserialized like this.

array(1) {
  ["Friends"]=>
  array(2) {
    [0]=>
    string(1) "4"
    [1]=>
    string(1) "5"
  }
}

what i'm trying to get are the string values, i have no idea how to query this and searching i have been able to find an explaination just lots of large examples.

i understand how to get the field there in but not an array from inside another. thanks in advance.

cahva

Its in the same form before serializing so you can find the first value in $arr['Friends'][0].

If you want to loop through Friends array then use foreach loop:

foreach($arr['Friends'] as $key=>$value) {
    echo "Key: {$key}, Value: {$value}<br/>";
}

webster76

thank you cahva 🙂

Piranha

This thread seems to be related to this one. If you can answer, in any of these threads, why you do not normalize the database it is easier to help you out.

webster76

i tried the smple you gave mu but i'm getting a this error:
Invalid argument supplied for foreach()

$query = "SELECT contacts FROM this_table WHERE id =1";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

while ($line = mysql_fetch_row($result) )
{
foreach($line['Friends'] as $key) {

 echo "Key: {$key}, Value: {$value}<br/>";
 }

output is:
Key: 0, Value: a:1:{s:7:"Friends";a:2:{i:0;s:1:"4";i:1;s:1:"5";}}

PS. Piranha if i knew how to normalize the database i would, believe me.

laserlight

Invalid argument supplied for foreach()

mysql_fetch_row() returns a numerically indexed array, so I can say with certainty that $line['Friends'] does not exist. Even if you changed it to mysql_fetch_assoc(), $line['Friends'] still would not exist, since the only column that you selected is contacts, not Friends. Besides that, you used the other form of foreach, the one that does not get the key, but apparently you want both key and value.

if i knew how to normalize the database i would, believe me.

And if you knew how to solve your problem with a hack, you would too, but you don't. Since you know neither how to use a hack or how to use the proper approach, there is absolutely no excuse for not learning the proper approach. I suggest reading the PHPBuilder article on Database Normalization and Design Techniques for an introduction to normalisation.

cahva

With that code Im 100% sure you wont get that output 🙂 For instance in your query you're selecting "contacts" as a field. Secondly, you're using mysql_fetch_row which will give you array with numerical keys ($line[0] and so on..) so $line['Friends'] is non existant.

BUT as piranha said, you need to go back to drawing board and redesign your database and scrap this solution to trashcan. It will give you more problems in the future and also overhead.

Normalization can be hard to figure out first but you'll have to learn that some day because you cant live without it in the future.

Im not sure what data you are holding now but I presume you have a contacts table and you want to store friends which are other contacts(friends are id's of same contacts table)? Then you would create 2 tables, one for contacts and one for friends to hold relation of contacts and friends. Like this:

contacts
-----------
id
name
email

friends
--------
id
contact_id
friend_id

Now you could easily add friends to some contact. For example if you like to add contact id 2 as a friend to contact id 1 you would just make the next insert query:

INSERT INTO friends (contact_id,friend_id) VALUES (1,2);

And when you want to get friends of certain contact id you would do a select like this:

SELECT name,email FROM contacts LEFT JOIN friends ON contacts.id=friends.friend_id WHERE friends.contact_id = 1;

As my post got so huge, laserlight has also aswered and I also suggest you read the normalization tutorial. You'll get better understanding of the queries I gave you.

webster76

thanks laserlight and cahva, i will read up on normalizing the database, cavha thanks for explaining.

Piranha

webster76 wrote:
PS. Piranha if i knew how to normalize the database i would, believe me.

That is a reason to avoid normalizing. But you should really read up on normalization, it will make everything much easier. Trust me, it is worth the time to learn it and then follow normalization, at least to 3NF. I have seen lots of people trying to do it "simple" by not normalizing and then get in trouble when they can't figure out how to do queries. I have even tried it myself once or twice :rolleyes:

Basically, if you know normalizing inside out and know exactly how the database will get used now and in the future you can avoid normalizing. But often the customer will get back and ask for exactly the things you don't want them to. Or you will yourself find that you have overseen something and have to redesign lots. So normalize everything, always. That is the best bet to avoid as much trouble as possible.