Insert PHP array into MySQL database as text

Question

I am willing to insert an array to a database as normal text.

$subjectcodes[1] = "";
$subjectcodes[2] = "";
$subjectcodes[3] = "";
$subjectcodes[4] = "";
$subjectcodes[5] = "";
$subjectcodes[6] = "";
$subjectcodes[7] = "";

etc...

I give my array an stringname called list_update. So you get this ( I am having "\" so it doesn't see it as PHP )

    $list_update="
    \$subjectcodes\[1\] = \"\";
    \$subjectcodes\[2\] = \"\";
    \$subjectcodes\[3\] = \"\";
    \$subjectcodes\[4\] = \"\";
    \$subjectcodes\[5\] = \"\";
    \$subjectcodes\[6\] = \"\";
    \$subjectcodes\[7\] = \"\";
"

Now when I use my query to input my list into a table it doesn't do anything.

 mysqli_query($connect,"INSERT INTO subjects (list)
        VALUES (" . $list_update . ") WHERE user='". $userid ."'");

Does someone know how I can solve this?

Thank you very much in advance ;)

So what does `mysqli_error()` tell you? Please also learn to always either use `mysqli_escape_string()` or prepared statements. — johannes, Feb 03 '14 at 17:59
@johannes It says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\[1\] = ""; $subjectcodes\[2\] = ""; $subjectcodes\[3\] = "' at line 3 — Ahmed, Feb 03 '14 at 18:02
I think, its a really bad idea to store it in array for the 1st place, because retrieving & querying data wont be efficient. — Akhil Sidharth, Feb 03 '14 at 18:03
@AkhilSidharth Well do you know another way to store an "array" in mysql. And I can retrieve the queries easy and use it after doing eval() But storing is the problem :( — Ahmed, Feb 03 '14 at 18:04
json encode your array and insert into db. When you need it , fetch it from db and then again json decode it and you will have your original array. — Altaf Hussain, Feb 03 '14 at 18:08
When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **Avoid** using string interpolation to accomplish this. What you're doing here is probably wide open to [SQL injection](http://bobby-tables.com/). — tadman, Feb 03 '14 at 18:44

score 3 · Answer 1 · edited May 23 '17 at 12:21

A PHP array or object or other complex data structure cannot be transported or stored or otherwise used outside of a running PHP script. If you want to persist such a complex data structure beyond a single run of a script, you need to serialize it. That just means to put the structure into a "lower common denominator" that can be handled by things other than PHP, like databases, text files, sockets. The standard PHP function serialize is just a format to express such a thing, it serializes a data structure into a string representation that's unique to PHP and can be reversed into a PHP object using unserialize. There are many other formats though, like JSON or XML.

From here

score 2 · Accepted Answer · answered Feb 03 '14 at 18:12

Do it as below:

$subjectcodes[1] = "";
$subjectcodes[2] = "";
$subjectcodes[3] = "";
$subjectcodes[4] = "";
$subjectcodes[5] = "";
$subjectcodes[6] = "";
$subjectcodes[7] = "";

json_encode($subjectcodes);

Insert into your DB.

After you fetch it from DB, then decode it as below:

$array = json_decode($yourArrayItemFromDB);

ANd you will have your original array like $subjectcodes.

Using this way, you can not only save arrays, but you can also save objects in DB.

Insert PHP array into MySQL database as text

2 Answers2