drxyaox153896
drxyaox153896
2015-07-06 15:01

将LOAD DATA INFILE MySQL UPDATE转换为php

已采纳

I have written a MySQL update statement which updates a table from a csv file. This command works perfectly when run from phpMyAdmin, but when trying to run it from php it fails. It says there is an error in my syntax.

This is my php code snippet

$file = "/root/location/to/file.csv";
$db = IEM::getDatabase();
$recover = "CREATE TEMPORARY TABLE temptable LIKE email_list_subscribers; LOAD DATA INFILE '$file' INTO TABLE temptable FIELDS TERMINATED BY ',' (emailaddress, subscriberid, confirmed); UPDATE email_list_subscribers INNER JOIN temptable on temptable.subscriberid = email_list_subscribers.subscriberid SET email_list_subscribers.confirmed = temptable.confirmed; DROP TEMPORARY TABLE temptable;";
$result = $db->Query($recover);

Any suggestions as to where I am going wrong?

the error message gets truncated to:

You have an error in your SQL syntax; check the manual that correspond...
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doumaqing6652 doumaqing6652 6年前

    Thanks to @spencer7593 suggestion. The Query function did not support multiple queries. Hence my answer was as follows:

    $file = "/root/location/to/file.csv";
    $db = IEM::getDatabase();
    $recover = "CREATE TEMPORARY TABLE temptable LIKE email_list_subscribers;";
    $result = $db->Query($recover); 
    $recover ="LOAD DATA INFILE '$file' INTO TABLE temptable FIELDS TERMINATED BY ',' (emailaddress, subscriberid, confirmed);";
    $result = $db->Query($recover); 
    $recover = "UPDATE email_list_subscribers INNER JOIN temptable on temptable.subscriberid = email_list_subscribers.subscriberid SET email_list_subscribers.confirmed = temptable.confirmed;";
    $result = $db->Query($recover);
    $recover = "DROP TEMPORARY TABLE temptable;";
    $result = $db->Query($recover);
    
    点赞 评论 复制链接分享

相关推荐