-1

I am trying to insert data into a MySql database with the query below.

$sqlQuery = "insert into abc set a='$var1', b=STR_TO_DATE('$b', '%d/%m/%Y')"

I get this error

Incorrect datetime value: '' for function str_to_date()

How can I default both a and b to NULL when the variable values are missing.

Jacky Huynh
  • 17
  • 1
garg10may
  • 5,794
  • 11
  • 50
  • 91
  • 3
    You are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. Simply escaping your variables is not enough. See [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). – elixenide Mar 18 '19 at 06:17
  • Are those fields nullable? You can set the `default` values of `a` and `b` to null in MySQL, then only supply those fields when executing the query in PHP. – Qirel Mar 18 '19 at 06:23
  • thanks @EdCottrell you have rightly suggested, I am basically fond of ORMs like hibernate and sql-Alchemy but very new to PHP – garg10may Mar 18 '19 at 06:25
  • @Qirel yes the fields are by default NULL, but I have multiple columns and would have to check using separate conditions to only run query when they are not null, I would rather have query handle it by self. – garg10may Mar 18 '19 at 06:26
  • Given your past experience with hibernate etc., take a look at [Doctrine](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/index.html) – gview Mar 18 '19 at 06:29
  • Also, the basic mysql date compatible string is of the format 'yyyy-mm-dd'. If you pass your strings in in that format there is no need to run mysql conversion functions. With that said -- for php, if you aren't going to use Doctrine, then use PDO and read @EdCottrell link. It's bad coding not to. Also the answer from esnkrimi is pertinent because `SET=` is the format you use for updates, not insert. – gview Mar 18 '19 at 06:32
  • Also, before people complain, yes you can use SET=, but like many mysql specific things, just because you can doesn't mean you should. Use the ANSI standard form. – gview Mar 18 '19 at 06:42
  • :) I get you, I am taking all these suggestions and would be utilizing them for new scripts, for now, I need to do some quick dirty bug fix :P and this format code is everywhere – garg10may Mar 18 '19 at 06:46

4 Answers4

1

If the columns are null by default, you can omit them in the INSERT query if the values are empty. Generate the query dynamically using an array, and implode on it within your query. The keys represent the column-names, the value of the element in the array is the value to be inserted. 

$columns = [];

if (!empty($var1)) {
    $columns['a'] = $var1;
}
if (!empty($var2)) {
    $columns['b'] = date("Y-m-d", strtotime($var2));
}

if (!empty($columns)) {
    $query = "INSERT INTO abc (".implode(", ", array_keys($columns)).") 
                     VALUES (".implode(", ", array_values($columns)).")";
}

If you can tell which database-API you're using (MySQLi or PDO), I can show you how to dynamically create the prepared statement instead, which you really should use! See How can I prevent SQL injection in PHP?

Update: Using prepared statements with MySQLi
MySQLi bindings are ordered, and using placeholders as ?. You still generate the values in $columns as before, but the generation of the query is slightly different.

  • Use trim(str_repeat("?, ", count($columns)), ", ") to generate the placeholders.
  • Use str_repeat("s", count($columns)) to generate the types.
  • Use the "unpacking operator" ... to unpack all the values.

The below code assumes that the connection-object is called $mysqli.

if (!empty($columns)) {
    $column_list = implode(", ", array_keys($columns));
    $bindings_list = trim(str_repeat("?, ", count($columns)), ", ");
    $types_list = str_repeat("s", count($columns));

    $query = "INSERT INTO abc ($column_list)
                     VALUES ($bindings_list)";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param($types_list, ...$columns);
    $stmt->execute();
    $stmt->close();
}
Qirel
  • 25,449
  • 7
  • 45
  • 62
0

Set default value of these columns in your database as NULL so when these values is missing then NULL with automatically saved as default.

neha gupta
  • 30
  • 3
-1

Don't Get MySQL to change the date format. Do it by your PHP code and make sure that SQL injection is handled.

$a = $_POST['var1'] ?? null;
$b = $_POST['var2'] ? date('Y-m-d', strtotime($_POST['var2'])) : null;
try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'your_username', 'your_password');
    $query_obj = $pdo->prepare("INSERT INTO abc (a, b) VALUES (:a, :b)");
    $query_obj->execute([':a' => $a, ':b' => $b]);
    echo "Data inserted successfully!";
} catch (\Exception $e) {
    echo "Error: " . $e->getMessage();
}
Martin
  • 22,212
  • 11
  • 70
  • 132
Gaurang Joshi
  • 684
  • 16
  • 32
  • 1
    Please add some more explanation to your code - and don't give MySQL a chance to perform SQL injections.... – Nico Haase Mar 18 '19 at 06:55
-1

You should use the format

month / day / year:

$sqlQuery = "Insert into abc(a,b) values($var1',STR_TO_DATE('$b', '%m/%d/%Y')"
Jacky Huynh
  • 17
  • 1
esnkrimi
  • 140
  • 1
  • 8
  • 2
    Always include an explanation with your answer. Why would this code work? What did you change, and why did you change it? – Qirel Mar 18 '19 at 06:24
  • standard SQL date format is *year-month-day*. Having day in the middle is nonesensical. – Martin Mar 18 '19 at 07:04
  • my question is different I am not looking for date formats but setting NULL – garg10may Mar 18 '19 at 07:14