loop on inserting data in mysql

Question

i want to insert data through loop in mysql table but it insert only a time when loop execute where i want to insert 500 Times the same data into my table below are the codes thanks in advance

enter code here



<html>
<head>
</head>
<body>
<div>
<?php

$con=mysqli_connect("localhost","root","khan","first");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

for($i=1;$i<500;$i++)
{
 $sql="INSERT INTO bio(name, fathername , address) VALUES('khan','khan','khan')";  
 echo "Number " . $i . "<br>";
}

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }

mysqli_close($con);
?>
</div>
</body>
</html>

I suggest doing a bulk insert instead of 500 separate queries. — MrCode, Jun 17 '13 at 10:06
I really dont think this question earns downvotes. He has a problem he cannot figure out so we should help him. That's why there's stackoverflow. Not just for questions no-one knows the answer to. — Tikkes, Jun 17 '13 at 10:14

score 0 · Answer 1 · edited May 23 '17 at 11:51

Include mysqli_query inside of for scope

$con=mysqli_connect("localhost","root","khan","first"); 
// Check connection 
if (mysqli_connect_errno()) { 
    echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
} 

for($i=1;$i<500;$i++) { 
    $mysqli_query ="INSERT INTO bio(name, fathername , address) VALUES('khan','khan','khan')";
    echo "Number " . $i . "<br>"; 
    // <<-- you always leave a closing bracket here 
    if (!mysqli_query($con,$sql)) { 
        die('Error: ' . mysqli_error($con)); 
    } 
} 
mysqli_close($con);

Now you can do that on db side without any loops with the query

INSERT INTO bio(name, fathername , address)
SELECT 'khan','khan','khan'
  FROM
(
select a.N + b.N * 10 + c.N * 100 + 1 n
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
) t
WHERE t.n < 501

See more on populating a table with fake data https://stackoverflow.com/a/17139749/1920232

thanks bro , u mean like this but it do the same i dont know why $mysqli_query ="INSERT INTO bio(name, fathername , address) VALUES('khan','khan','khan')"; — ismail khan, Jun 17 '13 at 10:09
@ismailkhan Please copy code exactly. In what you've just posted in comments again `mysqli_query` is out of loop. You've left extra curly bracket after `echo`. Remove it. — peterm, Jun 17 '13 at 10:13
"; } if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } } mysqli_close($con); ?> — ismail khan, Jun 17 '13 at 10:15
You did it again. Pay attention to opening and closing brackets. See updated answer. — peterm, Jun 17 '13 at 10:24

Tikkes · Accepted Answer · 2013-06-17T10:13:38.560

This is because you insert only after the loop

Try this:

for($i=1;$i<500;$i++)
{
 $sql="INSERT INTO bio(name, fathername , address) VALUES('khan','khan','khan')";  
 echo "Number " . $i . "<br>";

 if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
}

As you can see, now the mysqli_query is inside the loop and so it will execute 500 times and not just one (notice the { } positioning.

Also try not to use die(); Use proper error handling instead of this.

for instance:

for($i = 1; $i < 500; $i++) {
//INSERT AND STUFF
if(!mysqli_query($con, $sql)) {
echo "Something went wrong. Try again.";
break;
}
}

of course you might want to return what has gone wrong. So the actual error that happened in your SQL.

Look at the Mysqli documentation for this.

If you put this in a function, you dont need to use break;. Instead you can use the return "something"; statement to return the error and quit the for-loop.

Now you will end up with:

function insertFunction() {
   for(.....
       //DO INSERT
       if(!mysqli_query(...
            return "Errormessage: "+ $mysqli->error;

}

in addition you might want to look at prepared statements for your insert and how to make sure you only do 1 insert with 500 records instead of querying the database 500 times.

This might get you started on prepared statements

loop on inserting data in mysql

2 Answers2