An array stored in MySQL is not recognized as a value

Question

I am trying to store a json encoded array into a MySQL field. The stored array is in the form:

['value1', 'value2', 'value3']

Then I try and run a query like:

SELECT * FROM mytable WHERE arrayfield

it returns no results. The arrayfield is either blank or contains an array, so I am trying to get the records that only contain an array in that field.

Answer 1

Try

SELECT * FROM mytable WHERE arrayfield != ''

Does that produce any results?

Answer 2

Did you properly escaped your data when inserting/updating with your SQL query (see mysql_real_escape_string and sprintf)? Are magic quotes OFF?

As Machine points out this is rather a bad design since relational databases aren't meant for that kind of stuff...

Also test these queries:

SELECT * FROM mytable WHERE arrayfield != ''

or

SELECT * FROM mytable WHERE arrayfield IS NOT NULL

do they both return empty results? If so, check that your INSERT/UPDATE query are working...

Answer 3

You shouldn't store arrays, or any kind of object, in a relational database. A relational database, to be in first normal form, should consist only of fields with basic datatypes. That is INT, CHAR, VARCHAR, DECIMAL etc. Additionally, it should contain no repeating groups, and the columns should have no intrinsic ordering. Arrays, by their very definition, violates this. If you feel the need to store other values, you have misunderstood the concept of a relational database.

If you want to store objects, such as arrays etc, then you should try some database system that is created for such use, i.e. Object Databases

The best way to store the array is to make an additional table for the array, and put each value of the array in its own row, then store some kind of array identifier in the parent table.

Example:

CREATE TABLE People (
  ssn INT,
  first_name VARCHAR(15) NOT NULL,
  last_name VARCHAR(20) NOT NULL,
  PRIMARY KEY (ssn)
);

CREATE TABLE Emails (
  belongs_to INT,
  email VARCHAR(128),
  PRIMARY KEY (belongs_to, email),
  UNIQUE(email),
  CONSTRAINT fk_belongs_to_ssn FOREIGN KEY (belongs_to) REFERENCES People (ssn)
);

In this schema, every person can have multiple emails. Each email belongs to a person in the People table. A set of emails can be found for a person by joining People with Emails on ssn = belongs_to. A single email can easily be found by searching the email-column for a particular value and/or pattern.