使用php从基于mysql值的csv文件中获取特定数据

问题描述：

hi
so this is the setup: i need to update some prices from a csv file called pricelist.csv. the database table is called products and there is a column called product_id, which contains the product ids which can also be found in the first column of the csv file and the prices and lastly i need are located in the 7th column of the csv file. i need to write these to the price column of my database.
i have tried my best to come up with the code, but it just seems too much for my skill level. here is what i made:

<?php  
include("admin/include/db.php");  
$res=mysql_query("select * from products");  
$row = 1;  
$mycsvfile = array(); //define the main array.  
if (($handle = fopen("pricelist.csv", "r")) !== FALSE) {  
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
    {  
        $num = count($data);  
        $row++;  
        $mycsvfile[] = $data;  
    }  
    fclose($handle);  
}  

$row['product_id'] = $mycsvfile[$which_row][1] //trying to find the row in the csv  
$mycsvfile[$which_row][7] = $price; //should get price, but previous line does not work   

while($row=mysql_fetch_array($res))  
{  
    mysql_query("update products set price='".$price."', isavailable='1' where id='".$row['id']."'");  
}  
?>

any sort of help is welcome! thanks

答

I think you are looking for this (but I didn't test it):

<?php
include("admin/include/db.php");

if( ( $handle = fopen("pricelist.csv", "r") ) !== FALSE )
{
    while( ( $r = fgetcsv( $handle, 1000, ";") ) !== FALSE )
    {
        mysql_query('UPDATE products SET "price"="'.$r[6].'", "isavailable"="1" where "id"="'.$r[0].'"');  
    }
}

Disclaimer: Yes I know I didn't sanitize the data, but I don't feel like working with outdated mysql functions.

答

You can use file() to read the CSV file into an array. Then use str_getcsv() to read each item from the file array and turn it into an array reprensnting a row from the CSV file. Then you pull the data from that array into an update query and run it.

Like this:

$id = 0;
$price = 6;

$csv_file = file('pricelist.csv');

foreach($csv_file as $row)
{
    $data = str_getcsv($row);
    $query = "UPDATE products SET price = {$data[$price]}, isavailable='1' WHERE `id` = {$data[$id]}";
    //run the query
}

使用php从基于mysql值的csv文件中获取特定数据

相关推荐