使用PHP从CSV更新MySql表 [英] Update MySql Table from CSV using PHP

问题描述

我需要一个PHP脚本，该脚本将更新现有的MySQL表以从CSV获取数据.表字段hasweb需要与字段consultant_id进行比较.

因此MySql查询应为

UPDATE user_data 
SET hasweb="something" 
WHERE consultant_id = "something";

请帮助我编写一个PHP脚本，该脚本可以根据CSV数据根据需要多次执行此查询.

解决方案

我已经写了一些小的php脚本来完成多次，并且有很多方法可以解决这个问题:

根据我的经验，最好的方法是使用PHP提供的CSV函数，请查看 fgetcsv()，因为手动打开文件并逐行读取文件并进行解析会引起复杂性.

现在，您只需循环浏览csv中的所有行并动态准备查询并执行它，例如(假设第0列具有ID，而第1列具有"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);
    }
?>

希望有帮助.如果仍然卡住，请问我:)

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.

解决方案

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

这篇关于使用PHP从CSV更新MySql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！