Insert a datetime with prepared statement

Question

I am trying to use prepared statements to insert a datetime for a library application. Here is the code thus far:

    global $dbh;
    $query = "INSERT INTO `loan` SET
             `title` = (?), //example value - Lord of the Rings
             `description` = (?), //example value - Trilogy
             `start_date` = (?), //example value 20120701 in String datatype
             `end_date` = (?)";  //example value 20120702 in String datatype

    $statement = $dbh->prepare($query);
    $statement->bind_param("ssss", $title,$description,$startDate,$endDate);

    $statement->execute();
    print $statement->error; //to check errors
    $statement->close();

However, I cannot seem to insert this value into the row. At the same time, somehow the

            print $statement->error

does not seem to display any error.

Any help will really do.

UPDATE:

It actually works. I was just referencing the wrong database. But I want to add a little outro for new people who chanced upon this.

Remove all the comments as mentioned in the comments/answers as they will mess up your string.
For DATETIME, remember to specify the datatype as String as MySQL only recognises String datatype. If you are not using prepared queries, this means you have to add '' quotes for the values.
The insertion will result in the date (2012-07-01 00:00:00) format as time is not specified.
Both SQL queries work. INSERT INTO tbl_name SET col_name = value or INSERT INTO tbl_name(col_name) VALUES (value) work.

Hey nickb, I tried inserting directly into this table with this query `INSERT INTO loan SET title = 'Lord of the Rings', description = 'Trilogy', start_date = '20120715', end_date = '20120720'` and it works — bryan.blackbee, Jul 09 '12 at 14:07
I hope the `//example value` comments are not there in your actual code, as `//` is not a valid comment in mysql. — lanzz, Jul 09 '12 at 14:09
hey lanz, nope the example values are not part of my code. they are inserted into only this code for understanding purposes. you can consider the code without any of the comments. — bryan.blackbee, Jul 09 '12 at 14:10

score 6 · Accepted Answer · answered Jul 09 '12 at 14:22

6

Try something like this:

global $dbh;
$query = "INSERT INTO loan (title, description, start_date, end_date) VALUES (?,?,?,?)"

$statement = $dbh->prepare($query);
$statement->bind_param("ssss", $title,$description,$startDate,$endDate);

$statement->execute();
print $statement->error; //to check errors
$statement->close();

answered Jul 09 '12 at 14:22

Valeh Hajiyev

3,216
4
19
28

2

I have tried changing the sql statement to this configuration also, and it cannot work. – bryan.blackbee Jul 09 '12 at 14:37

DrinkJavaCodeJava · Answer 2 · 2012-07-09T14:53:20.690

-5

Assuming your form input for your date is a type input named "date", here is what you do. In php type

$date = $_POST['date']

$query = "INSERT INTO `loan` SET
             `title` = (?), 
             `description` = (?), 
             `start_date` = ".$date. //Insert variable here
             "`end_date` = (?)";

I know it's not good practice to insert a date in one single type input but I am just using this example for simplicity only. The proper way you can figure out yourself.

edited Jul 09 '12 at 14:53

answered Jul 09 '12 at 14:24

DrinkJavaCodeJava

812
8
22

hey redelman431, I actually have to insert a specific date, as collected by a form imput. hence, `mktime()` might not be what I really need. – bryan.blackbee Jul 09 '12 at 14:38
Here is a new example. Is this what your looking for? – DrinkJavaCodeJava Jul 09 '12 at 14:50
To make it YYYYMMDD format, all you have to do is put three type inputs, put character limits in each input and then concantenate them into one variable. – DrinkJavaCodeJava Jul 09 '12 at 15:14
1

Not a good idea to introduce the possibility of a SQL Injection attack. Besides, is that valid syntax for an Insert? – RowanPD Oct 21 '17 at 12:49

Insert a datetime with prepared statement

2 Answers2