Will using the php function "insert into" create new columns if they aren't already defined, or return an error?

Question

I'd like to use a foreach loop to insert a _POST associative array into a database using INSERT INTO.

What I need to know, is if my _POST data contains keys that don't match the columns of my database, will INSERT INTO simply create these columns in the database?

if not, will it return an error and not update my database at all? or will it return an error but still update my database with as many valid entries as possible?

I'd prefer the last situation. This is what I'm looking to do:

A user fills out a form with mulitple fields -> I do something with the responses in _POST, maybe some calculations in javascript, or send an email -> I store some, but not all, of the fields in my database for future reference, looping through the array and storing everything that matches seems to be the simplest way to do this.

Ideally, I'd like to be able to reuse this code in different situations so here I'm looking to gain a more detailed and subtle understanding of how INSERT INTO operates.

Answer 1

It will error and the insert will fail.

Answer 2

INSERT INTO will insert the values into fields that already exist, if they don't exist, an error occurs. If you insert one record per query , then if one row cannot be inserted, the next row will.

A warning though: You should look into SQL Injections. Taking data sent by the user and not validating or escaping it is very dangerous. A malicious user could easily delete all records in your database, or retrieve sensitive user data if you are not careful.

There are lots of good questions on SQL Injections on Stack Overflow and on the Internet in general.

Answer 3

Attempting to insert into columns that do not exist will cause an error, and no row will be inserted.

Why not just define an associative array in your PHP file that lists the columns in your DB, and then check to see if each field is a member of that array before including it in the INSERT?

$allowed_columns = array(
   "name" => 1,
   "phone" => 1,
   "address" => 1
);

foreach($_POST as $key => $val) {
    if(!isset($allowed_columns[$key])) continue;
    //otherwise, add this field after properly
    //validating/escaping the contents of $val
}