php - 我需要 DROP TABLE IF EXISTS 然后 CREATE TABLE 然后 LOAD DATA LOCAL INFILE

Question

我想使用以下场景:

DROP TABLE IF EXISTS then CREATE TABLE then LOAD DATA LOCAL INFILE.

错误

Can't insert portmailredirect_data.csv records : Table 'duncanho_csvtest.pmr_user_data' doesn't exist

代码:

<?php

define('HOST', 'localhost');
define('USER', 'duncanho_csvtest');
define('PASS', '271288Drh!');
define('DBNAME', 'duncanho_csvtest');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
    die("can not connect to the server!<br/>");
} else {
    $rdb = mysql_select_db(DBNAME);
    if (!$rdb) {
        die("The " . DBNAME . "database could not be selected");
    } else {

// insert to the student table query
        $sql = 'DROP TABLE IF EXISTS `pmr_user_data`';
        mysql_query( $sql, $connection );

$sql2 = 'CREATE TABLE `pmr_user_data` (
 `id` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 `customer_code` INT( 10 ) NOT NULL,
 `service` VARCHAR(30) NOT NULL,
 `primary_name` VARCHAR(50) NOT NULL,
 `secondary_name` VARCHAR(50) NOT NULL,
 `email` VARCHAR(50) NOT NULL,
 `start_date` DATE NOT NULL,
 `end_date` DATE NOT NULL,
 `letters_recieved` VARCHAR(50) NOscenarioT NULL, 
 `last_mail` VARCHAR(50) NOT NULL, 
 `reg_date` VARCHAR(50) NOT NULL, 
 )';
mysql_query( $sql2, $connection );

$insert_query = "   
        LOAD DATA LOCAL INFILE 'portmailredirect_data.csv' 
                            REPLACE INTO TABLE pmr_user_data 
                            FIELDS TERMINATED BY ','
                            LINES TERMINATED BY '\r\n'
                            IGNORE 1 ROWS
(customer_code,service,primary_name,secondary_name,email,start_date,end_date,letters_recieved,postage_fund,last_mail)
                ";

        if (!mysql_query($insert_query, $connection)) {
            echo "Can't insert portmailredirect_data.csv records : " . mysql_error($connection);
        } else {
            echo "You have successfully insert records into student table";
        }
    }
}
mysql_close($connection);
?>

感谢任何帮助。

Answer 1

您的“创建表”查询无法创建表，因为您没有提到自动增量列的主键。

$sql2 = 'CREATE TABLE `pmr_user_data` (
 `id` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 `customer_code` INT( 10 ) NOT NULL,
 `service` VARCHAR(30) NOT NULL,
 `primary_name` VARCHAR(50) NOT NULL,
 `secondary_name` VARCHAR(50) NOT NULL,
 `email` VARCHAR(50) NOT NULL,
 `start_date` DATE NOT NULL,
 `end_date` DATE NOT NULL,
 `letters_recieved` VARCHAR(50) NOT NULL, 
 `last_mail` VARCHAR(50) NOT NULL, 
 `reg_date` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
 )';

在上面的代码中，'PRIMARY KEY' 为第一个自增列定义。这应该有效。