CODEWITHSUNDEEP
phpmysqlphpmyadminmultiple-insert
Shahriar Kabir
Shahriar Kabir

Reputation: 284

Insert multiple data at once

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?

Upvotes: 0

Views: 1802

Answers (8)

Oscar P&#233;rez
Oscar P&#233;rez

Reputation: 4397

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 []

Upvotes: 1

Md. Maruf Hossain
Md. Maruf Hossain

Reputation: 922

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++;
}

Upvotes: 1

Devesh
Devesh

Reputation: 85

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

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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.

Upvotes: 1

Mj1992
Mj1992

Reputation: 3504

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.

Upvotes: 2

Ravi
Ravi

Reputation: 2086

$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;

Upvotes: 1

Mihai Matei
Mihai Matei

Reputation: 24276

$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);
}

Upvotes: 1

Fabian Schmengler
Fabian Schmengler

Reputation: 24576

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.

Upvotes: 3

Related Questions