CODEWITHSUNDEEP
phpmysqlarraysforeach
user3192581
user3192581

Reputation: 3

php foreach with sql

I have an array called "questions" that I am trying to INSERT into mysql with foreach. Array looks like this:

    Array
    (
    [0] => Array
        (
        [booking_id] => 936
        [question_id] => a
        [value] => 4
        )

    [1] => Array
        (
        [booking_id] => 936
        [question_id] => b
        [value] => 3
        )

    [2] => Array
        (
        [booking_id] => 936
        [question_id] => c
        [value] => 2
        )

    [3] => Array
        (
        [booking_id] => 936
        [question_id] => d
        [value] => 1
        )
     )

FOREACH looks like this:

$sql = array(); 
foreach( $_POST['questions'] as $row ) {

    $sql[] = '("'.$row['booking_id'].'", "'.$row['question_id'].'", '.$row['value'].')';
}

mysql_query('INSERT INTO table (booking_id, question_id, value) VALUES '.implode(',', $sql));

The foreach simply inserts the first item in the array into the table and doesn't loop through the entire array.

Any ideas where I am going wrong?

Upvotes: 0

Views: 123

Answers (3)

msg7086
msg7086

Reputation: 461

I've actually duplicated the code and got it run on an actual MySQL server perfectly, no mistakes in the code as far as I can see.

The only problem I can guess is a unique index that prevents from adding multiple records under the same booking_id.

Is there a primary / unique index on the booking_id field that prevents from adding multiple records?

Upvotes: 2

Marc
Marc

Reputation: 139

// $sql query value to get data in DB
$branches= array();
$sql = array();
while ($row = mysql_fetch_array($sql))
{
  array_push($question, $row);

}
 // this part to loop data fetch in sql
   $branch_type = '';
   foreach ($question as $NBU) {

    $sql[] = '("'.$row['booking_id'].'", "'.$row['question_id'].'", '.$row['value'].')';

    }

   mysql_query('INSERT INTO table (booking_id, question_id, value) VALUES '.implode(',', $sql));

dont use $_POST['questions'] because foreach only read array data.

Upvotes: -2

dachi
dachi

Reputation: 1602

It's already time to use PDO or MySQLi with PHP. This is a working example. You could replace $arr with $_POST and refactor a bit to suit your needs.

$mysqli = new mysqli('localhost', 'root', '', 'dachi');

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$arr = array(
  array(
    'booking_id' => 936,
    'question_id' => 15,
    'value' => 4,
  ),
  array(
    'booking_id' => 936,
    'question_id' => 15,
    'value' => 3,
  ),
);

foreach ($arr as $rec) {
  $stmt = $mysqli->prepare("INSERT INTO `test` (`booking_id`,`question_id`,`value`) VALUES (?,?,?)");
  // You will probably need to change 'iii'
  // i = integer
  // s = string
  // So you might have something like isi if you need like that or sss
  $stmt->bind_param('iii', $rec['booking_id'], $rec['question_id'], $rec['value']);
  $d = $stmt->execute();
  $stmt->close();
}

Upvotes: 2

Related Questions