how to insert date in mysql database from php mysqli_query

Question

i am doing is that i take current date and one increment date to 1 day the format i used for date works when inserting directly into the mysql. but from php it is showing this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'issuedate='2019/11/18' returndate='2019/11/19' WHERE id='2'' at line 1


    $date1=date('Y/m/d');
                        $date2=strftime("%Y/%m/%d", strtotime("$date1 +1 day"));
                        //$date2=strtotime();
                        //$date=date('y-m-d', strtotime('+1 day'strtotime($date1)));
                         echo "$date1";
                         echo "$date2";


                    echo "<tbody><tr style='background-color:#64626f; color:white;'><td>".$row['bookid']."</td><td>".$row['userid']."</td><td><form method='post'>".$date1."</td>&nbsp;&nbsp;&nbsp;<td>".$date2."</td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<td><input type='submit' value='CONFIRM ISSUE' class=''  name='issuec' /></form></td></tr></tbody></table>";

                    echo "<br/>";
                    if(isset($_POST['issuec'])){

                    $sqli="UPDATE issue SET status='1' issuedate='$date1' returndate='$date2' WHERE id='$id'";
                    $result2=mysqli_query($con,$sqli)or die(mysqli_error($con));

Your code is vulnerable to SQL injection. You should use prepared statements. See https://stackoverflow.com/a/60496/1839439 — Dharman, Nov 18 '19 at 17:51
seriously dont have time todo all these changings just plz tell me which format of date should enter. i have to submit my project — sid, Nov 18 '19 at 18:01
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Nov 18 '19 at 18:26
Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Nov 18 '19 at 18:26

score 0 · Accepted Answer · answered Nov 18 '19 at 17:54

0

As stated in a comment, your code is vulnerable to SQL Injection. If possible I would recommend switching to PDO and/or using prepared statements if the option is available.

Considering your fields are set to accept a date or varchar field type, i would try adding comma separators between your update variables:

$sqli="UPDATE issue SET status='1', issuedate='$date1', returndate='$date2' WHERE id='$id'";

answered Nov 18 '19 at 17:54

DLzer

159
11

well this works to actually remove that error but now it is showing error – sid Nov 18 '19 at 18:10
Unknown column 'id' in 'where clause – sid Nov 18 '19 at 18:11
however $id has value issueid if you see in my code – sid Nov 18 '19 at 18:11
I would then check that 1. The ID that you've set actually exists in your database, 2. That you're actually setting the value of ````$id````, and 3. Print out your query string ````print($sqli) exit;```` to make sure you're not adding any additional quotation marks. – DLzer Nov 18 '19 at 18:15
Please update my post as the answer to your original question. And please do not downvote answers because they may expose other issues in your code. This answer solved the original error you had posted and answered your question. Thank you and good luck with your project. – DLzer Nov 18 '19 at 18:25

how to insert date in mysql database from php mysqli_query

1 Answers1