0

I have a 5 level multidimensional array. The number of keys in the array fluctuates but I need to store it in a database so I can access it with PHP later on. Are there any easy ways to do this?

My idea was to convert the array into a single string using several different delimiters like #* and %* and then using a series of explode() to convert the data back into an array when I need it.

I haven't written any code at this point because I'm hoping there will be a better way to do this. But I do have a potential solution which I tried to outline below:

here's an overview of my array:

n=button number
i=item number
btn[n][0]               = button name
btn[n][1]               = button desc
btn[n][2]               = success or not (Y or N)
btn[n][3]               = array containing item info
    btn[n][3][i][0]     = item intput type (Default/Preset/UserTxt/UserDD)
    btn[n][3][i][1]     = array containing item value - if more than one index then display as drop down

Here's a run-down of the delimiters I was going to use:

#*Button Title                      //button title
    &*val1=*usr1234                 //items and values
    &*val2=*FROM_USER(_TEXT_$*name:)        //if an items value contains "FROM_USER" then extract the data between the perenthesis
    &*val3=*FROM_USER(_TEXT_$*Time:)        //if the datatype contains _TEXT_ then explode AGAIN by $* and just display a textfield with the title
    &*val4=*FROM_USER($*name1@*value1$*name2@*value2)       //else explode AGAIN by $* for a list of name value pairs which represent a drop box - name2@*value2

//sample string - a single button
#*Button Title%*val1=*usr1234&*val2=*FROM_USER(_TEXT_$*name:)&*val3=*FROM_USER(_TEXT_$*date:)&*val4=*FROM_USER($*name1@*value1$*name2@*value2)

In summary, I am seeking some ideas of how to store a multidimensional array in a single database table.

Improve this question
5
  • you can use child parent relation in single table is want to store in row level or you can encode your array into json. Commented Mar 28, 2013 at 9:31
  • 1
    you could json_encode it and save that string. Commented Mar 28, 2013 at 9:32
  • 2
    you could use php's serialize/unserialize to convert the array to/from a string Commented Mar 28, 2013 at 9:33
  • No need to invent a serialization method, PHP already implements more than one. Try with serialize() or a more general json_encode() Commented Mar 28, 2013 at 9:33
  • you could just use a session. Commented Mar 28, 2013 at 9:34

4 Answers 4

Reset to default
4

What you want is a data serialization method. Don't invent your own, there are plenty already out there. The most obvious candidates are JSON (json_encode) or the PHP specific serialize. XML is also an option, especially if your database may support it natively to some degree.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Serialize is perfect! I cannot believe I was actually going to do all that work. You just saved a couple hours of my life.
Note that PHP's serialization format is supported by virtually nothing but PHP. In the interest of keeping data stored in a database as neutral as possible, I'd prefer JSON.
Thanks but that's okay. I'm not using anything other than PHP for this project.
4

Have a look at serialize or json_encode

Improve this answer

Comments

0

The best decision for you is json_encode.

It has some advantages for json_encode beside serialize for storing in db.

  1. taking smaller size
  2. if you must modify data manually in db there will be some problems with serialize, because this format stores size of values that has been serialized and modifying this values you must count and modify this params.
Improve this answer

Comments

0

SQL (whether mySQL or any other variant) does not support array data types.

The way you are supposed to deal with this kind of data in SQL is to store it across multiple tables.

So in this example, you'd have one table that contains buttonID, buttonName, buttonSuccess, etc fields, and another table that contains buttonInputType and buttonInputValue fields, as well as buttonID to link back to the parent table.

That would be the recommended "relational" way of doing things. The point of doing it this way is that it makes it easier to query the data back out of the DB when the time comes.

There are other options though.

One option would be to use mySQL's enum feature. Since you've got a fixed set of values available for the input type, you could use an enum field for it, which could save you from needing to have an extra table for that.

Another option, of course, is what everyone else has suggested, and simply serialise the data using json_encode() or similar, and store it all in a big text field.

If the data is going to be used as a simple block of data, without any need to ever run a query to examine parts of it, then this can sometimes be the simplest solution. It's not something a database expert would want to see, but from a pragmatic angle, if it does the job then feel free to use it.

However, it's important to be aware of the limitations. By using a serialised solution, you're basically saying "this data doesn't need to be managed in any way at all, so I can't be bothered to do proper database design for it.". And that's fine, as long as you don't need to manage it or search for values within it. If you do, you need to think harder about your DB design, and be wary of taking the 'easy' option.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.