Jump to content
makamo66

Getting the error Undefined index but the column exists

Recommended Posts

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.
 

Share this post


Link to post
Share on other sites

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 by gw1500se

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Edited by gw1500se

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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' ");

 

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

The solution posted to a different forum was:

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

Share this post


Link to post
Share on other sites

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

That will be remembered.

  • Great Answer 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.