Jump to content
Larry Ullman's Book Forums

Total Number Of Rows Affected By An Insert Within A Loop


Recommended Posts

Hello,

 

When I use an INSERT query inside a loop, like this:

 

while ($genres_entree = mysqli_fetch_array($result2, MYSQLI_NUM))
{
  $query5 = "INSERT INTO referents_genders
     (id_referent, id_gramm_gender)
  VALUES
     ($id_referent, $genres_entree[0])";
  $result5 = @mysqli_query($bdd, $query5);
}

 

what PHP function should I use to know the total number of rows affected by $query5? If I use mysqli_affected_rows(), the result is always 1, since the counter is apparently set back to 0 at the end of every loop. So just now I'm getting the total number of affected rows with a SELECT query:

 

$query6 = "SELECT id_referent FROM referents_genders WHERE id_referent=$id_referent";
$result6 = @mysqli_query($bdd, $query6);
$nb_genres_referent = mysqli_num_rows($result6);

 

but I have a feeling I could be much more efficient!

 

With thanks for your help,

Link to comment
Share on other sites

$query5 will always create one row, so the result should always be 1 and mysqli_affected_rows() is the function you'll need to call. If you want to count how many times that query is executed (i.e., how many total rows were affected by the loop), create a new variable before the loop, with an initial value of 0. Then, within the loop, do

$affected += mysqli_affected_rows();

 

After the loop is done, $affected with reflect the total number.

Link to comment
Share on other sites

I think it will always report just one row affected because you are only inserting one row in the loop. You can count the total number of rows added by initializing a counter variable before you enter the while loop, and increment it by adding the results of mysqli_affected_rows() during each loop iteration. Then check the value of your counter variable after the while loop completes:

 

$affectedRows = 0;
while ($genres_entree = mysqli_fetch_array($result2, MYSQLI_NUM))
{
$query5 = "INSERT INTO referents_genders
   	(id_referent, id_gramm_gender)
VALUES
   	($id_referent, $genres_entree[0])";
$result5 = @mysqli_query($bdd, $query5);

$affectedRows += mysqli_affected_rows($bdd);
}

echo $affectedRows; // should be the total number of rows added

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...