• PHP Help

  • Getting the error Undefined index but the column exists

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.

(Added [code]...[/code] tags ~ MOD)

    All I can suggest at this point (not seeing anything obvious) is to add some debug code to see if it points to where the problem is. E.g., right before the line the line that raises the error, maybe do a test and display what's in that array:

    while($comr = mysqli_fetch_assoc($comres)) {
  // temporary debug code:
  if(empty($comr['name']) {
    die("<pre>ERROR: no 'name' key:\n".var_export($comr, 1)."</pre>");
  }
  // . . .
}

    If the error shows up and you find out it's not a case of the data being there but with some typo (case-sensitive), then start moving up debug stuff earlier up in the code until you find where it's breaking. 🤷

      Hmm. Have you tried it with "NOW()"?

      mysqli_query($connection, "INSERT INTO comment_table (name, date_col) VALUES ('$name', NOW())");

      If that works, then the issue is making sure you're actually sending a correctly-formatted DATETIME. That would be something like date('Y-m-d H:i:s') in PHP.

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

        Timestamp columns are kind of picky about the date/time string you use. If you want to use the current time, I'd recommend just using the NOW() SQL function. Also, if you use bound parameters, then you use place-holders for them in the SQL string, not the actual variables.

        $sql = "INSERT INTO comment_table (name, date_col, website, comment) VALUES (?, NOW(), ?, ?)";
if($stmt = mysqli_prepare($connection, $sql)){
  mysqli_stmt_bind_param($stmt, 'sss', $name, $website, $comment);

        Also, please wrap your code examples here in [code]...[/code] tags.

          Thank you, NogDog. That solved the problem perfectly.

            Write a Reply...