Entering an undetermined amount of datasets into a database.

[Eggzorcist]

The program I'm developing depends on an undetermined datasets to be entered into a database. I was thinking of having a loop with PDO entering each set per loop. As you as well might notice this doesn't seem to be the most ecological method. My question is this correct? There won't be more than 10-20 datasets. What is another more ecological way to do this?

 

Thanks

[Adam]

Use prepared statements. Databases cache the execution plan of prepared statements for the length of the connection (the request, in the web world), so re-executing the same statement in a loop with different values is perfectly efficient.

[Eggzorcist]

Let me know if I got this right. I can actually prepare many statements put it all into an array and execute them all together?

[Pikachu2000]

You can insert multiple records in a table with one query with MySQL. Not sure about other RDBMSs.

 

INSERT INTO table (field1, field2, field3) VALUES ('set1val1', 'set1val2', 'set1val3'), ('set2val1', 'set2val2', 'set2val3'), ('set3val1', 'set3val2', 'set3val3')

[Eggzorcist]

You can insert multiple records in a table with one query with MySQL. Not sure about other RDBMSs.

 

INSERT INTO table (field1, field2, field3) VALUES ('set1val1', 'set1val2', 'set1val3'), ('set2val1', 'set2val2', 'set2val3'), ('set3val1', 'set3val2', 'set3val3')

 

That's good to know. However, I'd prefer staying in the PDO practice.

[Adam]

No, a prepared statement is essentially a string of SQL (a statement) with place holders for dynamic data. That's then sent to the database to be prepared, which is essentially the database working out how to process it. You can then bind data into the prepared statement and execute it. After you can bind different data into the statement and execute it again. Each time the statement is executed the database already has the execution plan cached, so it re-uses it, taking out a substantial amount of the processing required and speeding up the query. If you only execute it a few times you probably won't notice the difference, but for an "undetermined" number of inserts it's definitely the best approach. Each extension has it's own syntax but they're roughly similar.

[Eggzorcist]

Thanks Adam. Now my question is. How should I proceed about the statement. Should I loop the statement create so

 

$query("INSERT INTO items values". loop (value1, value2, value3, value4) (value2.1, value2.2, value2.3, value2.4); or should I just do

 

loop the entire PDO statement per dataset?

 

Last question.

[Adam]

Here's a rough example:

 

$sql = "
    insert into tableName (
        col1, col2
    )
    values (
        ?, ?
    )
";

$statement = $dbo->prepare($sql);

foreach ($datasets as $dataset) {
    $statement->execute(array(
        $dataset['col1'],
        $dataset['col2']
    ));
}

 

As you can see, the $statement object is re-used/re-executed for each dataset.