Date inserts 00

[sonnieboy]

<?php
  $dbhost = "localhost";
  $dbname = "mydb";
  $dbusername = "xxxx";
  $dbpassword = "mypass";

if ($_SERVER['REQUEST_METHOD'] == 'POST')
    {
    $pdo = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo->prepare("INSERT INTO xphias (pastorsname, ministriesname, xstartdate,clientname, url) VALUES (?,?,?,?,?)");

    for ($i = 0; $i < count($_POST['pastorsname']); $i++)
        {
        $stmt->execute(array(
       $pastorname   =       $_POST['pastorsname'][$i],
       $ministryname =       $_POST['ministriesname'][$i],
       $startdate =    "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['xstartdate'][$i])))."'",
       $clientsname =     $_POST['clientname'][$i],
       $video_url =     $_POST['url'][$i],

        ));
        }
        echo "Success!";
    }
?>

Greetings again,

 

The following code is fairly working except that date field is inserting 0000-00-00

 

Any ideas why?

 

Please see attached code.

 

Thanks in advance

 

 

[gizmola]

MySQL has a number of different operational modes.

 

The default mode is permissive when it comes to invalid Dates. MySQL will create an internal warning, and accept the insert or update with the invalid date, but will store a date value of '0000-00-00'.

 

I don't know what type of column you are using, but if you want to include a time component, you need to use a Datetime or a Timestamp.

 

It's a bit hard from your code to tell what the actual string is that you are generating, but it seems to be invalid in some way.

[Psycho]

This 'array' makes no sense. At least I have never seen assignments used within an array like that. Since you would be overriding the assignments on each iteration of the loop it makes even less sense why you would have assignments (since they are not used).

$stmt->execute(array(
       $pastorname   =       $_POST['pastorsname'][$i],
       $ministryname =       $_POST['ministriesname'][$i],
       $startdate =    "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['xstartdate'][$i])))."'",
       $clientsname =     $_POST['clientname'][$i],
       $video_url =     $_POST['url'][$i],
 
        ));

But, part of your problem is that you are appending a single quote mark to the beginning and end of the date string. You don't quote the values that will be used in prepared statements. Try this:

for ($i = 0; $i < count($_POST['pastorsname']); $i++)
{
    //Create date string
    $startdate = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['xstartdate'][$i])));
    echo "Input: {$_POST['xstartdate'][$i]}, Start date: {$startdate}<br>\n"; //For debugging
    //Create values array
    $values = array(
        $_POST['pastorsname'][$i],
        $_POST['ministriesname'][$i],
        $startdate,
        $_POST['clientname'][$i],
        $_POST['url'][$i]
    );
    //Execute statement
    $stmt->execute($values);
}

Does it work (without the extra quotes)? If not, what does the output show?

 

By the way, you should create your fields something like this

name="record[1][pastorsname]"

Then, in your code you would just do

foreach($_POST['record'] as $record)
{
  //Fields are then referenced like this
  //$record['pastorsname']
  //$record['ministriesname']
}

Edited November 9, 2017 by Psycho

[sonnieboy]

                    <td><INPUT TYPE="TEXT" NAME="pastorsname[]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="ministriesname[]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="xstartdate[]" SIZE="10"></td>
                    <td><INPUT TYPE="TEXT" NAME="clientname[]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="url[]" SIZE="14"></td>

but you are suggesting to have them like below?

                    <td><INPUT TYPE="TEXT" NAME="record[1][pastorsname]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="record[2][ministriesname]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="record[3][xstartdate]" SIZE="10"></td>
                    <td><INPUT TYPE="TEXT" NAME="record[4][clientname]" SIZE="14"></td>
                    <td><INPUT TYPE="TEXT" NAME="record[5][url]" SIZE="14"></td>

Thank you very much psycho for such detailed explanations.

 

Initially, I didn't have the assignments.

 

I added them because I was looking for a way to format the date field.

 

Just to clarify, this is my current markup:

[sonnieboy]

Your solution worked perfectly,however.

 

Thank you so very much sir