Hazzy Hazzy - 2 months ago 9
MySQL Question

CSV import to MySQL using PHP

Trying to update tables with PHP and CSV import to MySQL DB. And I need some help.

I have two tables: users and balance.


  1. users

    ID|user_login|

    1 |name1 |

    2 |name2 |

  2. balance

    user_id|user_balance|

    1 |1000 |

    2 |2000 |



Query for operate:

SELECT users.user_login, balance.user_balance
FROM `users` , `balance`
WHERE `ID` = `user_id`


CSV file have this structure:

name1;3000

name2;5000


But I don't know how to update that two connected tables with ID and user_id form CSV.

I will be glad for any help and advice. Thanks!

Answer

First you get the data from CSV file.

$row = 1;
if (($handle = fopen("data.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

Then do update query.

UPDATE balance b  JOIN users u ON u.ID = b.user_id
SET b.user_balance = $data['balance']
WHERE u.user_login = $data['user_login']