From CSV To MySQL Table

[Valeria]

Hi, I've a CSV file with 3 columns

ID - Price1 - Price2

How I would like to update the records of a MySQL tables that has the
same ID of the CSV files.

How Can I do this? Read from CSV and update MySQL table?

Thanks

[purcaholic]

Re: From CSV To MySQL Table

Hi Valeria,

your idea/question to read from csv and update the table is the right
way.

Open your csv file and read each line using a loop. Check inside your
loop, if an entry in the database exists. if so update it, otherwhise
continue the loop or insert an entry.

See follwing pages for detailed information:
- http://de2.php.net/manual/en/function.fgetcsv.php
- http://de2.php.net/manual/en/ref.mysql.php or http://de2.php.net/manual/en/ref.mysqli.php

An simple example could be:
[snip]
$link = mysql_connect($ mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
$handle = fopen("myfile.c sv", "r");

// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle , 1000, ",")) !== false) {
$id = (int) $data[0];
$price = floatval($data[1]);
$price2 = floatval($data[2]);

$query = 'SELECT id FROM my_table';
if (!$result = mysql_query($qu ery)) {
continue;
}

if ($line = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
// entry exists update
$query = "UPDATE my_table SET price='$price', price2='price2'
WHERE id=$id";
mysql_query($qu ery);
if (mysql_affected _rows() <= 0) {
// no rows where affected by update query
}
} else {
// entry don't exists continue or insert...
}

mysql_free_resu lt($result);
}

fclose($handle) ;
mysql_close($li nk);
[/snap]

purcaholic

[Willem Bogaerts]

Re: From CSV To MySQL Table

your idea/question to read from csv and update the table is the right

way.
>
Open your csv file and read each line using a loop. Check inside your
loop, if an entry in the database exists. if so update it, otherwhise
continue the loop or insert an entry.

As an alternative, you can define a (temporary) table based on your CSV
file in MySQL (you'll have to upload the csv file to the database
server), and use an update statement. MySQL has a CSV engine for these
purposes. If this is a regular action and not a one-time only action,
you'll be better of using PHP though.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.

http://www.kratz.nl/

[Usenet]

Re: From CSV To MySQL Table

In article <46483396$0$791 39$e4fe514c@new s.xs4all.nl>, Willem Bogaerts
wrote:

If this is a regular action and not a one-time only action,
you'll be better of using PHP though.

Why so? I would have thought it would be the reverse.
Surely such a simple (looking) update as this would be quicker and more
efficient joining tables, even temporary ones, than in php?

Regards
Mark

[Willem Bogaerts]

Re: From CSV To MySQL Table

Why so? I would have thought it would be the reverse.

Surely such a simple (looking) update as this would be quicker and more
efficient joining tables, even temporary ones, than in php?

If you would do this from a CRON job or a PHP page, it needs the rights
to create a table. That is something I like to avoid. A one-time action
as an administrator is less dangerous, as you don't "run unattended"
yourself.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.

http://www.kratz.nl/

[Usenet]

Re: From CSV To MySQL Table

In article <46486a2c$0$337 $e4fe514c@news. xs4all.nl>, Willem Bogaerts wrote:

If you would do this from a CRON job or a PHP page, it needs the rights
to create a table. That is something I like to avoid. A one-time action
as an administrator is less dangerous, as you don't "run unattended"
yourself.

Ah, I see. Thank you for the explanation.

Regards
Mark