php foreach with sql

Question

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?

If booking_id is your primary key and cannot have duplicates and every record has id=936 then that's your problem. Try adding ` or die(mysql_error())` to the end of your mysql_query() statement. — user3141031
– user3141031, Commented Jan 14, 2014 at 3:52
spotted the problem - it's the primary key. obviously not meant to be. thanks. — user3192581
– user3192581, Commented Jan 14, 2014 at 4:00

dachi · Accepted Answer · 2014-01-14 03:53:47Z

2

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();
}

answered Jan 14, 2014 at 3:53

dachi

1,60211 silver badges15 bronze badges

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

5 Comments

user3192581 Over a year ago

does mysqli/PDO require an install on the server? just got back into all this for a new project and it seems like a bit has changed!

dachi Over a year ago

You have to install PDO php.net/manual/en/pdo.setup.php but the mysqli extension was introduced with PHP version 5.0.0.

dachi Over a year ago

Btw, your code is not secure. rumor that is "truish": Nowadays 'everyone' is using prepared statements for security

user3192581 Over a year ago

I know. I was going to run mysql_real_escape_string on the variables. Looks like this method is a bit outdated though.

dachi Over a year ago

mysql is deprecated as stated on php website. php.net/manual/en/function.mysql-real-escape-string.php You can see it in red.

msg7086 · Accepted Answer · 2014-01-14 04:05:32Z

2

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?

edited Jan 14, 2014 at 4:05

answered Jan 14, 2014 at 3:49

msg7086

4612 silver badges11 bronze badges

5 Comments

maackle Over a year ago

Why the downvotes? This seems like something worth looking into.

Ken Herbert Over a year ago

@maackle If someone has something to ask about the question, it should be a comment. If they are trying to answer the question, it should be worded as an answer.

user3192581 Over a year ago

@msg7086 - thanks. that is indeed the problem. many thanks. silly thing to miss.

msg7086 Over a year ago

@winterblood Thanks for the tip. I'm new to the community and not that good in constructing my words. It's really hard for me to ask for a clarification in the comment without answering the question itself.

Ken Herbert Over a year ago

@msg7086 Don't worry, we were all new once, and the StackOverflow way of doing things can be very particular sometimes.

Marc · Accepted Answer · 2014-01-14 03:54:24Z

-2

// $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.

answered Jan 14, 2014 at 3:54

Marc

1391 silver badge7 bronze badges

1 Comment

Marc Over a year ago

try to see if the variable is in array. the one you loop

Collectives™ on Stack Overflow

php foreach with sql

3 Answers 3

5 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related