在Mysql中导入CSV不起作用 [英] Import CSV in Mysql does not work
问题描述
我喜欢写一个csv导入/更新到我的Mysql数据库,但它不工作。我没有收到任何错误消息。
I like to write a csv import / update to my Mysql database, but it isnt working. I get no error messages.
任何人都可以帮我找到我的脚本错误或什么问题。
Can anybody help me to find the error or whats wrong with my script please.
// set local variables
$connect = mysql_connect("localhost","root","") or die('Could not connect: ' . mysql_error());
$handle = fopen("imptest.csv", "r");
// connect to mysql and select database or exit
mysql_select_db("shoptest1", $connect);
while($data = fgetcsv($handle, 30000, ';')) //Jede Zeile durchgehen
{
$Product_ID=$data[0];
$field=$data[1];
$query = 'SELECT Product_ID FROM testprod';
if (!$result = mysql_query($query)) {
continue;
} if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
// entry exists update
$query = "UPDATE ps_product_lang SET custom_field ='$field' WHERE id_product = '$Product_ID'";
mysql_query($query);
if (mysql_affected_rows() <= 0) {
echo "kein update";
// no rows where affected by update query
}
} else {
echo "kein eintrag";
// entry doesn't exist continue or insert...
}
mysql_free_result($result);
}
fclose($handle);
mysql_close($connect);
?>
推荐答案
您执行的查询:
SELECT Product_ID FROM testprod
UPDATE nl_product_lang SET custom_field = ? WHERE Product_ID = ?
适用于检测产品是否存在,然后是UPDATE或INSERT。对于只有UPDATE,SELECT没有关系,因为将不会有一个条目 WHERE Product_ID NOT IN(SELECT Product_ID from testprod)
- 如果你有一个外键。
are suitable to detect whether a product exists and then either UPDATE or INSERT. For only an UPDATE, the SELECT doesn't matter, as there won't be an entry WHERE Product_ID NOT IN (SELECT Product_ID FROM testprod)
- if you have a foreign key.
这是一个使用PDO如何做到这一点的例子。
Here's an example of how to do this using PDO.
list( $SQLDSN, $SQLUSER, $SQLPASS ) = [ 'mysql:dbname=shoptest1', 'root', '' ];
$db = new PDO( $SQLDSN, $SQLUSER, $SQLPASS, [
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
] );
$ids = $db->query( "SELECT Product_ID from testprod" )->fetchAll( \PDO::FETCH_COLUMN, 0 );
while ( list( $Product_ID, $field ) = fgetcsv(...) )
if ( in_array( $Product_ID, $ids ) )
query( $db, "UPDATE ps_product_lang SET custom_field = ? WHERE Product_ID = ?",
[ $field, $Product_ID ]
);
else
trigger_warning( "unknown product $Product_ID" );
function query( $db, $sql, $args = null ) {
$sth = $db->prepare( $sql );
$sth->execute( $sql );
return $sth;
}
这篇关于在Mysql中导入CSV不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!