how to concatenate sql queries

Question

i am trying to concatenate sql queries and run later after loop. how is that possible? this is my vision:

for($i=1;$i<=10;$i++){
   $item_.$i = "value_".$i;
   sql = sql . " insert into table (`item`) values ('$item_'.$i.'')";
   // this should be but an array
}

and save into db:

for($j=0;$j<sqlarray.length;$j++){
   $sql_done = mysql_query($sqlarray[$j]);
}

i didnot try anything yet, because the database is big and i am afraid of destroying something important with my code..

thanks a lot

You can debug by using `echo` on your sql strings instead of `mysql_query` - that way you can check your inserts look sensible first, without actually executing them. — Raad, Feb 13 '13 at 10:54
mysql_query does not allow multiple queries, but [anyway you should not use the `mysql_` functions anymore.](http://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli) — Fabian Schmengler, Feb 13 '13 at 10:55
@fab, i am not inserting multiple queries with ``mysql_query``, i am using ``mysql_query`` multiple times. this is okay. — doniyor, Feb 13 '13 at 10:56
I see. Then why do you want to concatenate them instead putting them into an array? — Fabian Schmengler, Feb 13 '13 at 10:57

Sébastien Barbieri · Answer 1 · 2013-02-13T16:06:34.110

Use mysqli and bindings

see http://www.php.net/manual/en/mysqli.prepare.php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
// define your query
$query = "INSERT INTO tablename (column1,column2) VALUES (:col1,:col2)";
if ($stmt = $mysqli->prepare($query)) {
  // loop of insert
  for($i=0;$i<10;$i++){
    $stmt->bind_param("col1", $i);
    $stmt->bind_param("col2", 'test'.$i);
    $stmt->execute();
  }
  $stmt->close();
}else{
  throw new Exception("unable to prepare query");
}
$mysqli->close();

Binding will avoid a lot of security issue, no one should use something else then binding ever.

Even better put everything in a transaction and in case of error your database remains unchanged.

see: http://www.php.net/manual/en/mysqli.commit.php for more info

and here is a proposal with commit or rollback

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
  throw new Exception("Unable to connect");
}else{
  try{
    $mysqli->autocommit(FALSE);
    // define your query
    $query = "INSERT INTO tablename (column1,column2) VALUES (:col1,:col2)";
    if ($stmt = $mysqli->prepare($query)) {
      // loop of insert
      for($i=0;$i<10;$i++){
        $stmt->bind_param("col1", $i);
        $stmt->bind_param("col2", 'test'.$i);
        $stmt->execute();
      }
      $stmt->close();
    }else{
      throw new Exception("unable to prepare query");
    }
    $mysqli->commit();
  }catch(Exception $e){
    $mysqli->rollback();
  }
  $mysqli->close();
}

I did not try it but we should be near a good (best practice?) solution.

I hope this could help you.

+1 for good practice, however I believe the OP wants to use 10 values for 10 fields for each insert (which is where the looping comes in), as opposed to insert 10 records. — Raad, Feb 13 '13 at 11:20

Rajasekar PHP · Accepted Answer · 2013-02-13T11:57:30.687

1

For insert query you can write code like below:

$sql .= " insert into table (`item`) values ";
for($i=1;$i<=10;$i++){
   $item_.$i = "value_".$i;
   $sql = $sql . " ('$item_'.$i.''),";

}
mysqli_query( substr($sql ,0,-1) );

The above will concatenate all the insert data in a single string and execute at once.

edited Feb 13 '13 at 11:57

answered Feb 13 '13 at 10:55

Rajasekar PHP

611
2
9
16

there's a typo: `$sql = sql` should be `$sql = $sql` – Raad Feb 13 '13 at 11:01
why is that ``substr``? for removing the last ``,`` ? – doniyor Feb 13 '13 at 11:06
substr is to remove the ,(comma) from the last concatenated string – Rajasekar PHP Feb 13 '13 at 11:54

score 0 · Answer 3 · answered Feb 13 '13 at 10:57

0

I hope you were looking for this

$query = "insert into table_name values";
for($i=0;$i<4;$i++) {
    $data1 = "test_".$i;
    $data2 = "new_".$i;
    $query .= "('','$data1','$data2'),";
}
$query = substr($query,0,-1);

echo $query;

Let me know

answered Feb 13 '13 at 10:57

Roger

1,693
1
18
34

score -1 · Answer 4 · answered Feb 13 '13 at 10:50

-1

try below code

 $sql="":
 for($i=1;$i<=10;$i++)
{
 $item_.$i = "value_".$i;
 $sql.=" insert into table (`item`) values ('$item_'.$i.'')";

  // this should be but an array
 }

 mysql_query($sql);

answered Feb 13 '13 at 10:50

Ripa Saha

2,532
6
27
51

``$sql`` is array? are you sure? – doniyor Feb 13 '13 at 10:53
@doniyor no. $sql is not array. it is an variable. which contains all insert query. try above code. it'll work. I've also applied it in some of my project. – Ripa Saha Feb 13 '13 at 10:58
i dont think that your code will work. because you have multiple ``insert into``'s in the statement – doniyor Feb 13 '13 at 11:00
yes. just try for once in phpmyadmin. and let me know please. – Ripa Saha Feb 13 '13 at 11:01

how to concatenate sql queries

4 Answers4