CODEWITHSUNDEEP
phpmysqlcsv
Steven Marrocco
Steven Marrocco

Reputation: 121

PHP - insert into mutiple tables, each table with identifcal fields

I'm going to explain with my best efforts what my goal is here. Everything I've searched for online hasn't been relevant enough for me to gain an idea.

First off, this is a PHP assignment where we have to load CSV files into a MySQL database.

Now, each table (total of 4) have the exact same field values. What I am trying to accomplish is using a for each loop that populates each table with the information from the CSV file. I know I can do this by having a while loop for each table and CSV file but I'm trying to go above the requirements and learn more about PHP. Here is my code for what I'm trying to accomplish:

$files = glob('*.txt'); // .txt required extension 
foreach($files as $file) {

    if (($handle = fopen($file, "r")) !== FALSE) {
        while (($data = fgetcsv($handle,4048, ",")) !== FALSE) {
            echo $data[0]; // making sure data is correct
            $import = "INSERT INTO".basename($file)."(id,itemName,price) VALUES('$data[0]','$data[1]','$data[2]')";
            multi_query($import) or die (mysql_error());
        }
        fclose($handle);
    }
    else {
        echo "Could not open file: " . $file;
    }
}

Each CSV file contains the id, itemName and price. Hopefully this is understandable enough. Thank you

Upvotes: 2

Views: 66

Answers (1)

Hyder B.
Hyder B.

Reputation: 12296

The way you are importing data into MySQL is OK for small volume of data. However, if you are importing huge volumes(thousands of rows), the best way would be to import it directy into MySQL is by using infile. Fo example:

LOAD DATA LOCAL INFILE '/path/to/your_file.csv' 
INTO TABLE your_table_name 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES 
TERMINATED BY '\n' (id, itemName, price)

That's a smarter way to import your CSV data :)

Upvotes: 1

Related Questions