PHP script to Update Mysql Database from .csv file

[zthomasz]

[FONT="Courier New"]

I'm new to PHP and I'm trying to update the status (status_id) of specified email addresses in a MYSQL database, using a csv file (test.csv) which holds the email addresses to be updated.

However, the script I"ve put together isn't working properly.

It not only updates the status_id of specified email addresses, but it also changes ALL the status_id's for ALL email addresses in the database:

---

<?php

// set local variables
$connect = mysql_connect("localhost","user","password") or die('Could not connect: ' . mysql_error());
$handle = fopen("/www/cgi-ssl/test.csv", "r");

// connect to mysql and select database or exit
mysql_select_db("report", $connect);

// loop content of csv file, using comma as delimiter
while (($data = fgetcsv($handle, 1000, ",")) !== false) {
$address = (int) $data[0];

$query = 'SELECT address FROM email';
if (!$result = mysql_query($query)) {
continue;
}

if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

// entry exists update
$query = "UPDATE email SET status_id ='2'
WHERE address=$address";

mysql_query($query);
if (mysql_affected_rows() <= 0) {

// no rows where affected by update query
}
} else {
// entry doesn't exist continue or insert...
}

mysql_free_result($result);
}

fclose($handle);
mysql_close($connect);

?>

---

Here are the contents of the csv file:

[e-mail]
[e-mail]
[e-mail]

---

This is the table info in the database I want to update:

+----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+-------+
| id | varchar(10) | | | | |
| status_id | varchar(10) | | | | |
| customer_id | varchar(10) | | | | |
| address | varchar(64) | | | | |
| flag | varchar(10) | | | | |
| format | varchar(10) | | | | |
+----------------+-----------------+------+-----+---------+-------+

[/FONT]

[gardnc]

Try this (enclose $address in single quotes):

// entry exists update
$query = "UPDATE email SET status_id ='2'
WHERE address='$address'";

And be sure $address contains the email address to be updated.

[zthomasz]

Thanks gardnc! I enclosed $address in single quotes, which resolved that problem.

// entry exists update
$query = "UPDATE email SET status_id ='2'
WHERE address='$address'";

There was also another problem. I deleted the (int) in $address = (int) $data[0]; and now it works!

For anyone who might need it, here's the entire working code ...

<?php

// set local variables
$connect = mysql_connect("localhost","user","password") or die('Could not connect: ' . mysql_error());
$handle = fopen("/www/cgi-ssl/test.csv", "r");

// connect to mysql and select database or exit
mysql_select_db("report", $connect);

// loop content of csv file, using comma as delimiter
while (($data = fgetcsv($handle, 1000, ",")) !== false) {
$address = $data[0];

$query = 'SELECT address FROM email';
if (!$result = mysql_query($query)) {
continue;
}

if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

// entry exists update
$query = "UPDATE email SET status_id ='2'
WHERE address = '$address'";

mysql_query($query);
if (mysql_affected_rows() <= 0) {

// no rows where affected by update query
}
} else {
// entry doesn't exist continue or insert...
}

mysql_free_result($result);
}

fclose($handle);
mysql_close($connect);

?>