INSERT multiple select arrays with prepared statements

Question

My DB contains a table called 'opening_days_hours':

CREATE TABLE IF NOT EXISTS opening_days_hours (
        ID_OpeningHours bigint(20) NOT NULL AUTO_INCREMENT,
        ID_Address bigint(20) NOT NULL,
        opening   time NOT NULL,
        closing   time NOT NULL,
        day       int(11) NOT NULL DEFAULT 0,
        INDEX (ID_Address),
        PRIMARY KEY (ID_OpeningHours),
        CONSTRAINT FK_Addresses_OpeningHours
        FOREIGN KEY (ID_Address) REFERENCES addresses(ID_Address) 
        ON DELETE CASCADE) ENGINE=InnoDB

Each day contains one and only one 'opening' and 'closing' time.

The 'day' column value is obtained from a 'checkbox' value as

<input type="checkbox" id="sun" name="days[]" "<?php if (isset($days) && $days=="days") echo "checked"; ?>" value="1">
...same for mon, tue, wed, etc.

whereas the times are passed by the user via a 'select' statement (dropdown).

Everything works fine if 'opening' and 'closing' are scalars (1 value) and 'day' is an array defined as 'days[]'. For this, I use a prepared statement as:

$q = "INSERT INTO opening_days_hours
                    day,
                    opening,
                    closing
                    ID_Address
                    ) VALUES (?, ?, ?, ?)";

  $days = array();
  if(isset($_POST['days'])) {
     $days = $_POST['days'];
  }

  mysqli_stmt_bind_param($stmt, 'issi',
                        $days,
                        $opening,
                        $closing,
                        $ID_Address
                        );

   foreach ($days as $one) {
      mysqli_stmt_execute($stmt);
   }

With this code I can store one day per row in the column called 'day' and and the values of $opening and $closing being the same for every day.

HOWEVER, when 'opening' and 'closing' are themselves arrays, just like 'day', I obtained a mysqli_stmt_execute error ('Array to string conversion') and nothing is hence stored to the DB.

I did a lot of tests with different constructions of the code. I diagnosed the problem being that I cannot pass mysqli_stm_execute($stmt) more than one array. I ran out of options on how to solve this problem.

All the solutions I found so far store many values in the same row, but I don't want that. I actually need to store one opening time and one closing time per each day, and store each day in its own row.

I hope the issue is clear.

score 0 · Answer 1 · edited May 23 '17 at 11:58

SOLVED:

I had to loop the binding:

  for ($i = 0; $i < count($days); ++$i)
  {
       mysqli_stmt_bind_param($stmt, "issi",
                               $days[$i],
                               $opening[$i],
                               $closing[$i],
                               $ID_Address
                          );

  mysqli_stmt_execute($stmt);
}

as explained in this previous stackoverflow explanation

INSERT multiple select arrays with prepared statements

1 Answers1