Storing arrays in mysql

Dolemite50

Hi,

I have a table storing items which are prepped to send to Flash. The table has only two columns, "item" and "string", so that I store the dozen or so different items like this:

item: hot_topic
string: topic_id=1234&topic_starter=EddieMunster&topic_txt=Hi there

When I update the hot_topic I use "UPDATE string WHERE item = hot_topic";

This is working fine for sending vars to Flash as they need to be, but how can I go about retieving the vars in my regular PHP applications? For example, how would I be able to echo the topic_starter for hot_topic? When i query the db for "string WHERE item=hot_topic", it gives me the result as: "topic_id=1234&topic_starter=EddieMunster&topic_txt=Hi there"

From there I've tried every angle I could think of using extract, explode, serialize, etc,.but can't seem to get the individual vars. The closest I've come is by exploding the result with "&", and then looping through it and exploding again using "=", but I still couldn't get it too work right.

Any help?

Thanks

devinemke

$string = 'topic_id=1234&topic_starter=EddieMunster&topic_txt=Hi there';
$explode = explode('&', $string);
foreach ($explode as $value)
{
	$value = explode('=', $value);
	echo $value[0] . ' : ' . $value[1] . '<br>';
}

i strongly suggest you reconsider this data design. you should really store each on these variables in a separate field:

linuwell

I agree

WDPEjoe

It's true, those values should be in different columns.

However, to make it work like you want, you should use the parse_str() function in PHP.

If you just pull out the string, then run
parse_str($string, $arr)
it will parse it as though it were a URL into an associative array, $arr.

Dolemite50

Hi guys,

Thanks. Believe me, I had expected to catch some chit for using the array storage approach, but its the best I could come up with for my scenario. Would anybody care to offer a suggestion?

I am storing at least 15 different "current event" items which are being passed along into Flash throughout my site. They are all updated via seperate timely events and vary in data. Some may be just "name, pic, and url",..and others may be something entirely different. The variables are named in a manner that Flash is expecting, such as the Featured Artist data being feat_n=tom&feat_p=1234.jpg&feat_u=toms-site. An arena battle might be bat_n=bluesoff&bat_sngida=1234.mp3&bat_sngidb=1444.mp3&bat_id=75&artista=theYahoos&&artistb=SilkUndies. The end result is that all of these strings are joined into one long mess and passed into Flash. I pre-inserted the item names into the table so that in order to update something, I just update the string column for that item. After that I'll query the curr_events table to gather all of the strings into one variable used to overwrite the existing toFlash.txt file.

So basically I'm just storing 15 or so of those strings. I had considered using one table with a column for every event's items but it would push 100+ columns in a hurry. I'd give each item its own table but I don't like the idea of 15+ extra tables only storing data for one item. I could just have one table with a column for each of the 15 items, but I'd still need to store variables the same way as I am now.

For the record, my current method seems to be working fine. The reason I've asked my question is that if I could have easy access to those variables, it would save me a few queries in other "non-Flash-related" areas of the site.

Any thoughts?

Thanks again. 🙂

WDPEjoe

see my post above, i think it solves your problem. if you want to use the variables in a php script, pull the string out of the database and use the parse_str() command. It will parse all the variables to an associative array in the current scope.

Powerboss

I'm having a similar problem. I have a masterlist.

This is seperated into armor weapons foods ETC...

I'm in the process of making a game. Well I have a members table. General info about a character username ID password tester mod or any admin access at all that sort of deal.

Next I have a Char_info table basically has the equipment being worn on the character the characters level exp and a few more.

Now what I'm wanting to work on is inventory the character has extra not equipped. I can think of ID charactername the equipment belongs to name of item and last qty of that item.

I'm wanting to try and store into table inventory all the weapons armor and equipment that a character has here along with Quantities as simple as possible. I want players to see of course whatever they have in a listbox and admin to show this as a combobox. I know this isn't easy but i need help to continue on. I'm not worried about the type of item because that will be in the master(s)list table so when I call from one or multiple masterlists I can then seperate at the time of calling something along the lines of...

if type == "head" then show in this listbox/combobox

so that way players can buy/sell using listboxes

while admin people can edit multiple entries at a time highlighting more than 1 and adding multiple quanities also if needed like for potions to heal so you don't need to click add 50 million times...

at the same time combining 2 or more sets of like 12 heal potions and another 13 heal potions making one single entry of 25 heal potions so same names combine.

So after this long winded commented reply is there anything someone can do to help?