3

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.

Improve this question
2
  • 1
    Do not do this. Always keep the values separate. See Normalization Commented Apr 15, 2017 at 15:46
  • 2
    you should not save multiple numbers to the same column. either have more columns or a "sub"-table. (or another database that is designed that way [MongoDB f.e.]) Commented Apr 15, 2017 at 15:47

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is something I also had in mind, create a separate table and store Phone numbers binding Contact ID from Contact table. My current SQL stores Landline, Mobile and Fax number in Contacts table, with Extension options for Landline and Mobile, to be honest I think this is sufficient but I'm unsure.
2

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);
Improve this answer

2 Comments

Approach #1 seems more usable, as I can execute search query rather easily and simply pull needed data objects instead of pulling all data objects while I'm only looking for say Fax #. The problem with JSON in my case would be caused by the fact that I plan to keep Contact information Encrypted, as such I don't believe it would play nicely with JSON-P.
I think you are right, it would add a layer of unnecessary complication. Approach #1 is the cleanest anyway.
1

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.

Improve this answer

1 Comment

I see, so it's either a new DB platform or dealing with JSON which brings up certain limitations, especially if trying to run a search query with PHP. I guess I will stick with landline + mobile columns in Contact tables as I also plan to add AES encryption to the MySQL data, which will make it a true nightmare when working with JSON. The reason for my question is because I seen some forum softwares and crm softwares store data as {1: something, 2: something, 3: something} in MySQL.
1

Yes it usualy saving as JSON.

$array_to_storage = ["phone1" => 911, "phone2" => 112];
$sql = 'INSERT INTO table (data_column) VALUES ("' . json_encode($array_to_storage) . '")';

But notice in that way you can't do filter query like WHERE phone2 = 112. But this effective to store some data that don't need this feature.

Improve this answer

4 Comments

Technically you can't query it in a normal way, but you can pre-load all Phone numbers into the User end as JSON and have JS filter truth needed results, but this would become a hassle if you have many clients and the JSON dataset would be massively large.
@0111010001110000 in current project I use that approach to save object state data and for query use object id. Some of object state is saved in separate columns to filter it in query.
But what about if the SQL data is AES-256 encrypted? This is something I'm concerned about, because it would mean in JSON you would need to decrypt large chunk of data, vs. decrypting the data you are only looking for. Plus I don't know how JSON would behave with encrypted data and if there is a possibility of data corruption, because as you know you can't exactly fix encrypted data that's been corrupted.
In current project it was storage as BLOB type in SQL table. Similar php array saved successfully. With AES-256 I don't famous, sorry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.