PHP SQL: Dynamically Creating and Deleting Columns in Database

Question

I'm creating a carousel/image slider plugin for WordPress and I've hit a wall. There's going to be an indefinite amount of user input and I need to know how to handle this.

I currently have six static inputs: transition_time, loop_carousel, stop_on_hover, reverse_order, navigation_arrows, and show_pagination and the variable amount of info will come from the images the user wants to use. So this could be anywhere from zero to infinite.

I want to be able to create/delete X amount of columns in the DB.

So starting out there will be zero images, meaning six columns. If a user adds two images I want to have eight columns, two created. If the user deletes them then I want to go back to my original six.

I'm guessing this is possible but how and is this a good idea or should I just have a set amount of images?

You should look into entity value attribute database structure, not how to dynamically create and remove columns. — Lawrence Cherone
– Lawrence Cherone, Commented Mar 4, 2018 at 3:15
I'd be careful with using EAV. A lot can go wrong. Read these: one, two. cc @LawrenceCherone — ishegg
– ishegg, Commented Mar 4, 2018 at 3:24
I don't believe everything I read, if used right it works fine. How else would you have many items with many properties with many values like, for example, a custom forms, with dynamically created selects, inputs, radio buttons and inputs with whom many people fill in the same form? I know what most would do is json encode/serialise it (wordpress).. — Lawrence Cherone
– Lawrence Cherone, Commented Mar 4, 2018 at 3:28
@LawrenceCherone Right, I agree. I had to research this a lot for a client that wanted to have flexibility in an entity's fields, and while EAV looked attractive, it would've brought some problems down the road. It ended up being the right decision. Like you said, if used right it works fine. Just wanted input from someone who knows about it. Cheers. — ishegg
– ishegg, Commented Mar 4, 2018 at 5:17

RandomSeed · Accepted Answer · 2018-03-04 10:44:06Z

2

You Are Doing It Wrong™. Changing a table definition should be an exceptional event.

Use two tables, one to model the Carousel, one to store image information, then link them.

Table Carousel:

id
[more fields here]

Table Image:

id
carousel_id (reference to the containing Carrousel)
[more fields]

answered Mar 4, 2018 at 10:44

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

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

2 Comments

RandomSeed Over a year ago

Keywords for your research: "one-to-many relationship", "many-to-many relationship", "foreign key", "database normalization"

Brandon Benefield Over a year ago

This sounds like the way I should go. I wanted to avoid creating more than one table but what can you do huh?

Collectives™ on Stack Overflow

PHP SQL: Dynamically Creating and Deleting Columns in Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related