CODEWITHSUNDEEP
phpmysql
LuckyCoder
LuckyCoder

Reputation: 520

Update MySql Table from CSV using PHP

I need a PHP script which will update existing MySQL table getting data from a CSV. Table field hasweb need to be updated comparing a field consultant_id.

So MySql query should be 

UPDATE user_data 
SET hasweb="something" 
WHERE consultant_id = "something";

Please help me to write a PHP script which can execute this query as many times as needed, depending upon CSV data.

Upvotes: 1

Views: 9806

Answers (3)

lanisle
lanisle

Reputation: 21

see this function: http://www.php.net/manual/zh/function.fgetcsv.php

Upvotes: 1

Rukmi Patel
Rukmi Patel

Reputation: 2561

You can use php function fgetcsv.

you can get all data from CSV file into php by using this function. for example,

$row = 1;
if (($handle = fopen("your_file.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        $SQL = 'UPDATE table_name SET col1=val1 WHERE con1';
        execute($SQL); 
    }
}
fclose($handle);
}

Upvotes: 1

DivinesLight
DivinesLight

Reputation: 2415

I have written little php scripts to accomplish this many times and there are many ways go to about it:

The best according to my experience is to use CSV functions provided by PHP, take a look at fgetcsv(), because manually opening file and reading it line by line and parsing can cause complications.

Now you just loop through all the rows in csv and prepare query dynamically and execute it, for example (assuming that column 0 has IDs and column 1 has "hasweb")

<?php
    if (($handle = fopen("input.csv", "r")) !== FALSE)
    {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
        {
            mysql_query(UPDATE user_data SET hasweb="{$data[1]}" WHERE consultant_id = "{$data[0]}"); 
        }
    fclose($handle);
    }
?>

Hope that helps. If still stuck, Please ask me :)

Upvotes: 4

Related Questions