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.

score 11 · Accepted Answer · answered Jun 02 '09 at 05:10

11

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

answered Jun 02 '09 at 05:10

segfault

5,759
9
45
66

1

Going from 0 to writing that code is a nice accomplishment. Congratulations. – Peter Lange Jun 02 '09 at 15:30
Thanks Cannonade and Goblyn. It always feel great to learn something new! – segfault Jun 03 '09 at 03:25
1

The SQL-Syntax is outdated in recent versions of PHP. E.g. https://www.php.net/manual/de/function.mysql-query.php – meles Apr 25 '20 at 21:04

score 3 · Answer 2 · answered Jun 02 '09 at 05:10

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

score 3 · Answer 3 · answered Jun 02 '09 at 06:02

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.

score 1 · Answer 4 · answered Jun 02 '09 at 05:02

1

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.

answered Jun 02 '09 at 05:02

Peter Lange

2,786
2
26
40

Hey man, thanks, actually I've figured out myself. Realized that PHP is pretty simple. – segfault Jun 02 '09 at 05:12

score 0 · Answer 5 · edited Apr 13 '13 at 03:49

<?php
include ("dbsanjog.php");
$uploadedfile=$_REQUEST['uploadedfile'];
  $target_path = "dataupload/";
  $user=$_REQUEST['username'];
$target_path = $target_path.$user.".csv"; 

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) 
{
    echo "The file ".  basename( $_FILES['uploadedfile']['name']). "has been uploaded";
} 

//echo $target_path; die;
//$row = 1;
$handle = fopen($target_path, 'r');
//$sql="update agents_det set where user='$user'";


while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
{
    $query = "UPDATE agents_det SET contact='$data[3]' WHERE user='$user'";   

}

fclose($handle);
//mysql_query($test);
//echo ($test[1]); die;
//echo $test; die;


?>

score 0 · Answer 6 · answered Jun 02 '09 at 05:08

0

Goblyn27 is correct. Take a look at fgetcsv() and mysql_query() on php.net. There are even examples in the docs for how to do this.

answered Jun 02 '09 at 05:08

Vic

1,336
11
30

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

6 Answers6

Linked