php mysql编写语句循环数组

Question

my problem is that I want to loop through an array and insert every entry of that array into another column of an mySQL table. To be honest, I'm not sure if that's the best way to design my database, but that's one way I could imagine, it works. If someone has a better idea of how to do it or a link for best practice or something, that would be awesome.

So what I want to do: I have a form where someone can register to offer a food delivery service. He can enter name etc. and up to 10 offers (limitation of the database table). These information should be insert into the table 'anbieter' into the fields 'angebot_0' , 'angebot_1' ... So what I did is:

if (isset($_POST['register_offer']) and isset($_POST['anbieter-email'])){

$name = $loc = $cat = $email = $password ="";
$angebot = array();
// fill all variables
$name = test_sql($_POST['anbieter-name']);
$email = test_sql($_POST['anbieter-email']);
$password = test_sql($_POST['anbieter-password']);
$loc = test_sql($_POST['anbieter-loc']);
$cat = test_sql($_POST['anbieter-cat']);
// fill $angebot with all given angebot[] entries
foreach($_POST['angebot'] as $ang) {
    $angebot[] = test_sql($ang);
}

if(!empty($name) and !empty($loc) and !empty($email) ){
    /* decrypt password */
    $password = password_hash($password, PASSWORD_BCRYPT, ["cost" => 12]);
    // insert name, email, password, location and category into database
    /* Prepared statement, stage 1: prepare */
  if (!($stmt = $conn->prepare("INSERT INTO anbieter (anbieter_name, anbieter_email, anbieter_password, anbieter_loc, anbieter_cat) VALUES (?, ?, ?, ?, ?)"))) {
     echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
/* Prepared statement, stage 2: bind and execute */
  if (!$stmt->bind_param('sssss', $name, $email, $password, $loc, $cat)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }
  if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }

$userid = $stmt->insert_id;
// safe all angebot[] entries to database - angebot[0]-> angebot_0
for($x=0; $x < count($angebot) ; $x++) {
    $upd = $conn->prepare("UPDATE anbieter SET angebot_".$x." = ? WHERE abieter_ID = ? ");
    $upd->bind_param('si', $angebot[$x], $userid);
    $upd->execute();
}

So when I do this, I get the error:

Fatal error: Call to a member function bind_param() on boolean in ...

It's a super bad way to do that by using $x to name different fields of the table, but that's the only way I could think of it works :/

I hope someone can help me here! :) Thanks a lot!

Answer 1

My suggestion instead on single record update query multiple times you can do it in a single query,

Eg:

$query = "UPDATE anbieter SET";
for ($x = 0; $x < count($angebot); $x++) {
    $query .= " angebot_" . $x . " = '" . $angebot[$x] . "', ";
}
echo $query .= " WHERE abieter_ID = " . $userid;