Getting the date and sending it through mysqli insert?

Question

Basically I've got a contact us form, and I would like to submit the date as well, when the submit button is pressed. Basically you fill out the boxes, submit, and the date goes through with it. I am just not sure exactly how to do it...

I've got the form in a .php in another page, but the action leads to the current page.

The date needs to be: year-mm-dd

<?php

include 'connection.php';

$name = $_POST['name'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$question = $_POST['question'];
$date = //I need the date here so it can go through the insert propperly;


if ($_POST['submit']){

    mysqli_query($connect,"INSERT INTO contactusresults (`id`, `date`, `name`, `email`, `phone`, `question`) VALUES (NULL, '$date', '$name', '$email' , '$phone', '$question')") or die(mysql_error());


    header('Location: ../pages/index.php'); 
    //change to submit page 
}else{
    header('Location: ../pages/index.php');
}


?>

edited, not `NOW()` but `CURDATE()`. you're also open to an sql injection — Funk Forty Niner, May 27 '16 at 22:35
@AustinM-S I see you accepted the other answer; that's fine, I'm ok with that (from what I posted). However and I need to note that it will be much easier to query later on (on SELECT, UPDATE, DELETE) using MySQL's built-in date/datetime features/functions. The choice is yours, but take it from experience. ;-) — Funk Forty Niner, May 27 '16 at 23:13

score 2 · Answer 1 · edited May 23 '17 at 11:44

"The date needs to be: year-mm-dd"

Use MySQL's CURDATE() function, as it returns YYYY-mm-dd.

VALUES (NULL, CURDATE(), '$name', ...

and be sure to set your date column to a DATE type. If it's presently VARCHAR then it won't work.

References:

You should also add exit; after each header. Otherwise, your code may want to continue to execute.

Note: mysql_error() that needs to read as mysqli_error($connect). The MySQL_ and MySQLi_ APIs do not intermix.

Your connection method is unknown, so you need to use the same one that you used for your query, being the MySQLi_ API.

Reference:

http://php.net/manual/en/function.mysqli-connect.php

If it's MySQL_ or PDO, then those will not work with your query. Different MySQL APIs do not intermix.

You should also check for errors.

Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code.

Your present code is open to SQL injection. Use mysqli with a prepared statement, or PDO with prepared statements, they're much safer.

Foonotes:

"I've got the form in a .php in another page, but the action leads to the current page."

That is unclear. The action needs to lead to your query code. "The current page" (to me) means either action="html_form.php" or action="".

Therefore it needs to be changed to the one you're using to query with.

You also should check against empty fields using empty().

Another thing. I noticed you are using NULL for your id column. Make sure that it does accept NULL values, otherwise your query will fail. If that should be the case, and that your id is AUTO_INCREMENT, then use '' as per the following for the id column.

VALUES ('', CURDATE(), '$name', ...

score 1 · Accepted Answer · answered May 27 '16 at 22:34

1

Use php date function.

$date = date("Y-m-d H:i:s"); // If your mysql column is datetime
$date = date("Y-m-d"); // If your mysql column is just date

Use NOW() in sql

mysqli_query($connect,"INSERT INTO contactusresults (`id`, `date`, `name`, `email`, `phone`, `question`) 
VALUES (NULL, NOW(), '$name', '$email' , '$phone', '$question')") or die(mysql_error());

answered May 27 '16 at 22:34

Abi Narayan

66
1

`NOW()` returns `YYYY-mm-dd 00:00:00` and not the intended `YYYY-mm-dd` – Funk Forty Niner May 27 '16 at 23:17

score 0 · Answer 3 · answered May 27 '16 at 23:13

As an alternative to the selected answer:

On your MySQL table you can set the column for the date value to save the timestamp of the insert, which will include the date as specified, and can be called using the DATE_FORMAT function. This method would completely negate the need to manually set and save the date value upon insert.

Table setup:

CREATE TABLE `contactusresults` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(256),
    `email` VARCHAR(256),
    `phone` VARCHAR(256),
    `question` VARCHAR(256),
    `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
)

Table Insert:

 mysqli_query($connect,"INSERT INTO contactusresults (`name`, `email`, `phone`, `question`) VALUES ( '$name', '$email' , '$phone', '$question')") or die(mysqli_error($connect));

To extract the date from the table data:

$var = mysqli_query($connect, "SELECT `name`,DATE_FORMAT(`date`, '%Y-%d-%m') as `date` FROM contactusresults WHERE id = 1");

NOTES:

Your MySQLi_error needs to be mysqli_ and needs to contain the connection variable.
Any NULL values upon insert, such as the id can be ignored
Only one timestamp column can be set in a table.

Getting the date and sending it through mysqli insert?

3 Answers3