INSERT into Table On Columns That Exist

Question

Suppose I have a very large array of information for a user:

$user=array(
"name"=>"john",
"ip"=>"xx.xx.xx.xx",
"email"=>"john@something.com",
//lots more values
)

Let's also suppose that this information needs to go into more than one table. For instance a username needs to go table users, address needs to go into a details table, etc.

Now, I use a certain self-made function to insert into my tables that matches array keys to column names and array values to the values being inputted. Something similar to this:

function insert_sql($table, arr $values){
    GLOBAL $dbc;
    $sql="INSERT INTO `$table` (".implode(array_keys($values), ", ").") VALUES (".implode(array_values($values), ", ").")";
    $dbc->prepare($sql)->execute();
    return $dbc->lastInsertId();

}
//I don't actually use this function, just trying to show you what is being accomplished.

The problem is that my function uses all the keys and all the values, so when I just need certain parts of the array put into multiple tables, it doesn't work.

The question is:

How do I make an INSERT statement ignore a column if it doesn't exist? So if I insert name,email,address, into table users, but this table doesn't have an address column, I need it to insert the row with the name and email but simply ignore the fact that the address column is not there.

EDIT: The other option is to make an array with the columns of a table and use it to filter the values array. Although I am not really sure how to set this up.

b.b3rn4rd · Accepted Answer · 2013-01-10T01:05:19.253

1

Find given table column names:

SELECT 
column_name
FROM 
information_schema.columns
WHERE 
table_name =  'tablename'

And then just whitelist your keys in $values array

Example:

function insert_sql($table, array $values){
        global $connection;
        $query = "SELECT column_name FROM information_schema.columns WHERE table_name =  :tablename";
        /* @var $stmt PDOStatement */
        $stmt  = $connection->prepare($query);
        $stmt->execute(array(
            'tablename' => $table
        ));
        $columns = array_flip($stmt->fetchAll(PDO::FETCH_COLUMN, 0));
        $values  = array_intersect_key($values, $columns);
        var_dump($values);
    }

edited Jan 10 '13 at 01:05

answered Jan 10 '13 at 00:41

b.b3rn4rd

8,494
2
45
57

Thanks! I have the columns as values of array $columns. What's the best way to filter the $values based on the $columns (remember the keys of array $values are the columns). – kmoney12 Jan 10 '13 at 01:00
Nevermind, I found this answer for that: http://stackoverflow.com/questions/4260086/php-how-to-use-array-filter-to-filter-array-keys – kmoney12 Jan 10 '13 at 01:02
@hellohellosharp I've provided an example, using `array_intersect_key` but array_filter is also fine – b.b3rn4rd Jan 10 '13 at 01:05

score 1 · Answer 2 · answered Jan 10 '13 at 00:43

1

How do I make an INSERT statement ignore a column if it doesn't exist? So if I insert name,email,address, into table users, but this table doesn't have an address column, I need it to insert the row with the name and email but simply ignore the fact that the address column is not there.

You can't

Instead you should map your data to the appropriate tables with separate inserts.

answered Jan 10 '13 at 00:43

Sure, separate INSERTS can be used, I guess I am just looking for a way to dynamically split the array then to have the right values. – kmoney12 Jan 10 '13 at 00:45

INSERT into Table On Columns That Exist

2 Answers2

Linked