mysql/field type with php array Q

Anon

More of a mysql question than a PHP question, but it's pretty straightforward -

if an mysql field is going to be populated by a php array, what type should the field be, and what does it's length refer to?

ie field name is categories, and is currently set to type VARCHAR with length 18 (since none of the categories will exceed 18 characters in length)
However, the field will actually be populated with a 1 or 2 dimensional php array - if it's a 1 dimensional array, then the width will clearly exceed 18 characters, if it's 2 dimensional with the categories as $categories[cat_id][cat_name], then it shouldn't exceed 18 characters.

I think I'm getting muddled in my visualisation, or at least how myslq interprets/stores an array.

Can someone please clarify ...

[deleted]

"if an mysql field is going to be populated by a php array, what type should the field be, and what does it's length refer to?"

There is no such type, because you never ever store an array in a field.
Instead, create a one-to-many relationship with a new table.

You are definately in a heap of doodie with your current approach.

Read this and then re-think your strategy:
http://www.hvt-automation.nl/yapf/faq.php?cmd=viewitem&itemid=4

Anon

yup, thanks Vincent -
I think I was getting confused with how to store arrays -
I think I'll stay with my current mysql setup -

d.dept_id
d.dept_name

c.cat_id
c.cat_name
c.dept_id

to use two tables to link categories to departments - I was wanting to perhaps be more slik with 1 table
d.dept_id
d.dept_name
d.categories

where d.categories stored c.cat_id and c.cat_name in an array.
The only reason I thought of doing this is because I'm up to c.cat_id #800, and have to index 3 fields - if I could have stored categories in an array, I could have gotten away with 2 indices, and d.categories[cat_id] numbers of <20.

So to store an array, it must first be imploded, with, say a " | " delimiter? I ask because my products can belong to more than one department/category combination - at present I have my products table classified with a $class_id field, where the numbers,
eg 4.12 | 10.115
class the product as belonging to

dept_id 4 / cat_id 12
AND
dept_id 10 / category_id 115

you see how my category id's are building up, even though there are only 8 categories in department 10.

Does this make any sense? if so, am I heading in the right direction or going to come a cropper some time in the future?

Thanks

Anon

I think that you can store php array in mysql.

Determine how big your array could be, because it will affect the mysql storage requirements. Chances are you'll have to make mysql field with a data type of tinytext, which holds 255 bytes.
serialize() your array and then write output from serialize to a mysql table.

When you retrieve array you'll have to unserialize() it.

Anon

wouldn't that cause a headache to query though?

wouldnt you have to serialize() and addslashes() your $queryterm?

[deleted]

"wouldn't that cause a headache to query though?"

Worse: you can't query that at all.

Your array would look like a delimited string containing the values. That means that if you want to get any info from it, you'll have to use LIKE and SUBSTRING etc.
What's more, updating it would be near impossible; using a select to get all the arrays, unserialize them back into arrays, modifying the arrays, serializing them again and updating the strings in the database.

Anon

Look,

you asked if array can be stored in mysql table. It can be through the use of serialize() and unserialize(). In some instances it can be very useful, whether it is useful for you I don't know.

You asked if it'll cause headache when executing a query. I don't know. If you do select array_field from your_table, and afterward you unserialize() array, then the answer is no. However if you intend to do more complex queries, then the answer is likely yes, because (if you would've studied mysql manual) mysql doesn't support data type that is an array!

Anon

yeah, thanks -

didn't mean to cause offence, but the knowledge you provided will not fall on deaf ears, since i can foresee it proving useful for a not-to-distant field.

have studied the php and mysql manuals, but you know, there are so many things to learn my poor lil brain has dumped some info to fit it all in. I don't profess to understand all this, just trying to have a bash, and having fun in the process.

Seriously, cheers though - it's people like you that respond to our half-wit questions that keeps me (and others) going.
;-)

Anon

No offense taken,

Hope my post provided some insight.