Getting the error Undefined index but the column exists

[makamo66]

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.
 

[gw1500se]

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

[Barand]

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')");
                          ^                                                                        ^

[gw1500se]

I always have trouble using them in that context which is why I use my suggestion trouble free.

Edited October 23, 2019 by gw1500se

[Barand]

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' ;

 

[benanamen]

The OP created the column as datetime and is trying to insert a text string.

$date_col = "test";//

[cyberRobot]

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?

[gw1500se]

Yes, when it comes from an HTML page. That is any time you are inserting user provided data.

[Barand]

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' ");

 

[cyberRobot]

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.

 

[makamo66]

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);

 

[Barand]

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.

[maxxd]

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).

[benanamen]

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.

[makamo66]

The solution posted to a different forum was:

$sql = "INSERT INTO comment_table (name,date_col,website,comment) VALUES (?, ?, ?, ?)";
 

[Barand]

So you are one of those people who likes to waste people's time by posting simultaneously on multiple forums?

That will be remembered.