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