Writing a PHP file to read from CSV and execute SQL Query

Question

I would like a PHP script to read the contents from a CSV file in the following format

id, sku
1,104101
2,105213

there are a total of 1486 entries, I believe that it's better to use a for loop instead of while !EOF.

After that, I would like to perform SQL query on a database named m118, table catalog_product_entity.

The query would be like UPDATE sku=$csvSku WHERE id=$csvId

Being a novice at both PHP and MySQL, I do not know where to start coding.

Solution 1

fgetcsv can be used to parse CSV files. mysql_query method could be used to perform MySQL queries.

The complete code is as follows:

<?php
$fin = fopen('catalog_product_entity.csv','r') or die('cant open file');
$link = mysql_connect('localhost', 'm118', 'pw');
If (!$link) {
    die ('Could not connect: ' . mysql_error());
}
@mysql_select_db('m118') or die ('Unable to select database');
echo "Connection succeeded <br />\n";
while (($data=fgetcsv($fin,1000,","))!==FALSE) {
    $query = "UPDATE catalog_product_entity SET sku='$data[1]' WHERE entity_id='$data[0]'";
    mysql_query($query);
    echo "Record updated <br />\n";
    }
fclose($fin);
mysql_close();
?>

Solution 2

Well done on learning :)

You should perhaps now learn about PDO in PHP, as it's (in my opinion) the best, safest and fastest way to execute MySQL queries in PHP:

<?php
$fin = fopen('catalog_product_entity.csv','r') or die('cant open file');
try {
    $link = new PDO('mysql:dbname=m118;host=localhost', 'm118', 'pw');
    echo 'Connection succeeded <br />' . PHP_EOL;
    $stmt = $db->prepare('UPDATE catalog_product_entity SET sku = :sku WHERE entity_id = :id');
    //Only give the length parameter if you **KNOW** that no line will be longer than that
    while (($data=fgetcsv($fin,1000,","))!==FALSE) {
        if ($stmt->execute(array(':sku' => $data[1], ':id' => $data[0]))) {
            echo 'Record updated <br />' . PHP_EOL;
        } else {
            $err = $stmt->errorInfo();
            echo 'Update Failed: ' . $err[2] . PHP_EOL;
        }
    }
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
fclose($fin);

The PDO script has the following advantages over yours:

• It's safer: PDO automatically quotes the data being inserted as needed. This prevents SQL injection attacks.
• It's faster: PDO caches the query (in the prepare), and then uses the parameters passed in execute.
• It's portable: PDO can connect to various types DB's, not just MySQL, so if you need to switchs DB's, its much easier.

Solution 3

You will need to do something like:

$filename = "file_name_on_server.csv"
$fp = fopen( $filename ,"r");
while ($line = fgets ($fp))
{

now use Split to get an array of the comma delimited values

    $arr = split (",", $line);

Now you have an array for the comma delimited values on $line. You can do simple string formatting to stick those values into an SQL query.

    $query = "INSERT INTO `TableBlah` (Field1, Field2) VALUES (" . $arr[0] . "," . $arr[1] . ")";

Use the mysql api to send those queries to the database

}
fclose($fp);

Solution 4

Ok man, I wont write the code for you because you wont learn if I do. But I will point you in the right direction. Check out this link http://us.php.net/fgetcsv for information on parsing a CSV file in PHP. A simple google search should also give you the necessary information on entering it into a MySQL table.

View more solutions

Comments

[segfault]

Hey man, thanks, actually I've figured out myself. Realized that PHP is pretty simple.

[Peter Lange]

Going from 0 to writing that code is a nice accomplishment. Congratulations.

[segfault]

Thanks Cannonade and Goblyn. It always feel great to learn something new!

[meles]

The SQL-Syntax is outdated in recent versions of PHP. E.g. php.net/manual/de/function.mysql-query.php