What would be a proper way to store multiple values in the same SQL column with help of PHP.
For example, in a system that stores Contact/Client details and permits you to add more then 1 phone number. For example you may save 2 mobile numbers, 1 office number and couple of other numbers for the very same client.
But I don't quite understand how you would go about saving this data in a single column vs creating a column for Home #, Mobile #, Work # and ect.
In my understanding it may be saved like a JSON format, but I may be wrong.
Thank you.
If you wanted to store multiple phone numbers for a given contact, you should do so via multiple records, not multiple values within a given record. For example, you could have a Numbers table looking 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 type column can record whether a phone be e.g. landline (1) or cellular (2). You can see that contact 1 has both landline and cell numbers, while 2 has only a landline. This Numbers table could be joined to a table for the contacts.
Well, if you for example have a dynamic number of phone numbers (like multiple mobile numbers) you could instead create a table just for phone numbers and add a relation to the client:
+-----------------------------------+
| phone_number |
+-----------+-----------+-----------+
| client_id | type | number |
+-----------+-----------+-----------+
| 5 | office #1 | 055768765 |
+-----------+-----------+-----------+
| 5 | mobile | 017884778 |
+-----------+-----------+-----------+
...
However if you really want to save all numbers in one column of the client, here is how you could do that:
# 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);
It's not possible in MySQL - unless you want to store JSON in a single column, such as contact_information and then storing all of the data you described in there so you don't have to worry about data structure every time you enter new data set.
Look at NoSQL database enginges such as MongoDB where you can save JSON and then call it by column names meaning you don't have to create strict columns and can insert shorter/longer entries as needed - it's more flexible than SQL databases.