Save a PHP array in mySQL column
This article explores how to save PHP arrays in MySQL. MySQL is an open-source relational database management system that allows you to store data in rows and columns. SQL stands for Structured Query Language for manipulating and retrieving data from SQL databases. Know more about MySQL.
Before You Read!
The article requires you to connect to your MySQL database with PHP, create a table and connect to it in PHP. If you’re not familiar with all these consider learning it from w3schools. Here we just focus on different ways of storing arrays in MySQL PHP.
Storing Arrays in a Normalized Table
Employees Table
You need to create an employee table at your end if you’re trying out the examples in the article. For your convenience, here’s the SQL table creation code for you.
CREATE TABLE EMPLOYEES_TABLE (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
salary VARCHAR(30) NOT NULL
)
Store PHP Array in MySQL as new row using implode with variable
There’s something wrong with the below approach. Can you figure it out?
PHP implode function converts an array into a string. The comma-separated values from the implode are ready to be used in the SQL INSERT INTO statement. Here’s an example of adding a row to the employee table using the INSERT statement.
INSERT INTO EMPLOYEES_TABLE (name, salary) VALUES (‘Tom’, ‘60000’);The code below uses implode, and SQL INSERT to add multiple rows to the EMPLOYEE_TABLE.
<?php
include "config.php";
$employees_arr =
[
["Allen", "60000"],
["Anderson", "100000"],
["Susi", "120000"]
];
$sql = ""; //SQL query string eventually.
foreach($employees_arr as $employee)
{
$data = implode(",",$employee);
$sql .= "INSERT INTO EMPLOYEES_TABLE (name, salary)
VALUES ('".$data.");"; //Builds up multi insert query.
}
//Inserts records into the database.
if ($conn->multi_query($sql) === TRUE)
{
echo "New employees records added successfully.";
}
else
{
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close(); //Closes the connection finally.
//OUTPUT
//New employees records added successfully.
/// SECURITY RISK - DO YOU KNOW WHY?
// WHY SHOULD YOU NOT USE THIS?
?>
In the code, the PHP implode function receives an employee array and turns it into a comma-separated string. The string is appended to the $sql variable. This variable stores the query string, just the same SQL syntax we’ve seen above already.
The difference here is that the code performs multi-insert, and thus the loop keeps on adding to the query string. Finally, it saves the array in MySQL PHP using the object-oriented way – learn more about MySQLi Object-oriented here.
Security Risk – SQL Injection: Don’t Use
Ok. So we just showed you a solution and you likely will come across this solution on Stack Overflow or other resources. Don’t use it! It will open up your database to vulnerabilities.
Don’t include PHP variables directly into your SQL statements. You need to parameterize them with statements.
Why is this?
Because your variable may come from an unknown value like from a browser. What if someone evil puts it into your text box and it gets passed your validations?
DROP TABLE EMPLOYEES_TABLE;
Not good.
This is why parameterized statements exist. You need to write your general statement and then bind your variables as parameters. Check out the below links and examples to learn more.
Store Array in MySQL as new row using foreach loop and parameterized statement
The implode function is helpful when there are many columns or data points. If there are a few, then the for loop is easier and more efficient. The database table that we’ve been looking into includes two columns only – name and salary.
Let’s see how to save an array using for loop in PHP.
<?php
include "config.php";
$employees_arr =
[
["Allen", "60000"],
["Anderson", "100000"],
["Susi", "120000"]
];
// prepare and bind
$stmt = $conn->prepare("INSERT INTO EMPLOYEES_TABLE (name, salary) VALUES(?, ?)");
foreach($employees_arr as $employee)
{
$name = $employee[0];
$salary = $employee[1];
// Let's bind our parameters correctly
$stmt->bind_param($name, $salary);
$stmt->execute();
}
$conn->close(); //Closes the connection finally.
//OUTPUT
//New employees records added successfully.
?>
The code is pretty much the same except that it gets the name and salary of an employee through direct indexing from the array.
Save Analytics & Non-normalized Data
Ok.. the above is great to transform an array into new rows, but what if you wanted to save the entire array as a string for analytics purposes into a single column?
Use the json_encode function to store the full PHP array in MySQL
Save it as JSON!
JSON is generally the preferred format to store arrays in MySQL columns. It is easy to insert, read and pull out of the database. All modern languages can interpret and extract it. In addition, you can actually query on a JSON object since MySQL 8. This should be the goto unless you have a specific reason not to use this method.
Check out the example below using json_encode
Let’s create an analytics table.
CREATE TABLE EMPLOYEE_REVIEWS ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id INT(6) NOT NULL, details TEXT NOT NULL )Alright. Now let’s insert some data.
<?php
include "config.php";
$employeeID = 1;
$reviews = ["GOOD", "BAD", "GOOD", "POOR", "EXCELLENT", 3, 0];
// prepare and bind
$stmt = $conn->prepare("INSERT INTO EMPLOYEE_REVIEWS (employee_id, details) VALUES(?, ?)");
$stmt->bind_param($employeeID, json_encode($reviews));
$stmt->execute();
$stmt->close();Notes about json_encoding & storing full array
The above content probably isn’t realistic but I did write it a certain way to show the potential case where you may need to store different types of data. This is pretty common in analytical data. You just need to store it and your data ETL processes will manage it for the business users later.
You could directly query the details column using MySQL, but you’re more likely using the SQL database as a simple data store. This is reasonable to do in small amounts but it is a bit of a code smell. A smarter solution would likely to store the arrays as JSON or CSV in an s3 bucket.
Save the Array in MySQL PHP using serialize
What if you are unable to use JSON for some silly reason?
You can use the serialize function.
The serialize function returns a byte-stream representation of an array. It is not readable unless you call unserialize to turn it back into an array. It is useful if you want to save just one long string in a single column in the database.
Now, it sort of defeats the purpose of a relational database because you cannot have many fields and thus relationships between the tables in a true sense. It is just one way of storing an array as a string in a database.
Here’s how to save the array in MySQL PHP with the serialize function.
<?php
include "config.php";
$employees_arr =
[
["Allen", "60000"],
["Anderson", "100000"],
["Susi", "120000"]
];
$stmt = $conn->prepare("INSERT INTO EMPLOYEE_REVIEWS (employee_id, details) VALUES(?, ?)");
$stmt->bind_param($id, serialize($employees_arr));
$stmt->execute();
$stmt->close();
$conn->close(); //Closes the connection finally.
//OUTPUT
//New employees records added successfully.
?>
See that it doesn’t need a loop anymore, it serializes the entire array and saves the byte stream string to one single data column in the EMPLOYEE_REVIEWS table.
Wrap Up
Phew! That was a lot of stuff to digest. The article explores different ways of storing arrays in MySQL with PHP. It peeks into the implode function and how it saves the array into the database. Next, it explores the for loop method and finally the serialize function to store one long string in a single column.
That’s all about it. We hope you’ve liked it. Stay tuned for more at FuelingPHP.
Want to learn more about PHP?
We have many fun articles related to PHP. You can explore these to learn more about PHP.
