[SOLVED] Array - seaching inside serialised array

[williamZanelli]

All right,

 

I have another dilemna that has arisen, just as I thought I'd fixed another major one! 

 

Ok, I have an column in my DB, which contains an serialised array (list of items).

 

I now need to conduct a query which is something like

 

Select * from tables where arrayColum = 'someVar'

 

What I'm saying is that I need to select the rows from the database where the array contains a particular value [ the array can contain an arbitary number of values]

 

I dont have any code, as I dont really know how to write this. Using real values the query should be like

 

Select * from _links where link_field1 = "Liverpool"

 

But link_field is a serialized array - Is there a special way/characters to use to carry out such an operation.

 

Or is my whole approach to this incorrect?

 

I was tempted to have a column for each Var, but then I was thinking if there can be an arbitary number, how many columns do I need to have? Will they ever be enough [there's always one of those exceptional cases when its never enough].

 

Anyway, your thoughts an opinions will be much appreciated.

 

Thanks in advance

 

William

[cooldude832]

If you use an ENUM field its very easy google how to use it.

[lemmin]

Instead of serializing, implode the array when you put it in the db, then you can query for "LIKE '%Liverpool%'" to find it. Just explode it when you take it out of the db.

[williamZanelli]

Thanks for the prompt responses guys.

 

I'm not using ENUM, I have a an array with asscoaitive arrays inside it.

 

LIKE '%Liverpool% - can't I use this with a field thats serialized [afterall that is a string representation of some object/array etc.?]

 

Thanks

 

Will

[cooldude832]

well then your storage strucutre is flawed because you shouldn't ever need to store data like that in mysql.

 

A secondary table should be used to link into this table to store array type data.

[rmbarnes82]

Hi,

 

You asked if your entire approach to this was wrong, and it is. cooldude832 hit the nail on the head when he told you that your DB structure was wrong, and now I will go into some detail on how you can correct this.

 

Ok, the problem is that you wish to store an arbitrary number of items in a single database table. As you have correctly observed, having one column for each item wont work as you don not know how many items there may be in the future. Also, although serializing and array into a single column, or imploding a comma separated list into a column may seem to work fine for updates / inserts it is very slow and verging on impossible to select based on an item in this column. Lets say you have a numeric, comma separated list in a column e.g. 2,12,45,192. Say you want to select all columns which have '12' in them:

 

SELECT * FROM my_table WHERE my_column LIKE %12%;

 

The above SQL will not work as desired. I will leave you to figure out why this is.

 

Now for the proper way.

 

Lets say we have a website selling TVs. The database schema for the TV table may look like this:

 

tv

----

id

title

size

price

 

 

Each TV can have any number of options. These, at the current time include

 

HD Ready

Widescreen

Flatpanel

Remote Control

 

However more options may be added later. We now need a second table, which we will call options. It could look like this:

 

option

---------

id

title

 

Our only problem now is that there is nothing to relate the two tables. We don't know which options are associated with which TVs.

 

The two tables have what we call a 'many to many' or 'n to n' relationship with each other. This means a TV can relate to 0 or n options, and an option in turn can relate to 0 or n TVs. To achieve this we need a join table:

 

tv_option

------------

tv_id

option_id

 

Note for this table there should be a dual primary key over each column in the table. This stops a tv having the same option more than once.

 

Ok, now say a given tv with id 213 was HD ready(id 1) and widescreen (id 2), there would be the following entries in the tv_option table:

 

tv_id option_id
213      1
213      2

 

to select all HD Ready TVs you'd do:

 

SELECT t.* FROM tv t INNER JOIN tv_option o ON t.id = o.tv_id WHERE o.option_id = 1;

 

Note I'm selecting based on option ID and not option title, as it's so much quicker and easier to search a database based on a numerical id as opposed to a string. This may mean you need some constants in you PHP cod so you have the option IDs to hand:

 

define('OPTION_HD', 1);
define('OPTION_WIDESCREEN', 2);

 

But it does mean you would have to add constants each time you added items to the option table, so you may want to select on title rather than id.

 

Don't just read up on all the possible MySQL commands, also learn about relational database theory (e.g. one to many relationships, many to many relationships, foreign keys etc.) . If you don't know this stuff you'll never be a great programmer, and will continually run into problems like this.

 

[williamZanelli]

Hi guys,

 

rmbarnes82 - thanks for the detailed reply.

 

I see what your saying,, essentially I need a row for each "array entry". My main reason for not using that approach was I needed a quick fix and rather than  changing the tables etc. I thought I could squeeze it in an a space column I had.

 

I cant see why the code below wont work as desired, is it because the field is stored as a string and the code below shows an int ??

 

SELECT * FROM my_table WHERE my_column LIKE %12%;

 

Regards

 

Will

 

[rmbarnes82]

Hi,

 

My mistake:

 

SELECT * FROM my_table WHERE my_column LIKE %12%;

 

Should have had quotes:

 

SELECT * FROM my_table WHERE my_column LIKE "%12%";

 

However, this still won't work properly. You won't get an error, it's syntactically correct. Think, however, what "%12%" will match. You only want the number 12, but this also matches 121, 123, 1201, 512 ect, so rows without the number 12 in could be returned. To get it right the query you'd have to right would be really slow if run over a large number of rows.

 

When you start to get errors like this, quick fixes become bug ridden nightmares, and the 'long' fix is actually much quicker then the 'quick' fix.

[cooldude832]

A quick fix is never a good fix.

 

A good fix will give you the flexibility you need down the road since arrays can grow and change with time.

 

even in a trivial example doing proper methods will always help.

[williamZanelli]

Thanks for the response guys, I see your points and I agree - the long fix would be the solution!!