How do you dynamically insert parameters into a mySQL database with php?

Question

I try to insert data into a mySQL table and i don't know yet how many parameters there will be.

So i need to find out a way how to insert parameters dynamically.

You can do it that way:

Insert into mytable (parameters) VALUES ('Ford;red;100kW;diesel;');
Insert into mytable (parameters) VALUES ('Ford;red;100kW;electric;40kWH');

So if you have to add a electric car, you need the kWH, which you didn't need for the petrol car.

Or you do it that way:

Insert into mytable(name, color, kW, engine) VALUES ('Ford', 'red', '100', 'diesel')

ALTER TABLE mytable ADD kWh VARCHAR( 255 ) after engine

Insert into mytable(name, color, kW, engine, kWh) VALUES ('Ford', 'red', '100', 'electric', '40')

In first case you have to hande all the data with string operations like explode, in second case you have to allow the user to add columns with php.

What is the better way? Or is there another way that is even better?

I don't see any similarity with the other question.

@JayBlanchard I was about to answer the question before you closed it. This question is absolutely not a duplicate of the question you linked and the selected answer doesn't answer the question... — Mouradif, Jan 26 '17 at 16:04
As i don't even understand the other answer, i guess that's true. — Roman, Jan 26 '17 at 16:14
@JayBlanchard Spent 45 mins to think how to make that question perfect. I give up, lol. — Roman, Jan 26 '17 at 16:19

score 0 · Answer 1 · answered Sep 02 '18 at 14:09

This is a verry dificult question. I hope i understood it correctly.

Disclaimer

This is a long post, it may contain errors. Feel free to correct those or ask for clarifiation. It also is (because of the nature of the question) somewhat oppinion based. I tried to balance all possibilities.

I assumed a object orriented approach to this, i.e. that object should be stored.

TL;DR: It might be best to not do this programmatically.

Evaluation

The first answer has the advantage, that you may split the text at runtime and dynamically create a new Object, which will not lead to a PDOException (or whatever you are using). This however also has it's disadvantages. It can lead to you using reflections a lot. Why? If you want to alter the Table at runtime, i have to asume you do not know what kind of Objects are to be expected. This leads to you creating those objects "on the fly". This would also suggest, that you should store the Objects name somehow.

The seconds answer raises a Question. How do you read from that Database?

If you dynamically read of that database (i.e. programmatically defining which columns you are going to need): How do you know, which columns to read from? What ensures you, that the columns will exist? You would have to check that the column you request exists. This may get messy real fast, because you would have to check for each column. And if it does exist, but is not set, what will be the default value for it?

If you statically read of that database: Why not design the database beforehand to hold the kWh column? It might be null at some point, but you could compensate for that, by ignoring them.

If you know the Object you want to use beforehand, design your Database to be able to hold it.

Another way to aproach this

Or is there another way that is even better?

You may be happy to use Relational Databases and abstruct those with a Data Access Object. Even tho this answer dipps deep into design aspects, you may go best with designing your application first. Go ahead an create a EER-Diagram, that represents your data-structure. You can have a generic car entity, that is extended by the patrol car and the electric car (and even a fusion car). There are plenty of tools out there, that help you create such a diagram and convert it into an DDL for the database of your choice.

Conclusion

To be concrete, if you realy have to alter the table at runtime, i would recommend going with the second approach and add a default value to it. However, based on the question you asked, i can't realy see you getting far with it. It would mean that you would have an unkown Object that you want to store.

If that is the case, why not create a new Table with the objects name, that holds the fields of the object as columns? That would allow you to have an acces like this (asuming a repository that stores said object):

$object = new TestObject();
repository->store($object);

Upon calling repository->store(), the repository will check if the database has a table called "TestObject" and, if not, create it. If the table was created, it than could proceed to alter it and add the columns. So, the following would use this:

$object = $repository->load("TestObject");

The Repository would now check for the column TestObject and may create a new TestObject at runtime like this:

function load($name) {
    $returnValue = new $name;
    // Set the fields based on the database entries.
}

It has the big advantage, that you would only have to check, whether or not the table exists and (if yes) create a new Object or (if not) throw an Exception.

Ofcourse this comes with more problems. Error handling is not done here (for example, what happens with namespaces and what happens, if there are more objects with the same name), to keep it simple. But this should bring the point accross.

Sorry for the long post, have a nice day :)

How do you dynamically insert parameters into a mySQL database with php?

1 Answers1

Disclaimer

Evaluation

Another way to aproach this

Conclusion