PHP prepared insert statement from multidimensional array

Question

Updated code:

if (isset($_POST['submit'])) {

/* Create a prepared statement */

$query = "INSERT INTO log_dates(week_date, crew_chief, monday_crew) values(?,?,?)";

$stmt = mysqli_prepare($connection, $query);


  $returnedData = $_POST['data'];

  foreach($returnedData as $data) {
    $week_date = $data['week_date'];
    $crew_chief = $data['crew_chief'];
    $monday_crew = $data['monday_crew'];
    $stmt->execute();
  }


 mysqli_stmt_bind_param($stmt, 'sss', $week_date, $crew_chief, $monday_crew);

/* Execute it */
 mysqli_stmt_execute($stmt);


/* Close statement */
 mysqli_stmt_close($stmt);

 } // end if

Here's what my POST array looks like:

Array ( [0] => Array ( [week_date] => 2013-07-08 - 2013-07-14 ) [1] => Array ( [crew_chief] => Alan ) [2] => Array ( [monday_crew] => dd ) )

You are trying to concat a string with injected values, instead of passing php variables as bound values. And no, you can't bulk insert lists of them using mysql_prepare. — mario
– mario, Commented Jul 8, 2013 at 21:12
The above code still doesn't function correctly. Only the last variable is being inserted into the db. Any ideas? — user1040259
– user1040259, Commented Jul 9, 2013 at 4:55

Marc B · Accepted Answer · 2013-07-08 21:12:06Z

4

That's not how you bind parameters. You're slapping your multiple parameters into a SINGLE value. The bind call should be

mysqli_stmt_bind_param($stmt, 'ss', $foo, $bar);
                               ^^--two params
                                    ^^^^^^^^^^---two values


foreach($returnedData as $data) {
   $bar = $data['crew_chief'];
   $foo = $data['week_date'];
   $stmt->execute();
}

once the variables are bound, simply assigning new values to them will cause the next ->execute() call on the statement to pick up those new values.

answered Jul 8, 2013 at 21:12

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

8 Comments

Gumbo Over a year ago

You could also do mysqli_stmt_bind_param($stmt, 'ss', $data['crew_chief'], $data['week_date']);.

Marc B Over a year ago

True. but it's bad enough that PHP lets you use undefined variables in the bind call. Using an undefined array with undefined keys just makes me feel dirty.

user1040259 Over a year ago

@MarcB Thanks, you're the best :) Any reason why "2013-07-08 - 2013-07-14" isn't being inserted from my form post?

Marc B Over a year ago

depends on the field type, I suppose. if that's a date/datetime field, then you can only insert a SINGLE date into it. you're trying to stuff in a string.

user1040259 Over a year ago

@MarcB I might have something further wrong with my above code when using Marc's answer because for some reason the code is only inserting the last variable. In the example above, it's only inserting $bar. Any ideas?

|

Your Common Sense · Accepted Answer · 2013-07-09 07:21:59Z

I have an idea for you. You need to have an idea on what are you doing.

First of all you need to develop a pure SQL query that you want to run against SQL server. Without prepared statements, without mysqli, without PHP. A clean SQL.

SQL is your problem now, not API to send it to server. And then eventually, step by step, go further with developing a program you need. Here is a rough checklist:

First of all, a database have to be designed to store your data.
Once you've done with it, you need to make your mind on what query would serve the purpose of insert.
then write this query by hand and make sure it works in console
next step would be to determine what data you need for this query
then you have to verify the data you have and determine if it fits for the (4)
if not - you need to format your existing data to make it meet requirements from (4)
As soon as you have it, you may start writing a PHP program that does create your query dynamically, using string concatenation, and echo the result out.
then you have to test this dynamically built query in the console as if in (3)
if it works - replace variables in the query with placeholders and proceed with running this query using mysqli prepared statements, with one single set of data. Note that when using native prepared statements, a placeholder can represent only a single data literal alone.
having done with it, you may finally start your research on a problem of feeding a prepared statement with multiple values.

You may ask for help for the any stage from above but it's essential to follow the list. As it seems you are asking for the last item having not completed the first one.

Hope it helps.

Collectives™ on Stack Overflow

PHP prepared insert statement from multidimensional array

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related