Hi, I am having huge trouble getting a date to the DB. Values for month,day,year come from text boxes on a form. I get no ‘die’ message but the database is empty. I am sure my confusion arises due to the difference between the two formats (PHP vs SQL). Connection is via an include file which I know works because I have used it on another table in the same DB with success.
$themonth = $_POST[‘themonth’];
$theday = $_POST[‘theday’];
$theyear = $_POST[‘theyear’];
$thesite = $_POST[‘thesite’];
$thespiel = htmlentities($_POST[‘thespiel’]);
$eventdate = $theday . “.” . $themonth . “.” . $theyear;
$conn = dbconnect(‘admin’);
$sql = ‘insert into events(talkdate) values ("$eventdate")’;
$result = $conn->query($sql) OR DIE();
Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects? Stop and start your web server to get any changes made to the php.ini to take effect and check using a phpinfo() statement in a .php file that you request via a url to your web server that the changes actually took effect, in case the php.ini that you are editing is not the one that php is using.
Next, modern php (8+) uses exceptions for database statement errors by default. When using exceptions for errors, no discrete logic testing for database errors, including or die() statements, will ever get executed upon an error and should be removed. You are also not outputting anything in the die() statement, so you won’t see anything even if that logic gets executed.
Are you sure the code where the query is at is even being executed?
You should NOT put dynamic values directly into sql query statements, where any sql special character in a value, such as a ’ can break the sql query syntax, which is how sql injection is accomplished. You need to use a prepared query instead.
There are two problems with the sql query you are building -
- You are using an overall single-quoted string. Php variables are not replaced by their value inside of a single-quoted string. If you echo $sql, you can see what it actually is. This problem will go away when you switch to a prepared query, because you won’t be putting php variables directly into sql query statements any longer.
- The talkdate column should be a DATE data type. The format of the value you supply needs to be YYYY-MM-DD.
As to the rest of the posted code -
- Do not copy variables to other variables for nothing. This is a waste of typing. If you had 10 or 30 form fields, would it be a good idea to write out 10 or 30 lines of code copying $_POST variables to discrete variables? Your answer should be no. You should keep the form data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code.
- The post method form processing code should trim all input data, mainly so that you can detect if all white-space characters were entered, before validating the data.
- You need to validate all inputs before using them.
- You should use a date type form field. All modern browsers/clients present a date-picker for a date form field, and they submit the selected/entered date in a YYYY-MM-DD format.
- htmlentities() is an OUTPUT function. Do NOT use it on the input data to your form processing code.
- If this INSERT query could result in a duplicate data error (a unique event already exists) you would need to test in the try/catch exception logic for this query if there is a duplicate index error (number) and setup a message for the user that the submitted data already exists.
Once more, thanks for your help.
I have checked php.ini and the 2 things you mention are present and correct.
The contents of the 3 textboxes on the form are stored OK as variables (I didnt even know there was a date type form field… maybe I should be in the ‘beginners’ section!)
I used simple variables derived from the POST array to eliminate possible typos with different sorts of brackets, nesting and so on. I am quite OK with doing that, though I realise it makes for more typing - but I do see what you’re getting at.
I shall take your advice re prepared queries, though I’ve never used them before. Sounds like that could solve a lot of trouble.
The talkdate column is a date data type. At least I got that right! 
Point 5: OK.
Point 6: doesn’t arise because the DB is empty!
So many thanks (again!)
Mike
Just because you have a php.ini with those settings in it doesn’t mean that php is using them. You must check using a phpinfo() statement, as written, if those setting are actually being set and used by php. If you have never seen any php runtime errors from any code, these settings are likely not being used.
The point of about the sql query statement having overall single-quotes, means that the value being used by the database server is literally the string of characters - $ e v e n t d a t e. This is not a date and depending on the database server’s mode, either produces a query error or the value is truncated to the nearest valid value, which would be 0000-00-00, and would result in a row being inserted, assuming nothing else about the table definition is producing an error.
Cheers. I did use phpinfo() and also looked at php.ini just to double-check, and both settings were as you recommended - backed up by the fact that I was getting those scary orange-cell error messages! (scary but necessary of course)
I am going to have a try with the prepared query arrangement you recommended, but for now, if I were to enclose the existing query in double quotes and surround the variables in single quotes, a la:
$sql = "insert into events (talkdate) values ‘$eventdate’ ";
would that work? (I have put a space between the final single quote and the closing double quote for ease of reading)
By the way, if you’re ever in the Norwich area I will buy you a beer for all your help - and patience.
You can always change the quote-type and observe the result.
If you are not getting any php error with the current code and there is no row being inserted, either the code where the query is at is not being executed at all (I would add an echo statement or a print_r($_POST); to confirm) or you may have more then one database and the one being selected for this query is not the one you are looking at to find the row.
OK, me again!
I tried the prepared technique and got a PHP error message saying "call to undefined function prepare. I just checked versions on my webserver: PHP is 7.0.4 and mysql is 5.7.11.
I echoed all the variables from the form and they are all fine, all strings. Offending line is 2nd line below.
$conn = dbconnect(‘admin’);
$stmt = $mysqli>prepare(“insert into events(theplace,talktitle,speaker,talkdate,spkrsite,descr) values(?,?,?,?,?,?)”);
$stmt-> bind_param(“ssssss”,$theplace,$talktitle,$speaker,$thedate,$thesite,$thespiel);
$stmt->execute();
$stmt->close();
$conn->close();
(Above code skeleton from w3 schools example)
Quick update.
Good news: At least when I hit the submit button I get a nice clean page with no error messages!
Bad news: The ‘events’ table is still empty.
Previously PHP was saying (quite reasonably) that $mysqli was undefined. So much for w3 schools! So I searched around and saw how that variable is created (looks like a connection thing): $mysqli = new mysqli(localhost, user name, password, db name); {all in single quote pairs}
Problem solved!!
Put my deerstalker on, magnifying glass out (I dont smoke, so no pipe):
All the fields in the DB were set to ‘not null’. Reversed that setting and all the data is now there!
So many thanks for bearing with me. Your help has been really, really appreciated. I had a heart attack 2 months ago and thought I might be heading for another!
Mike
Is this current date and time? I finally just defined the db as datetime and inital value to current date/time.
With all the systems and databases that I have worked on, I just found it best just to keep it simple. date as 8 chars, and time as 6 chars.
All these systems have different date/time implementation. It is perfectly fine if you intend to stay with the same stuff. But my last job was with a bank and we exchanged hundreds of thousands files each month with other organizations. So you have to go this way.
date_default_timezone_set('US/CENTRAL');
$ddate = date("Ymd");
$dtime = date("his");
$stmt = $pdo->prepare("INSERT INTO user_post (ddate,dtime,title,content) VALUES ('$ddate','$dtime',?,?)");
The OP’s talkdate field is a user selected/entered (future) event date.