How to save data in Mysql using Prepared statments

Asked

Viewed 247 times

3

I’m changing the fields to save date in Mysql, fromvarchar for date, and I was in doubt about the correct way to include the date fields in the database using prepared statments.

As DD/MM/YYYY use during the script to make some comparisons etc, I wanted to make the change only when including in the bank, during the insert.

I don’t think you can use the STR_TO_DATE( string, formato ) with prepared statments, as indicated in this answer, then, following this outra resposta I adapted it like this:

if ($stmt = mysqli_prepare($mysqli, $sql)) {

mysqli_stmt_bind_param
(
    $stmt,
    's',
    date("Y-m-d", strtotime($dataDMY)),
...

And it worked, but debugging I get a notice undefined variable, that did not appear without putting date("Y-m-d", strtotime($dataDMY)) within the execution of bind::param. This way is correct? What is the ideal way?

php mysqli

  • where do you come from $dataDMY ?

  • From a form field. It comes in dd/mm/yyyy format...

  • but where is this variable set in php? There must be something like $dataDMY = $_POST['dataDMY'].. something there

  • Ah, so, it’s in another file, this file is just to include in the comic, and include with include at the end of the form output file... then when it runs it finds the variable, but debugging gives this notice that it did not give before...

  • Do it like this, before this stretch if ($stmt = mysqli_prepare($mysqli, $sql)) {, put this print_r($_POST); print_r($_GET); exit; And put the result in your question.

  • So the exit to this field with the print_r is for example: [dataDMY]=> 11/10/2010... Tomorrow I edit the question, because now the sleep even hit :) thanks for now! Hugs!

  • 1

    The idea is to receive the variable in the format d/m/Y and convert to Y-m-d and then record in the bank.

  • That @rray, that’s right...

  • 1

    Good Q solved the problem, about the undefined variable the code that occurs this is not in the question. o bind_param seems to accept only references as argument.

Show 4 more comments

1 answer

3


The format coming in the POST is string. You need to convert to a date instance.

$date = DateTime::createFromFormat('d/m/Y', $_POST['dataDMY'])->format('Y-m-d');

So the variable will be ready to be inserted into the seat in a field of type TIMESTAMP or DATETIME.

  • Thanks Junior, but I still have one question: could not do this conversion within the bind_param, the way I put it in the question date("Y-m-d", strtotime($dataDMY)). Because this would ensure that the conversion occurred only to include in the database, since it is used in several other files in the dd/mm/YYY format... it would also be possible to convert just before the bind_param, but wanted to know if there is any "evil" in converting inside bind_param... thanks for now. + 1

  • There is nothing wrong with this way of doing inside the bind. I was a little confused by this question of yours. There was not much connection

  • Yes, I think it was a bit confusing, but still you helped a lot. : ) Thanks.

Browser other questions tagged php mysqli

You are not signed in. Login or sign up in order to post.