Update MySql Table from CSV using PHP

Question

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.

DivinesLight · Accepted Answer · 2011-10-31 05:00:48Z

4

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

answered Oct 31, 2011 at 5:00

DivinesLight

2,4152 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kale Over a year ago

Hey @BlackDivine - why are you using 1000 for the length in that fgetcsv function? I'm trying to do the same thing that the original poster wanted to do and I'm curious. Thank you!!

DivinesLight Over a year ago

Because it's a safe limit, unless you have a line longer than 1k chars. Be aware that second parameter (1000 in this case) is not the length of whole file, but a single line. Quoting official php docs "Must be greater than the longest line (in characters) to be found in the CSV file (allowing for trailing line-end characters). It became optional in PHP 5. Omitting this parameter (or setting it to 0 in PHP 5.0.4 and later) the maximum line length is not limited, which is slightly slower. "

Rukmi Patel · Accepted Answer · 2011-10-31 05:01:47Z

1

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

answered Oct 31, 2011 at 5:01

Rukmi Patel

2,5619 gold badges29 silver badges42 bronze badges

Comments

lanisle · Accepted Answer · 2011-10-31 05:04:04Z

1

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

answered Oct 31, 2011 at 5:04

lanisle

213 bronze badges

Collectives™ on Stack Overflow

Update MySql Table from CSV using PHP

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related