How to store an array in mysql database through php

Answer 1

There are basically 2 options to consider. Each has its own pros and cons.

- 1 array element per record. For example, each user has many preferences and you have a separate array element corresponding with each preference. You could create a user_preference table where each record of user_preference corresponds with a single element in this array you're asking about. This will make the preferences for a given user easier to filter using SQL which is nice. What's less nice is that you have to join the user and user_preference tables together if you want that information combined so some SQL queries will be more complex. More database tables also generally adds a little more complexity.

- all array elements in the same record. This could be done with something like a JSON type field. Say you have an array for user preferences and a user table. You could add a user_preferences field on the user table. The datatype could be text, or json ( https://dev.mysql.com/doc/refman/8.0/en/json.html ) if that's supported in your version of mysql. I think JSON is new to MySQL 8 but not supported in MariaDb. The value can then be a JSON-formatted representation of your array. This will be 1 less database table, 1 less join if you want to query user and the preferences together, but you'll have less ability to filter user preferences in an SQL-where clause. Querying for a specific user preference may need to combine both an SQL query to get all user preferences for the user and then you need your server-side programming language to filter more.

Consider both of them, their pros and cons, and ask yourself how you might want to access elements from the array or if you want to filter elements of this array.

The 1 record per array element approach with 2 tables would generally be more common in SQL. The array in a new field is less structured in SQL and more like the format you could store in MongoDb.

Answer 2

Josh Greig A third, albeit less preferred, option would be to store the array as a PHP serialized string. This seems to be a popular practice among many of the PHP applications I've reviewed over the decade, especially in WordPress.