Drupal 6 store array in mysql table as varchar is error invalid argument supplied

Question

I made a mysql table storing questions and their Drupal 6 attributes like prefix, suffix, options, title, value, type... etc.

Unfortunately the stored options value is an array and I get an error "warning: Invalid argument supplied for foreach()"

the code is:

$fruit = db_query("SELECT type,title, value, section, collapsible,collapsed, description, options, size, prefix, suffix, default_value FROM {table} ");
  $count = 1;
  while($slice = db_fetch_array($fruit)){
  $section = $slice['section'];
  $op = $slice['options'];
    $form[$count] = array(
    '#type' => $slice['type'],
    '#title' => $slice['title'],
    '#collapsible' => $slice['collapsible'],
    '#collapsed' => $slice['collapsed'], 
    '#description' => $slice['description'], 
    '#options' => $op, 
    '#size' => $slice['size'], 
    '#prefix' => $slice['prefix'], 
    '#suffix' => $slice['suffix'], 
      );
$count = $count+1;
      }

in one particular case the option is

array(t('yes'), t('no'))

where type is 'radios'
which is what is being stored as a varchar (blob isn't working either) (adding a comma doesn't help either.

Where is the code for saving the data? And do you also increment the $count variable? Otherwise you'll end up with only one value. — Max
yes yes, i just showed the problem area. I'll add that if you think it adds value! — ingrid
saving data is not really part of the problem here so i wont add that. that is part of the submit, not the form processing — ingrid
I explicitly wrote out what my problem is. I already have a functional form from this. The problem is the options value, like i state in the problem. — ingrid

Wang Wang · Accepted Answer · 2013-03-12T12:48:27

Do not serialize your arrays to strings. This is one of the common mistakes people make when storing lists in databases (although to be fair it's better than making a fixed number of columns for the first n items).

I would make a separate options table where each row has two columns: (question_id, option) where question_id is a foreign key into your table of questions. The query would look something like,

SELECT option FROM options WHERE question_id = <<$id>>;

(<> isn't actual SQL, just a placeholder for whatever the PHP parameterized query syntax is.)

If the options need to be ordered, add a third column rank:

SELECT option FROM options WHERE question_id = <<$id>> SORT BY rank ASCENDING;

Edit: Why not serialize arrays into strings?

This isn't something I've studied in depth, but off the top of my head I can think of performance, convenience, and theory:

For performance reasons, databases are optimized to store fields of fixed width. If you start putting lists into fields, eventually you'll encounter a list with too many items, and need to resize the field. Meanwhile, databases are highly optimized to store tables of arbitrarily many rows, and you never need to resize the table to accommodate more rows.
As a matter of convenience, it's really annoying to run queries against lists that have been serialized into a single field. Most databases have very poor support for querying into lists stored as fields. From the database's perspective, that's just a string or maybe even an opaque binary blob.
From a theoretical perspective, it violates the fiction that relational databases are reifications of the relational algebra. But this turns out to have practical consequences, because the query optimizer uses equivalence theorems from the relational algebra to rewrite your query into a more performant form. Violating the assumptions of the relational algebra tends to generate queries that optimize poorly (although never incorrectly).

Drupal 6 store array in mysql table as varchar is error invalid argument supplied

2 Answers