0

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

NullUserException
  • 83,810
  • 28
  • 209
  • 234
George Kurdahi
  • 19
  • 6
  • 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? – Wrikken Sep 01 '11 at 11:58
  • Edited my posts. Please see again. – George Kurdahi Sep 01 '11 at 12:05
  • 2
    Why are you putting serialized values in an RDBMS? – NullUserException Sep 01 '11 at 12:10
  • This is [bad design](http://en.wikipedia.org/wiki/Database_normalisation). It *will* [come back](http://stackoverflow.com/questions/7201158/join-tables-with-comma-values) [to haunt you](http://stackoverflow.com/questions/7212282/is-it-possible-to-query-a-comma-separated-column-for-a-specific-value) in the future. – NullUserException Sep 01 '11 at 12:18
  • What is your suggestion in my code? – George Kurdahi Sep 01 '11 at 12:31
  • Consider using BINARY/VARBINARY/BLOB columns instead of CHAR/VARCHAR/TEXT columns. – Wrikken Sep 01 '11 at 12:53
  • 1
    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. – NullUserException Sep 01 '11 at 12:58
  • 1
    Now you can get all your units for id=1 by using: `SELECT unit FROM arraha_units WHERE arraha_id=1` – NullUserException Sep 01 '11 at 13:00

1 Answers1

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);
Max Romanovsky
  • 2,824
  • 5
  • 30
  • 37
  • 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. – Wrikken Sep 01 '11 at 12:53
  • @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](http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html) MySQL function – Max Romanovsky Sep 01 '11 at 13:03
  • @Wrikken That's right. i used of `VARCHAR`. Which one do I use (`BINARY` or `VARBINARY` or `BLOB`) ? – George Kurdahi Sep 01 '11 at 13:04
  • @ExtractValue how is use of `json_encode`, did you can give a example of my code in first post? – George Kurdahi Sep 01 '11 at 13:08
  • @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). – Wrikken Sep 01 '11 at 13:59