Re: Formatting Date (m/d/yyyy) To Timestamp

> > Now I don't get any errors, but all the dates come to MySQL as 0000-00-00 > 00:00:00... I am using strtotime with dates in the format: d/m/yyyy > You need to format the dates (before sending them to MySQL) to ISO notation (as string) or as a timestamp (as int) I wrote a minimal example https://pastebin.com/dqyP654z HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Op wo 13 jan. 2021 om 13:50 schreef Talin Sharma <talinsharma.in@gmail.com>: > Now I don't get any errors, but all the dates come to MySQL as 0000-00-00 > 00:00:00... I am using strtotime with dates in the format: d/m/yyyy > > Could you help? > > > Thanks: > Talin Sharma > > > On Wed, Jan 13, 2021 at 5:51 AM Serge Fonville <serge.fonville@gmail.com> > wrote: > >> This might contribute to solving your problem: >> https://stackoverflow.com/a/805880 >> Also check >> https://www.php.net/manual/en/mysqli-stmt.bind-param.php >> >> What it means is that you need to specify the data types in the bind >> definition (contrary to what I said before), you should use convert the >> date field to an ISO date (so you can use it as a string. >> >> Fatal error: Uncaught ArgumentCountError: The number of elements in the >>> type definition string must match the number of bind variables >>> >> This error is correct, since you are using 6 bind variables, bind_param >> expects 7 arguments. >> >> HTH >> >> Kind regards/met vriendelijke groet, >> >> Serge Fonville >> >> http://www.sergefonville.nl >> >> >> Op di 12 jan. 2021 om 14:12 schreef Talin Sharma < >> talinsharma.in@gmail.com>: >> >>> Here is a link: |�wJD�k���+� >>> https://paiza.io/projects/mx5k3SDvxC9lnkDBsm8JYw >>> >>> Also, I fixed the 'd.' thing... I still get the same error. >>> >>> Fatal error: Uncaught ArgumentCountError: The number of elements in the >>>> type definition string must match the number of bind variables in >>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php:64 >>>> Stack trace: #0 >>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php(64): >>>> mysqli_stmt->bind_param('1', false, 1, 1, 1, 1) #1 {main} thrown in >>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php >>>> on line 64 >>> >>> >>> >>> Thanks: >>> Talin Sharma >>> >>> >>> On Mon, Jan 11, 2021 at 8:27 PM Serge Fonville <serge.fonville@gmail.com> >>> wrote: >>> >>>> Fatal error: Uncaught ArgumentCountError: The number of elements in the >>>>> type definition string must match the number of bind variables >>>> >>>> This shows the number of values in your query does not match the amount >>>> of bind variables, you should post your query as well, so we can look at it. >>>> Also, instead of mailing excerpts, it is probably easier to use >>>> something like pastebin to share your entire code. >>>> >>>>> I have also noticed that "DateTime::createFromFormat('d./m/Y', >>>>> $d);" >>>>> returns false... Why? >>>> >>>> The value of $d seemingly does not match the format, perhaps due to the >>>> extra '.' (dot) after the d in the format (or is that intentional?) >>>> >>>> Kind regards/met vriendelijke groet, >>>> >>>> Serge Fonville >>>> >>>> http://www.sergefonville.nl >>>> >>>> >>>> Op di 12 jan. 2021 om 00:53 schreef Talin Sharma < >>>> talinsharma.in@gmail.com>: >>>> >>>>> Without the quotes I get: >>>>> >>>>>> >>>>>> Fatal error: Uncaught ArgumentCountError: The number of elements in >>>>>> the type definition string must match the number of bind variables in >>>>>> >>>>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php:64 >>>>>> Stack trace: #0 >>>>>> >>>>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php(64): >>>>>> mysqli_stmt->bind_param('1', false, 1, 1, 1, 1) #1 {main} >>>>>> thrown in >>>>>> >>>>>> C:\Users\talin\Documents\Code\Web\Projects\Transactions\excel\excelUpload.php >>>>>> on line 64 >>>>>> >>>>> >>>>> What now? >>>>> I have also noticed that "DateTime::createFromFormat('d./m/Y', >>>>> $d);" >>>>> returns false... Why? >>>>> >>>>> Thanks: >>>>> Talin Sharma >>>>> >>>>> >>>>> On Mon, Jan 11, 2021 at 4:59 PM Serge Fonville < >>>>> serge.fonville@gmail.com> wrote: >>>>> >>>>>> When I try : $stmp->bind_param('$des', '$d1', >>>>>> '$a', '$m', '$t', '$s'); >>>>>>> I get: Fatal error: Uncaught Error: mysqli_stmt::bind_param(): >>>>>>> Argument #2 cannot be passed by reference >>>>>> >>>>>> What does that mean? >>>>>> >>>>>> You are converting all values to strings, the whole idea is to >>>>>> maintain the data type, so you should remove the quotes. >>>>>> On top of that, you are using single quotes, so the values are not >>>>>> expanded inside the string. >>>>>> >>>>>> Kind regards/met vriendelijke groet, >>>>>> >>>>>> Serge Fonville >>>>>> >>>>>> http://www.sergefonville.nl >>>>>> >>>>>> >>>>>> Op ma 11 jan. 2021 om 22:44 schreef Talin Sharma < >>>>>> talinsharma.in@gmail.com>: >>>>>> >>>>>>> When I try : $stmp->bind_param('$des', '$d1', >>>>>>> '$a', '$m', '$t', >>>>>>> '$s'); >>>>>>> I get: Fatal error: Uncaught Error: mysqli_stmt::bind_param(): >>>>>>> Argument #2 cannot be passed by reference >>>>>>> >>>>>>> What does that mean? >>>>>>> Thanks: >>>>>>> Talin Sharma >>>>>>> >>>>>>> >>>>>>> On Mon, Jan 11, 2021 at 12:44 PM Serge Fonville < >>>>>>> serge.fonville@gmail.com> wrote: >>>>>>> >>>>>>>> What would I put where you have to tell what type of date(string, >>>>>>>>> number?) >>>>>>>>> >>>>>>>> One of the benefits of prepared statements is that you do not need >>>>>>>> to convert or specify data types. Since the mysqli library handles >>>>>>>> that for >>>>>>>> you. It finds the datatype from the datatype of the variable and >>>>>>>> passes its >>>>>>>> value to the database service. >>>>>>>> >>>>>>>> Kind regards/met vriendelijke groet, >>>>>>>> >>>>>>>> Serge Fonville >>>>>>>> >>>>>>>> http://www.sergefonville.nl >>>>>>>> >>>>>>>> >>>>>>>> Op ma 11 jan. 2021 om 13:42 schreef Talin Sharma < >>>>>>>> talinsharma.in@gmail.com>: >>>>>>>> >>>>>>>>> I can try this but if I use prepared statements, I have >>>>>>>>> multiple >>>>>>>>> fields to insert. What would I put where you have to tell what >>>>>>>>> type of >>>>>>>>> date(string, number?) >>>>>>>>> >>>>>>>>> Thanks: >>>>>>>>> Talin Sharma >>>>>>>>> >>>>>>>>> >>>>>>>>> On Mon, Jan 11, 2021 at 2:42 AM Serge Fonville < >>>>>>>>> serge.fonville@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> I tried and minimize your question to the following code: >>>>>>>>>> >>>>>>>>>> <?php >>>>>>>>>> >>>>>>>>>> $date_from_sheet = "1/1/2021"; >>>>>>>>>> // As you know the format of the date, it is easier to >>>>>>>>>> convert >>>>>>>>>> based on that >>>>>>>>>> $timestamp = DateTime::createFromFormat('d./m/Y', >>>>>>>>>> $date_from_sheet); >>>>>>>>>> >>>>>>>>>> // Instead of inserting the values inside the string, use >>>>>>>>>> bind >>>>>>>>>> variables instead. >>>>>>>>>> $query = "INSERT INTO some_table(date_field) values >>>>>>>>>> (?)"; >>>>>>>>>> // I'm assuming $mysqli is already instantiated >>>>>>>>>> elsewhere >>>>>>>>>> $stmt = $mysqli->prepare($query); >>>>>>>>>> // The only field in the query is the date field, >>>>>>>>>> // so it is clear that the only parameter is the >>>>>>>>>> corresponding >>>>>>>>>> value >>>>>>>>>> $stmt->bind_param($timestamp); >>>>>>>>>> // The only remaining thing to do is execute it. >>>>>>>>>> $stmt->execute(); >>>>>>>>>> ?> >>>>>>>>>> >>>>>>>>>> Does this enable you to solve your issue? >>>>>>>>>> >>>>>>>>>> I used the following sources: >>>>>>>>>> >>>>>>>>>> https://www.php.net/manual/en/mysqli-stmt.bind-param.php >>>>>>>>>> >>>>>>>>>> https://www.php.net/manual/en/datetime.createfromformat.php >>>>>>>>>> >>>>>>>>>> Kind regards/met vriendelijke groet, >>>>>>>>>> >>>>>>>>>> Serge Fonville >>>>>>>>>> >>>>>>>>>> http://www.sergefonville.nl >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Op ma 11 jan. 2021 om 02:18 schreef Talin Sharma < >>>>>>>>>> talinsharma.in@gmail.com>: >>>>>>>>>> >>>>>>>>>>> I have also tried: $d1 = date("Y-m-d H:i:s", >>>>>>>>>>> strtotime($d)); But >>>>>>>>>>> that returns random dates when I insert into the >>>>>>>>>>> database... What is wrong? >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Thanks: >>>>>>>>>>> Talin Sharma >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Sun, Jan 10, 2021 at 7:10 PM Talin Sharma < >>>>>>>>>>> talinsharma.in@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> Also here is the excel sheet I am using for >>>>>>>>>>>> testing. >>>>>>>>>>>> Thanks: >>>>>>>>>>>> Talin Sharma >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> On Sun, Jan 10, 2021 at 7:05 PM Talin Sharma < >>>>>>>>>>>> talinsharma.in@gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> I am attaching all of my code. How do I enter >>>>>>>>>>>>> the timestamp to >>>>>>>>>>>>> the database then? >>>>>>>>>>>>> >>>>>>>>>>>>> Thanks: >>>>>>>>>>>>> Talin Sharma >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> On Sun, Jan 10, 2021 at 7:01 PM Serge Fonville >>>>>>>>>>>>> < >>>>>>>>>>>>> serge.fonville@gmail.com> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> It doesn't seem to work. What I did: >>>>>>>>>>>>>> $d1 = 'strtotime(' >>>>>>>>>>>>>>> 1/1/2021'); echo $d1;' Echos: >>>>>>>>>>>>>>> '980053200' That in human >>>>>>>>>>>>>>> form is: 'November 30, -0001' >>>>>>>>>>>>>>> Not what I want... any idea >>>>>>>>>>>>>>> why? >>>>>>>>>>>>>> >>>>>>>>>>>>>> You are converting the timestamp to a >>>>>>>>>>>>>> string again using >>>>>>>>>>>>>> echo, this outputs a unix epoch. You should >>>>>>>>>>>>>> instead treat $d1 as >>>>>>>>>>>>>> >>>>>>>>>>>>>> Perhaps if you share a bigger part of your >>>>>>>>>>>>>> code, it would be >>>>>>>>>>>>>> easier to be of help. >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Kind regards/met vriendelijke groet, >>>>>>>>>>>>>> >>>>>>>>>>>>>> Serge Fonville >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> http://www.sergefonville.nl >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Op ma 11 jan. 2021 om 00:51 schreef Talin >>>>>>>>>>>>>> Sharma < >>>>>>>>>>>>>> talinsharma.in@gmail.com>: >>>>>>>>>>>>>> >>>>>>>>>>>>>>> It doesn't seem to work. What I >>>>>>>>>>>>>>> did: $d1 = 'strtotime(' >>>>>>>>>>>>>>> 1/1/2021'); echo $d1;' Echos: >>>>>>>>>>>>>>> '980053200' That in human >>>>>>>>>>>>>>> form is: 'November 30, -0001' >>>>>>>>>>>>>>> Not what I want... any idea >>>>>>>>>>>>>>> why? >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thanks: >>>>>>>>>>>>>>> Talin Sharma >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> On Sun, Jan 10, 2021 at 6:30 PM Serge >>>>>>>>>>>>>>> Fonville < >>>>>>>>>>>>>>> serge.fonville@gmail.com> wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Hi Talin, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Would >>>>>>>>>>>>>>>> https://www.php.net/manual/en/function.strtotime.php >>>>>>>>>>>>>>>> solve your conversion problem? >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Kind regards/met vriendelijke >>>>>>>>>>>>>>>> groet, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Serge Fonville >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> http://www.sergefonville.nl >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Op ma 11 jan. 2021 om 00:18 schreef >>>>>>>>>>>>>>>> Talin Sharma < >>>>>>>>>>>>>>>> talinsharma.in@gmail.com>: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Hi All, >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> I have a string: >>>>>>>>>>>>>>>>> '1/1/2021'. I want to turn this into a >>>>>>>>>>>>>>>>> timestamp for entering into >>>>>>>>>>>>>>>>> MySql. I have tried everything I could find. >>>>>>>>>>>>>>>>> Please let me know how to >>>>>>>>>>>>>>>>> proceed. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Also, let me know if you need >>>>>>>>>>>>>>>>> more information. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Thanks: >>>>>>>>>>>>>>>>> Talin Sharma >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>