Good evening, I wanted to know if we can create a table with an array type fields with phpMyAdmin , thank you

Member Avatar
diafol

You can store whatever you like in a blob or text-based data type, but the issue is retrieving data. Not sure what you're actually asking for though. If you have an array, you'd simply split it and pass the individual values to different fields. Equally, if you have a nested array, you'd split and possibly pass 'child' arrays (once split) into related tables.

Really not sure what advantage you'd get from storing as an array. Give us an idea of usage.

first of all I 'm sorry for my English may be I misspoke . I am currently developing an application with php that needs to show me a table like in the example below, I need to create a field that contains check boxes such as the case for the Access fields, that 's why I thought of the array type fields , so if you can explain me more clearly the relationship between the tables in this example, thank you
Click Here

Member Avatar
diafol

Sorry, I don't understand what you want. I looked at the link, but I see no connection with 'Access-like checkbox fields'.
Perhaps if you show a typical data row that you want to store.

this is exactly what i want to have jointure3.png

Member Avatar
diafol

AH ok, I thought you were referring to Microsoft Access.

Couple of ways that you can do this. The usual method would be:

USER

id (PK/int) | firstname (varchar) | lastname (varchar) | site_id (int)*

SITES

id (PK/int) | label (varchar)

ACCESS

id (PK/int) | label (varchar)

USER_ACCESS

user_id (int)* | access_id (int)*

  • = foreign key

So when you post an insert:

INSERT INTO users (firstname, lastname, site_id) VALUES (?,?,?)
[get last inserted id from the above]
INSERT INTO user_access (user_id, access_id) VALUES (?,?),(?,?)... (as many as you need to insert checked access_ids)

Update is a little trickier.

Another approach is to use bitwise operators - fine if you have less than 32 types of access. Any more and it won't work on 32-bit servers.

USER

id (PK/int) | firstname (varchar) | lastname (varchar) | site_id (int)* | access (int)

SITES

id (PK/int) | label (varchar)

ACCESS

id (PK/int) | label (varchar) | bvalue (int)

Here your access table would contain something like:

1 | Accounts | 1
2 | Desktop | 2
3 | Printer | 4
4 | Servers | 8
5 | VMs | 16
6 | Web-site | 32

SO when you have Accounts and Servers checked in the form, you get 1 + 8 = 9. Store the 9 in the access field of the users table. You no longer need the user_access table.

There are pros and cons of using both.
You can see a demo of the second method here: http://demos.diafol.org/bitwise-checkboxes.php