how to insert correct date and ID in mysql table

Question

I have a simple form with following fields Auditor, departement, date, weeknr, registration (i have them in dutch..) PHP is a problem since i don't know it too well.

So 2 question's here:

My date field is a jQuery datepicker and returns (07/19/2019) but in mysql I only get 0000-00-00,
Also i would like to have an ID based on the values of some of those fields that a user fills in. Already tried to search how to do it but can't find good examples. So my ID would be something like: departement_weeknr (for example..)

I looked at other examples of code online, but since i don't know PHP it is difficult for me to integrate it.

This is my code how I store all the information

if (isset($_POST['register_form'])) {
  // receive all input values from the form
  $Auditor = mysqli_real_escape_string($db, $_POST['Auditor']);
  $Afdeling = mysqli_real_escape_string($db, $_POST['Afdeling']);
  $Datum = mysqli_real_escape_string($db, $_POST['Datum']);
  $Week = mysqli_real_escape_string($db, $_POST['Week']);
  $Aanmelding = mysqli_real_escape_string($db, $_POST['Aanmelding']);


$query="INSERT INTO 6saudits (Auditor,Afdeling,Datum,Week,Aanmelding) VALUES ('$Auditor','$Afdeling','$Datum','$Week','$Aanmelding')";

$datum = date('Y-m-d', strtotime(str_replace('-', '/', $datum)));
mysqli_query($db, $query);

I hope someone can help me with the date and ID part, so it would be stored properly in mysql table

Edit SQL table:

CREATE TABLE IF NOT EXISTS `6saudits` (
  `ID` int(50) NOT NULL AUTO_INCREMENT,
  `Auditor` varchar(50) NOT NULL,
  `Afdeling` varchar(50) NOT NULL,
  `Datum` date NOT NULL,
  `Week` int(20) NOT NULL,
  `Aanmelding` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

PHP variable names are case-sensitive. `$datum` should be `$Datum`. Also if you intend to modify the variable after building the query, consider using [prepared statements](https://stackoverflow.com/questions/24988867/when-should-i-use-prepared-statements). — Maxime Launois, Jul 19 '19 at 12:05
**Warning:** You might be open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Jul 19 '19 at 18:33
Hi Dharman, thx for your concern, but like I mentioned before, my knowledge of PHP is very limited, the code I'm using is what I learned at school and thought it was save enough. I understand that there are enough examples online, but if I don't know how to change my code into something similar as the examples then I'm stuck anyway. I will look up what your concern is though. — molovhic, Jul 22 '19 at 14:06

score -1 · Answer 1 · answered Jul 19 '19 at 12:17

First Assign the Date value in $Datum. And check the variable name also.'$datum' and '$Datum' are not equal. Then write insert query. so your query like as follwing..

if (isset($_POST['register_form'])) {
  // receive all input values from the form
  $Auditor = mysqli_real_escape_string($db, $_POST['Auditor']);
  $Afdeling = mysqli_real_escape_string($db, $_POST['Afdeling']);
  $Datum = mysqli_real_escape_string($db, $_POST['Datum']);
  $Week = mysqli_real_escape_string($db, $_POST['Week']);
  $Aanmelding = mysqli_real_escape_string($db, $_POST['Aanmelding']);
  $Datum = date('Y-m-d', strtotime(str_replace('-', '/', $Datum)));
  $query="INSERT INTO 6saudits (Auditor,Afdeling,Datum,Week,Aanmelding) VALUES ('$Auditor','$Afdeling','$Datum','$Week','$Aanmelding')";
mysqli_query($db, $query);

**Warning:** You might be open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Jul 19 '19 at 18:32

how to insert correct date and ID in mysql table

1 Answers1