updating a MySQL table from CSV file

d-woo

I have a query that I'm using in phpMyAdmin to update a field "order_status" in a table "orders" by defining the field "order_id"

Problem/Challenge:

I would to change the order_status on many different order_id records and would like to use a CSV file where the various order_id values are listed on separate lines like:

"UPDATE orders SET order_status='S' WHERE order_id IN ($order_id_string) ";

and the string of order_id values might be:
0875
0125
0354
8514
.
.
.

Question: How would the query look and can this be done from phpMyAdmin or should it be executed from a php file?

Anon

UPDATE orders
SET order_status='S'
WHERE order_id IN ('0875', '0125', '0354', '8514', ...)

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

d-woo

Thanks Michael...That gets me one step closer.

How do I get those numerical values to read from a text file?

Anon

To be honest, I regularly don't use phpMyAdmin for this kind of work. If this was a once-off situation, I would just do the update semi-manually (ie: use a good editor to read in the data file and regexp it into an UPDATE). If this was something I was going to have to do on a regular basis, I would whip together a quick SQL script to automate the work (ie: LOAD DATA into temp_table, SELECT 'UPDATE ...' from temp_table, execute output of SELECT). Doing the equivalent of a SQL script in PHP would be pretty straight forward:

// Suck the whole file into an array called $data
$data = file("/your/data/file/here.txt", "r");
$rowcount = count($data) - 1; // We want relative to 0 not 1
for ($rownum=0; $rownum<=$rowcount; $rownum++)
{
$thisorder = chop($data[$rownum]);
// Update order status to 'S'
// database call to UPDATE orders SET order_status='S' WHERE order_id = $thisorder;
}

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com