mysql导入.csv权限拒绝,在Mysql中导入CSV不起作用

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

?>

解决方案

The queries you are performing:

SELECT Product_ID FROM testprod

UPDATE nl_product_lang SET custom_field = ? WHERE Product_ID = ?

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.

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;

}