By kurtfoster on

I have created a new module for myself which has two data base tables, one of which is a child table with a 1 to many relationship to its parent. When I save, I need to write 1 new row to the parent table and 1 or many rows to the child table. The optimum solution would be to do it in a transaction so if the insert to the chlid table doesn't work the insert in the parent rolls back, however I can manage that through code if required.

My main issue is that I would like to insert several rows at a time in one database call rather than creating a loop and running a drupal_write_record or db_query insert each time, is that possible? I can do it through code by creating a SQL statement similar to this, however this won't prevent a sql injection, the insert in my module comes from a user input form not from another database table.

Has anyone got any suggestions for me, I have been looking through the forums and generally on the web but can't find anything useful.

TIA

⋅ Categories: Drupal 6.x

Comments

jaypan’s picture

jaypan
Location Victoria, BC
commented 

$sql = 'INSERT INTO {table} (colA, colB, colC) VALUES ';
$sql2 = '';
$variables = array();
foreach($something as $item)
{
  if(strlen($sql2))
  {
    $sql2 .= ', ';
  }
  $sql2 .= '(%d, %d, %d');
  $variables[] = $item['col1'];
  $variables[] = $item['col2'];
  $variables[] = $item['col3'];
}

db_query($sql . $sql2, $variables);

Contact me to contract me for D7 -> D10/11 migrations.

kurtfoster’s picture

kurtfoster commented

Thats incredible, I only posted my question 15 minutes ago. Thanks.