How to store multiple records in one SQL column with PHP?
What would be the correct way to store multiple values in one SQL column using PHP.
For example, in a system that stores Contact / Client data and you can add more than one phone number. For example, you can store 2 mobile numbers, 1 office number and a couple of other numbers for the same customer.
But I don't quite understand how you could store this data in a single column or create a column for Home #, Mobile #, Work # and ect.
In my understanding this can be saved as JSON format, but I could be wrong.
Thank.
source to share
If you want to store multiple phone numbers for a given contact, you must do so with multiple entries, not multiple values in this entry. For example, you could have a table Numbers
that looks something like this:
id | contact_id | number | type
1 | 1 | 591-8563 | 1
2 | 1 | 123-4567 | 2
3 | 2 | 867-5309 | 1
Here the column type
can record if the phone is, for example. stationary (1) or cellular (2). You can see that the contact 1
has both landline and cell numbers, but 2
only landline. This table Numbers
can be attached to the contact table.
source to share
It's good if you, for example, have a dynamic number of phone numbers (for example, multiple mobile numbers), you can instead create a table for phone numbers only and add a customer relationship:
+-----------------------------------+
| phone_number |
+-----------+-----------+-----------+
| client_id | type | number |
+-----------+-----------+-----------+
| 5 | office #1 | 055768765 |
+-----------+-----------+-----------+
| 5 | mobile | 017884778 |
+-----------+-----------+-----------+
...
However, if you really want to keep all the numbers in one customer column, here's how you could do it:
# Write:
$phone_numbers = array('office #1' => '055768765', 'mobile' => '017884778');
$phone_numbers_for_db = json_encode($phone_numbers);
# Read:
$phone_numbers = json_decode($phone_numbers_from_db);
source to share
This is not possible in MySQL - if you don't want to store JSON in a single column like contact_information and then store all the data you described there, so you don't have to worry about the data structure every time you enter a new dataset.
Look at a NoSQL database like MongoDB where you can store the JSON and then call it on the column names, which means you don't have to create strict columns and insert shorter / longer records if necessary - it's more flexible than SQL databases.
source to share
Yes, it usually saves as JSON.
$array_to_storage = ["phone1" => 911, "phone2" => 112];
$sql = 'INSERT INTO table (data_column) VALUES ("' . json_encode($array_to_storage) . '")';
But please note that you cannot do a filter query for example WHERE phone2 = 112
. But it is efficient for storing some data that does not need this feature.
source to share