CODEWITHSUNDEEP
phpmysql
Matthew
Matthew

Reputation: 309

Is it safe to run my queries from an array?

I'm running a loop to collect the data from an XML file, after the 2000+ lines are done there is some validation rules that run afterwards. If it is safe I'd like to store the MySQL data, in the before mentioned loops I am wanting to store all the MySQL queries to an array (that being almost 2000 queries) and run them in a loop after validation rules have finished.

Is this a bad idea? If so, what would be the best idea behind storing the query and running it later?

To give you an idea of what I'm talking about if you didn't understand:

foreach($xml->object as $control) {
// Stores relative xml data here
}

if(1=1) // Validation rules run

for(...) { // For Loop for my queries (2000 odd loop)

Mysql_Query(..) 
}

Upvotes: 0

Views: 75

Answers (3)

O. Jones
O. Jones

Reputation: 108676

There's nothing wrong with this approach, except for the fact that it uses more RAM than would be used by progressively parsing and posting the XML data records to the database. If the server running the php belongs to you that's no problem. If you're on one of those cheap USD5 per month shared hosting plans, it's possible you'll run into memory or execution time limits.

You mentioned "safety." Are you concerned that you will have to back out all the database updates if any one of them fails? If that's the case, do two things:

  • Don't use the MyISAM access method for your table.

  • Use a transaction, and when you've posted all your data base changes, commit it.

This transactional approach is good, because if your posting process fails for any reason, you can roll back the transaction and get back to your starting point.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

What you could do is use a prepared statement for a library that supports it (PDO or mysqli). In PDO:

$stmt = $pdo->prepare("INSERT INTO t1 VALUES (:f1, :f2, :f3)");
$stmt->bindParam(":f1", $f1);
$stmt->bindParam(":f2", $f2);
$stmt->bindParam(":f3", $f3);

foreach ($xml->obj as $control) {
    // create $array
}

if ($valid) {
    foreach ($array as $control) {
        $f1 = $control['f1'];
        $f2 = $control['f2'];
        $f3 = $control['f3'];
        $stmt->execute();
    }
}

Upvotes: 2

Lorenz
Lorenz

Reputation: 2259

To insert a lot of date into a MySQL database I would start a transaction (http://php.net/manual/en/mysqli.begin-transaction.php), and then create a prepared statement and just loop through all the items and validate them straight away and execute the prepared statement. Afterwards just mark the transaction as successful and end it. This is the fastest approach. The use of prepared statements also prevents SQL Injection.

Upvotes: 2

Related Questions