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.
如果列不存在,如何使INSERT语句忽略该列?因此,如果我将名称,电子邮件,地址插入表用户,但此表没有地址列,我需要它插入带有名称和电子邮件的行,但只是忽略了地址列不存在的事实。
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.
编辑:另一个选项是使用表的列创建一个数组,并使用它来过滤值数组。虽然我不确定如何设置它。
2 个解决方案
#1
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
然后在$ values数组中将密钥列入白名单
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);
}
#2
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.
如果列不存在,如何使INSERT语句忽略该列?因此,如果我将名称,电子邮件,地址插入表用户,但此表没有地址列,我需要它插入带有名称和电子邮件的行,但只是忽略了地址列不存在的事实。
You can't
Instead you should map your data to the appropriate tables with separate inserts.
相反,您应该将数据映射到具有单独插入的相应表。
#1
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
然后在$ values数组中将密钥列入白名单
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);
}
#2
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.
如果列不存在,如何使INSERT语句忽略该列?因此,如果我将名称,电子邮件,地址插入表用户,但此表没有地址列,我需要它插入带有名称和电子邮件的行,但只是忽略了地址列不存在的事实。
You can't
Instead you should map your data to the appropriate tables with separate inserts.
相反,您应该将数据映射到具有单独插入的相应表。