Storing variables in mysql in for loops

Question

I am working on a project where I need to save some variables to a database and retrieve some of those variables in a class to recalculate the next batch of variables.

However I came to realise that the current project is running too slow and find out where the leak was: in storing those variables. (Insert statement in PHP)

Since the insert statement can get in a pretty big for loop, like this ($auctionobjects and numberofdays is filled in by the user, $hours == 24):

for($i = 0; $i< $numberofdays)  {
    for ($n = 0; $n < $hours)  {
        for ($k = 0; $k < $auctionobjects)  {
        $strSql = 'insert into results (vars) values ()'
        $blnOke = $objDatabase->query($strSql)
        }
    }
 }

The waiting time can get pretty long, which is not desirable as the simulation must be able to run for a pretty long time.

Now my idea was to get an insert statement as the following:

$strSql = 'insert into results (vars) values (), (),()'

But the variables that fill the columns in MySql change troughout the simulation. Since I don't know how big the $auctionobjects variable will be, I cannot get the right number of brackets I need to fill in.

What I could do is create a variable that creates the string needed for the amount of $auctionobjects like this:

$createSqlString  = '(vars)'
for($x = 0; $x< $auctionobjects; $x ++)  {
   $createSqlString .= ,(vars)
}

And then insert it in the Sql statement, but I am not sure if there is a more clean solution.

If (vars) in the last example are the actual values you want to insert, that seems like a pretty reasonable solution. Maybe start a new query after 100 values or so. — nils
– nils, Commented May 16, 2014 at 14:08

O. Jones · Accepted Answer · 2014-05-16 14:15:36Z

1

You might try something like this:

$createSqlString = `INSERT INTO results (var, var, var) VALUES ';
$vars = array();

for($x = 0; $x < $auctionobjects; $x ++)  {
    /* processing ... */
    $vars[] = "($val1, $val2, $val3)";
}
$createSqlString .= implode (',', $vars);

implode is pretty nice for these SQL-style comma separated lists.

If you wanted to be really careful about bind variables to prevent injection attacks, you could try this:

$createSqlString = `INSERT INTO results (var, var, var) VALUES ()';
$sqls = array();
$vals = array();

for($x = 0; $x < $auctionobjects; $x ++)  {
    /* processing ... */
    $sqls[] = '(?,?,?)';
    $vals[] = $val1;
    $vals[] = $val2;
    $vals[] = $val2;
}
$createSqlString .= implode (',', $vars);
/* then bind the $vals array to the statement and execute it */

answered May 16, 2014 at 14:15

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

1 Comment

Kevin Over a year ago

Think you misunderstood my question, but you are underlining the idea I had, so I am accepting your answer :).

Collectives™ on Stack Overflow

Storing variables in mysql in for loops

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related