unserialize() value of database and putting it in json_encode after foreach

Question

I insert in database values (array) $row->units with use function serialize()=>[$row->units], how can echo they with unserialize() in json_encode with $row->name? (return send for ajax call in jQuery)

Columns in database:

$row->units => a:6:{i:0;s:15:"Coffee";i:1;s:14:"Satellite";i:2;s:11:"Game Notes";i:3;s:14:"Internet";i:4;s:10:"Pool";i:5;s:0:"";}

$row->name=> George Kurdahi

$query = $this->db->query("SELECT * FROM arraha WHERE name LIKE '%$search%' ORDER BY name asc");

$data = array();
foreach ($query->result() as $row)
{
   $data[] = array('name' => $row->name, 'units' => unserialize($row->units)); // Line 22
}
return json_encode($data)

The error for code above is:

A PHP Error was encountered

Severity: Notice

Message: unserialize() [function.unserialize]: Error at offset 277 of 281 bytes

Filename: model.php

Line Number: 22

Threegaited

Either your serialize failed, the column isn't big enough to hold the data, or some charset issue. Could you show us the string before you unserialize, and a SHOW CREATE TABLE arraha output? – Lothaire

Edited my posts. Please see again. – Threegaited

Why are you putting serialized values in an RDBMS? – Meri

This is bad design. It will come back to haunt you in the future. – Meri

What is your suggestion in my code? – Threegaited

Consider using BINARY/VARBINARY/BLOB columns instead of CHAR/VARCHAR/TEXT columns. – Lothaire

Create another table, say arraha_units, with two fields, say arraha_id (FK to your PK in arraha) and unit (and perhaps its own PK as well). Get rid of the units column in arraha. Now insert rows like (1, 'Coffee'), (1, 'Satellite') into this new table. – Meri

Now you can get all your units for id=1 by using: SELECT unit FROM arraha_units WHERE arraha_id=1 – Meri

Answer 1

You have some issues with character encoding:

s:15:"Coffee"

15 means length in bytes. So you have to translate encoding of data fetched from DB into encoding that was used with serialize()

You can use json_encode instead of serialize:

$arr = array('Coffee', 'Satellite', /*...*/);
$row->units = json_encode($arr);

Latticework

This is true, and your serialized column should not have a character set. You're not storing 'textual' data as far as serialized strings are concerned. Consider using BINARY/VARBINARY/BLOB columns instead of CHAR/VARCHAR/TEXT columns. – Lothaire

@George If you don't want to change your application design either change the column type in DB or replace serialize with json_encode. Or you can store your array as XML document. Then you can query it with ExtractValue MySQL function – Latticework

@Lothaire That's right. i used of VARCHAR. Which one do I use (BINARY or VARBINARY or BLOB) ? – Threegaited

@ExtractValue how is use of json_encode, did you can give a example of my code in first post? – Threegaited

@George Kurdahi: type depends on need. How much data do you need to enter there? (And although NullUserException's comment is somewhat premature because he doesn't know your data yet, database normalization could be a better solution). – Lothaire