Building a query in MySql with php

Question

I have the following loop to add several users to a database, however it only works when only one user is added into a form.

echo "The following names have been added to $cname<br><br>";
$query = "";
for ($i = 0; $i < count($fname); $i++) {
  $query = $query . "INSERT INTO Students (firstName, lastName, year, class, school, email) " 
  ." VALUES ('{$fname[$i]}','{$lname[$i]}','{$year}','{$cname}','{$sch}','{$uname[$i]}'); ";
  echo $fname[$i] . " " . $lname[$i] . " " . $uname[$i];
  }

if ($mysqli->query($query)) {
    $id = $mysqli->insert_id;
    error_log("Inserted {$firstname} as ID {$id}");
    return true;
} else {
    error_log("Problem inserting {$query}");
    return false;
}

Do I just need to add ($mysqli->query($query) to each iteration? Or just move the 'if' clause inside the loop? Or is there a better way.

Thank you.

what is your echo result? and how to you pump value to this php file? — user3454436
– user3454436, Commented Apr 2, 2014 at 6:35
echo is blank and no data is added to DB. 'if' returns false and page redirects to form. If only one name entered in form, it works without problem. — Russell
– Russell, Commented Apr 2, 2014 at 6:37

MSadura · Accepted Answer · 2014-04-02 06:48:36Z

1

At the moment your query is trying to send muliple sql queries at once and it fails. You should change the way you build query:

$query = "INSERT INTO Students (firstName, lastName, year, class, school, email) VALUES ";
for ($i = 0; $i < count($fname); $i++) {
  $query .= $i>0 ? ',':'';
  $query .="('{$fname[$i]}','{$lname[$i]}','{$year}','{$cname}','{$sch}','{$uname[$i]}') ";
  echo $fname[$i] . " " . $lname[$i] . " " . $uname[$i];
}

edited Apr 2, 2014 at 6:48

answered Apr 2, 2014 at 6:41

MSadura

1,04213 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Callidior Over a year ago

Better leave out the semicolon after each VALUES tuple.

MSadura Over a year ago

It doesn't really matter, as you will have to "if" it anyway - otherwise it will leave semicolon after last data set and fail.

kimbarcelona · Accepted Answer · 2014-04-02 06:38:29Z

0

Better put if clause in the loop. Have you tried it?

answered Apr 2, 2014 at 6:38

kimbarcelona

1,1462 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Building a query in MySql with php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related