SQL syntax for database insert of PHP multidimensional array values

Question

Trying something new (to me) here.

First I build a multidimensional array using some form post values. Let's assume numdays here equals 2:

$multieventarr = Array();

    for($i=0; $i<$numdays; $i++){
            $values = Array();

            $multieventdate = clean($_POST['eventDate_'.$i.'']);
            $multistarttime = clean($_POST['startTime_'.$i.'']);
            $multiendtime = clean($_POST['endTime_'.$i.'']);
            $multistarttime24 = date("H:i", strtotime($multistarttime));
            $multiendtime24 = date("H:i", strtotime($multiendtime));

                array_push($values, $multieventdate);
                array_push($values, $multistarttime24);
                array_push($values, $multiendtime24);
                array_push($multieventarr, $values);

        }

From this I get the following, which I dump just prior to attempting the insert to make sure I have values:

array(2) { 

[0]=> array(3) { 
    [0]=> string(10) "2018/08/30" 
    [1]=> string(5) "00:00" 
    [2]=> string(5) "01:00" 
} 

[1]=> array(3) { 
    [0]=> string(10) "2018/08/31" 
    [1]=> string(5) "01:00" 
    [2]=> string(5) "02:00" 
} 

}

Then I try to insert the values as follows:

for($i=0; $i<$numdays; $i++){           
    $qry = "INSERT INTO cae_event_dates(eventid, eventdate, starttime, endtime)VALUES('$eventid', '{$multieventarr[$i][0]}', '{$multieventarr[$i][1]}', '{$multieventarr[$i][2]}')";
    $conn->exec($qry);
}

Thanks to Nigel for the original suggestion to add curly braces around the array vars in the VALUES section of the insert statement. This worked for me, as the screen capture shows.

I didn't change anything else, so not sure how or why the database accepts what I am giving it without performing some of the formatting suggestions mentioned here, but it worked.

Cheers!

Hi your date and time doesn't respect the format the date format is 0000-00-00 not 0000/00/00 and in time you doesn't have put the seconds — Inazo, Aug 29 '18 at 13:13
You would be better off using prepared statements, but for a start - the way you include the arrays in your string will not work properly - try `'{$multieventarr[$i][0]}'` — Nigel Ren, Aug 29 '18 at 13:15
Also, it's generally a bad idea to execute a query inside a loop. Instead, build the query inside the loop, and then execute it, once, right at the end. — Strawberry, Aug 29 '18 at 13:33
Possible duplicate of [Curly braces in string in PHP](https://stackoverflow.com/questions/2596837/curly-braces-in-string-in-php) — mickmackusa, Aug 29 '18 at 13:58
Maybe your start and end times should just be full date/time stamps. What if an event starts at 11:30pm and goes to 1am. — James, Aug 29 '18 at 14:20
My point was that start and end are on 2 different dates not just times on the same date. — James, Aug 29 '18 at 15:19

mickmackusa · Answer 1 · 2018-08-29T13:50:29.643

Because your database table is expecting Date type values (Y-m-d) and Time type values (H:i:s) you need to prepare your posted values before they go to the query.

*note, all of those temporary variables and pushing can be removed to simplify your snippet (unless you like the readability of your way).

Untested Code:

for($i=0; $i<$numdays; $i++){
    $rows[] = [
        str_replace('/', '-', clean($_POST['eventDate_'.$i])),
        date("H:i:s", strtotime(clean($_POST['startTime_'.$i]))),
        date("H:i:s", strtotime(clean($_POST['endTime_'.$i])))
    ];
}

Then you can iterate $row to generate your queries.

If you want to follow Strawberry's good advice, you can build up the full collection of data as a single string for your VALUES clause.

for($i=0; $i<$numdays; $i++){
    $rows[] = "('" . $event_id . "','" .
                     str_replace('/', '-', clean($_POST['eventDate_'.$i])) . "','" .
                     date("H:i:s", strtotime(clean($_POST['startTime_'.$i]))) . "','" .
                     date("H:i:s", strtotime(clean($_POST['endTime_'.$i]))) . "')";
}                 
$values = implode(',', $rows);

Then the single query execution:

$conn->exec("INSERT INTO cae_event_dates (eventid, eventdate, starttime, endtime) VALUES $values");

score 0 · Answer 2 · answered Aug 29 '18 at 13:38

Edit what you have to this:

for($i=0; $i<$numdays; $i++){
    $values = Array();

    $multieventdate = clean($_POST['eventDate_'.$i.'']);
    $convertedDate = date('Y-m-d', strtotime($multieventdate)); //The date has to be converted to the format that your database is expecting.
    $multistarttime = clean($_POST['startTime_'.$i.'']);
    $multiendtime = clean($_POST['endTime_'.$i.'']);
    $multistarttime24 = date("H:i:s", strtotime($multistarttime)); //The time has to be converted to the format that your database is expecting.
    $multiendtime24 = date("H:i:s", strtotime($multiendtime));

    array_push($values, $convertedDate);
    array_push($values, $multistarttime24);
    array_push($values, $multiendtime24);
    array_push($multieventarr, $values);
}

Edit your query too:

$qry = "INSERT INTO cae_event_dates(eventid, eventdate, starttime, endtime)VALUES('$eventid', '{$multieventarr[$i][0]}', '{$multieventarr[$i][1]}', '{$multieventarr[$i][2]}')";  //Array inclusion in queries could be a little tricky, so wrap it using curly braces.

Syno · Answer 3 · 2018-08-29T13:29:32.603

-1

As you see, your var_dump tells your that your date and your time is a string. You have to format your date and your time to a date datatype, so the database knows what it is.

Take a look at dateformat in the PHP doc to format the date before inserting it to your db, like so:

$insertableDate = date("Y-m-d", $yourDate);
$insertableTime = date("h-i-s", $yourTime);

edited Aug 29 '18 at 13:29

answered Aug 29 '18 at 13:16

Syno

1,056
10
25

It's not a hint in my opinion, it's a solution. @mickmackusa – Syno Aug 29 '18 at 13:19
As most of the SO users.. But at least the user knows, what's the problem of the code :) – Syno Aug 29 '18 at 13:20

score -1 · Answer 4 · answered Aug 29 '18 at 13:30

-1

It really depends on what is the type of this column in your SQL database.

DATE - Y-m-d
DATETIME - Y-m-d H:i:s
TIMESTAMP - Y-m-d H:i:s
YEAR - Y

Then you can loop your dates before inserting them in database and convert them to correct format:

$timestamp = strtotime($old_date);
$mydate = date('Y-m-d H:i:s', $timestamp);

answered Aug 29 '18 at 13:30

Janis Purins

126
7

The eventdate column Type in the database is DATE. – midasxl Aug 29 '18 at 13:37

SQL syntax for database insert of PHP multidimensional array values

4 Answers4