Insert multiple data at once

Question

Though there are some same questions here, still I want to post my own question as I think I made a solution, but it's not working. Here is the following code:

$i=1;
while ( $i<= 10) 
{
    $i++;
    $sql    =   "INSERT INTO job (Category, Title, Type) VALUES ('$_POST[cat]','$_POST[title]','$_POST[type]')";
}

Shouldn't it insert 10 data one by one? But it just inserts 1 data. Why is this happening?

`echo $sql;` and then copy it in phpmyadmin more than once, see what errors you get — Vlad Preda, Jan 22 '13 at 09:27
Yes I have a auto increment primary column named ID. But how could be it a problem? There is no SQL error show up. @WayneWhitty — Shahriar Kabir, Jan 22 '13 at 09:30

score 3 · Answer 1 · answered Jan 22 '13 at 09:28

3

You don't insert anything in this code, just assign a value to the string $sql (and overwrite it 10 times). I guess you execute the query after the while-loop.

answered Jan 22 '13 at 09:28

Fabian Schmengler

24,155
9
79
111

score 2 · Answer 2 · edited Jan 22 '13 at 09:33

2

perhaps you are calling mysqli_query($conn, $query) only once, put it inside the loop.

edited Jan 22 '13 at 09:33

Vlad Preda

9,780
7
36
63

answered Jan 22 '13 at 09:28

Devesh

85
9

score 2 · Answer 3 · answered Jan 22 '13 at 09:31

This is the way I do it to insert multiple results

$sql = "INSERT  INTO job(category,title,type) VALUES";
for($i=0;$i<sizeof($category);$i++)
{
   if($i= sizeof($category) - 1)
    $sql. = "(".$category[$i].",".$title[$i].",".$type[$i].");"; 
   else
    $sql. = "(".$category[$i].",".$title[$i].",".$type[$i]."),"; 
}
 $mysqli->query($sql);

Considering $category,$title,$type as arrays which you want to insert in a single query.

score 1 · Answer 4 · answered Jan 22 '13 at 09:30

$mysqli = new MySQLi("host","user","pass","db_name");

for($i = 0; $i < 10; $i++)
{
  $sql = "INSERT INTO job (Category, Title, Type)
          VALUES ('" . $mysqli->real_escape_string($_POST['cat']) . "','" . $mysqli->real_escape_string($_POST['title']) . "','" . $mysqli->real_escape_string($_POST['type']) . "')";
  $mysqli->query($sql);
}

score 1 · Answer 5 · answered Jan 22 '13 at 09:31

$i=1;
$addCount=0;
while ( $i<= 10) 
{
    $i++;
    $sql    =   "INSERT INTO job (Category, Title, Type) VALUES ('$_POST[cat]','$_POST[title]','$_POST[type]')";
    if(mysql_query($sql)){
        $addCount++;
    }
}

echo "Total number of records added: ".$addCount;

score 1 · Answer 6 · answered Jan 22 '13 at 09:31

You can do it like this. Instead of running 10 queries you can do it with single query

$i=1;

while ( $i<= 10) 
{
    $i++;
    $values[]       =   "('$_POST[cat]','$_POST[title]','$_POST[type]')";
}
$new_values =   implode(',',$values);
$sql    =   "INSERT INTO job (Category, Title, Type) VALUES $new_values";

And now you can use mysqli_query to run it.

Oscar Pérez · Accepted Answer · 2013-01-22T13:36:36.197

1

See that you're constructing exactly the same sql string every iteration.
Your code should be:

$i=1;
while ( $i<= 10)
{
    $i++;
    $sql = "INSERT INTO job (Category, Title, Type)
                 VALUES ('{$_POST[cat]}','{$_POST[title]}','{$_POST[type]}')";
    mysql_query($sql);
}

Pay attention to the []

edited Jan 22 '13 at 13:36

answered Jan 22 '13 at 09:33

Oscar Pérez

4,377
1
17
36

score 1 · Answer 8 · answered Jan 22 '13 at 10:43

1

You Can Do As Like Following...

 $i=1;
 while ( $i<= 10) {
   mysql_query("INSERT INTO job (Category, Title, Type) VALUES('".$_POST[cat]."','".$_POST[title]."','".$_POST[type]."')");
   $i++;
}

answered Jan 22 '13 at 10:43

Md. Maruf Hossain

912
9
11

Insert multiple data at once

8 Answers8