Getting the error Undefined index but the column exists

General PHP Help
#1

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.

#2

Use prepared statements, https://www.w3schools.com/php/php_mysql_prepared_statements.asp

That is first and foremost. Fix that and come back.

#3

You created the column as datetype. It is not going to accept a string.

#4

First thing first: You should do some error handling on mysqli_query(). Take a look on php.net and read the return section of mysqli_query and the examples below.

#5

if you get an “undefined index” error, the easiest is to look at the surrounding array

var_dump($comr);

The array key still may be unavailable if you don’t get any data.

#6

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

No you didnt.

Why? A date is not a varchar, it is a date.

Pretty much the entire code block is no good. I would highly recommend you use PDO with Prepared Statements. Here is a tutorial to get you going.

#8

The solution posted to a different forum was:

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

#9

And that solution uses prepared statements with the correct number of parameters.

Yours is not a prepared statement, even though you say it will be. Since there are not parameters passed in for the prepared statement, you end up not matching the count of values it is expecting.

Sponsor our Newsletter | Privacy Policy | Terms of Service