[SOLVED] Passing Arrays into SQL

[mikesta707]

Hello. I'm pretty sure the answer to this is pretty easy, and as usual I am overthinking it, but I need a good method to pass arrays or array information into a sql database. I had come to the conclusion that you couldn't pass arrays directly into SQL ( I tried and failed once), but since my experience with this is so limited, I am not sure if that is entirely true.

 

I have made a few functions that do pass array like information into SQL. My current method is to take a string, with a common delimiter (IE ":") and pass that string into SQL. The string would look something like

$string = "Pie:Cake:Doughnuts:Strawberries:etc";

 

Now what I would do when I retrieved this string would be to use a string explode, something like

$sql = mysql_query("blah blah blah");
//..other sql stuff to put the information I want into variable $string
$string_array = explode($string);

And Now I have an array of data. Oh and my syntax might be a little off up there, but my actual script works fine at this part. This works fine for getting the data, and outputting it or processing it.

 

However, When I want to put the data back into the table, I use the implode function, IE

$string = implode($string_array, ':');

 

However, sometimes my script messes up a little, and I get an extra semi-colon here and there. Sometimes it works great, and other times it just fails.

 

I'm not really looking to fix my code or anything, I just want to know if there is an easier way to do something like this. I'm not versed to well with arrays, and how they interact with SQL, so I can't really think of another way. Thanks in advance for any responses

[Michdd]

Using explode() and implode() would be your best way to do this. However you might need to add an offset to get rid of extra endings you're talking about.

[.josh]

if you're really think the best route to go is this...

serialize unserialize

[mikesta707]

if you're really think the best route to go is this...

serialize unserialize

 

Incredible! When I was thinking about this earlier, I was hoping that a function like that did exist, and it does! thank you so much!

[.josh]

it's not that those functions are bad or nothin', but just saying, if you find yourself storing info in a db like that, then imo first thing you should do is sit down and rethink your db structure.

[mikesta707]

it's not that those functions are bad or nothin', but just saying, if you find yourself storing info in a db like that, then imo first thing you should do is sit down and rethink your db structure.

 

Why is that? I'm not too familiar with the topic of a db structure, so if you wouln't mind explaining it to me, or at least giving me some reading material, i would greatly appreciate it =)

[.josh]

well the point of a database in general is to have data stored in as much of a granulated way as possible. So when you are combining multiple values into a single cell, at best, you are making it harder on yourself to perform efficient queries, at worst, making some queries impossible.  Take for instance the following:

 

someColumn:

 

animal:monkey

animal:bat

animal:mouse

animal:snake

animal:turtle

plant:apple

plant:banana

plant:cucumber

plant:rose

plant:daisy

 

So you have this column called someColumn If you wanted to find out all the rows that were plants, you would have to write a query returning everything that is a plant by doing something simple like this (or something similar with substr):

 

select someColumn from table where someColumn like 'plant:%'

 

it would be more efficient to have that stuff as two columns, like so:

 

type someColumn:

 

animal monkey

animal bat

animal mouse

animal snake

animal turtle

plant apple

plant banana

plant cucumber

plant rose

plant daisy

 

and did this:

 

select someColumn from table where type = 'plant'

 

On the surface, that doesn't seem like a big deal.  Either way, both are relatively simple queries.  But let's say you had a larger depth like so:

 

animal:mammal:monkey

animal:mammal:bat

animal:mammal:mouse

animal:reptile:snake

animal:reptile:turtle

plant:tree:fruit:red:apple

plant:tree:fruit:yellow:banana

plant:vine:green:cucumber

plant:flower:red:rose

plant:flower:white:daisy

 

Now you are restricting yourself, because the different "levels" or "categories" suddenly become arbitrary.  3rd level animal may or may not have anything to do with 3rd level plant.  Or even within 3rd level of plants, things may or may not have anything to do with each other at that level.  It's all arbitrary.  There's no way to "label" or categorize each level so you know which ones line up with which.  That's what separating them into columns or even whole other tables are for. 

 

This may or may not apply to your specific circumstance. Just saying, in general, be weary of putting multiple values in the same cell like that.

 

[mikesta707]

Ahh ok, I see what you were saying. Yes, indeed you are absolutely right. however, in my specific case, What I am grouping together are sort of "achievements". basically a user does a specific thing on my application, and they are rewarded this achievement. In my table I am probably going to have different columns for different types of achievements, types describing what part of the application the achievement comes from.

 

I thought about making a seperate column for each achievement, and having a user id column for each row, but adding new achievements, and taking away old ones would become a pain.

 

Regardless, thank you for typing that out! I never really thought of that, and that is very good advice.

[.josh]

if the lowest level of granularity are those achievement names, then sure, what you are currently doing would be okay.  But if each of those achievements have their own attributes, then you should probably make a column called 'achievement_name' and columns for each attribute (and another column linking each row to specific user, like user_id or whatever).