makamo66 Posted October 23, 2019 Share Posted October 23, 2019 I added a column to my database with SQL as follows: ALTER TABLE comment_table ADD date_col Datetime NOT NULL; I try to insert into the database with the following PHP but nothing gets inserted. if(isset($_POST["submit"])) { $date_col = "test";//this will be DateTime later $name = $_POST["name"]; mysqli_query($connection, "INSERT INTO comment_table (name, date_col) VALUES ('$name', '$date_col')"); } $comsql = "SELECT * FROM comment_table"; $comres = mysqli_query($connection, $comsql); while($comr = mysqli_fetch_assoc($comres)){ ?> <div class="row"> <p>Name: <strong><?php echo $comr['name']; ?></strong>This is the code that is being pointed to as undefined index. <?php echo $comr['date_col']; ?> </p> <?php } ?> </div> The data I'm using for the date_col column is varchar and it should be Datetime but I don't know if that's the reason for the error. I would like to set $date_col equal to a Datetime expression for testing purposes but the formatting I chose wasn't working either. Quote Link to comment Share on other sites More sharing options...
gw1500se Posted October 23, 2019 Share Posted October 23, 2019 (edited) First please use the code icon (<>) to insert code and select PHP. It makes your code much easier to read. The main thing I notice is that you are using single quotes for your variaables when you build the query string. Single quotes tells PHP to treat the contents as literals. Had you used the code icon you probably would have seen that. mysqli_query($connection, "INSERT INTO comment_table (name, date_col) VALUES (".$name.", ".$date_col.")"); That being said never, ever use the above code. You leave it wide open to injection attack. Any time you want to insert HTML variables into a query you MUST use prepared statements. Edited October 23, 2019 by gw1500se Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2019 Share Posted October 23, 2019 DATETIME columns require Y-m-d H:i:s format. (eg 2019-10-23 14:52:38 ) Have you checked the column exists, say, with "DESCRIBE comment_table" or "SHOW CREATE TABLE comment_table"? @gw1500se the quotes are correct mysqli_query($connection, "INSERT INTO comment_table (name, date_col) VALUES ('$name', '$date_col')"); ^ ^ Quote Link to comment Share on other sites More sharing options...
gw1500se Posted October 23, 2019 Share Posted October 23, 2019 (edited) I always have trouble using them in that context which is why I use my suggestion trouble free. Edited October 23, 2019 by gw1500se Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2019 Share Posted October 23, 2019 String literals in a query require single quotes otherwise they are treated as column names. Identifiers which contain spaces, or are reserved words, require backticks. SELECT `group` , fname as `first name` FROM user WHERE lname = 'jones' ; Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 23, 2019 Share Posted October 23, 2019 The OP created the column as datetime and is trying to insert a text string. $date_col = "test";// Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted October 23, 2019 Share Posted October 23, 2019 1 hour ago, gw1500se said: That being said never, ever use the above code. You leave it wide open to injection attack. Any time you want to insert HTML variables... Serious question: is that case for every variable? $name should definitely be inserted using a prepared query since that contains a value supplied by the user. What about the date? If it's a date that supplied by the user, it should go through the prepared query. However, what if the date is generated by the server / script? Quote Link to comment Share on other sites More sharing options...
gw1500se Posted October 23, 2019 Share Posted October 23, 2019 Yes, when it comes from an HTML page. That is any time you are inserting user provided data. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2019 Share Posted October 23, 2019 4 minutes ago, cyberRobot said: However, what if the date is generated by the server / script? I see no problem with this (eg if date comes from a date picker) as the date finally used is not that originally input... $date = (new DateTime($_POST['date']))->format('Y-m-d'); $res = $pdo->query("SELECT whatever FROM tablename WHERE thedate > '$date' "); Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted October 23, 2019 Share Posted October 23, 2019 34 minutes ago, gw1500se said: Yes, when it comes from an HTML page. That is any time you are inserting user provided data. Ah...that's what was meant by HTML variables. That makes sense. The safest bet would be to use prepared statements when working with user-supplied data in your queries. That way you're less likely to make mistakes. However, as Barand pointed out, if you're able to validate the user data in some way (and you know what you're doing), the variable containing the data could be put directly into the query. Of course, variables that might contain something that would break the query, like a quotation mark, should go through the binding process of prepared statements. That way the quotes can be escaped so the query doesn't break. Quote Link to comment Share on other sites More sharing options...
makamo66 Posted October 25, 2019 Author Share Posted October 25, 2019 I changed the code so that it is using prepared statements but I just get new errors now. I altered the table so that the date_col column has the data type of varchar. I get the following warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement if(isset($_REQUEST["submit"]) ) { $date = new DateTime('', new DateTimeZone('America/New_York')); $name = mysqli_real_escape_string($connection, $_REQUEST["name"]); $date_col = $date->format('M d, Y H:i'); $website = $_REQUEST["website"]; $comment = mysqli_real_escape_string($connection, $_REQUEST["comment"]); $sql = "INSERT INTO comment_table (name,date_col,website,comment) VALUES ('$name', '$date_col','$website','$comment')"; if($stmt = mysqli_prepare($connection, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt,'ssss',$name,$date_col,$website,$comment); // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ echo "Records inserted successfully."; } else{ echo "ERROR: Could not execute query: $sql. " . mysqli_error($connection); } } else{ echo "ERROR: Could not prepare query: $sql. " . mysqli_error($connection); } // Close statement mysqli_stmt_close($stmt); // Close connection mysqli_close($connection); Quote Link to comment Share on other sites More sharing options...
Barand Posted October 25, 2019 Share Posted October 25, 2019 Your error is because you have no parameter placeholders in the query that you are preparing. (See the examples) 2 hours ago, makamo66 said: $date_col = $date->format('M d, Y H:i'); Dates stored in that format are as much use as a chocolate teapot. You cannot do correct comparisons and therefore you can't sort them. You can't use the dozens of date/time functions without reformatting Store data for functionality, not prettiness. You can format it for human consumption on output, I've told you the correct format to use. Quote Link to comment Share on other sites More sharing options...
maxxd Posted October 25, 2019 Share Posted October 25, 2019 5 minutes ago, Barand said: Store data for functionality, not prettiness. You can format it for human consumption on output Just feel like this should be repeated (maybe some of my coworkers will happen across it). Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 25, 2019 Share Posted October 25, 2019 On 10/23/2019 at 10:14 AM, Barand said: I see no problem with this (eg if date comes from a date picker) What guarantee do you have that it was actually submitted by the users form and the users date picker? There are several ways to make a Post Request without using the users form. Bottom line, it is user supplied data and you well understand to NEVER trust user supplied data. The one case where you can safely get away with a variable in the query is if the date (or whatever) comes directly from the code, but then then that isn't user supplied data is it? But why do it? Consistency is always a good thing. Quote I changed the code so that it is using prepared statements No you didn't. Read the manual and see what you did wrong. Quote Link to comment Share on other sites More sharing options...
makamo66 Posted October 25, 2019 Author Share Posted October 25, 2019 The solution posted to a different forum was: $sql = "INSERT INTO comment_table (name,date_col,website,comment) VALUES (?, ?, ?, ?)"; Quote Link to comment Share on other sites More sharing options...
Barand Posted October 25, 2019 Share Posted October 25, 2019 So you are one of those people who likes to waste people's time by posting simultaneously on multiple forums? That will be remembered. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.