CSV import to MySQL using PHP

Question

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

I have two tables: users and balance.

users
ID|user_login|
1 |name1 |
2 |name2 |
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!

Possible duplicate of [MySQL, update multiple tables with one query](http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query) — Frederic Klein, Sep 29 '16 at 11:38
This question is too broad and also unclear. On the one hand there is the data load. No mention of what to do with inserts versus updates of missing first table data. Then there is the update with a join. Some could swing for `LOAD DATA INFILE` (my approach). Others go the slow route (for large tables) and have PHP File I/O. — Drew, Sep 29 '16 at 17:24
Please don't add an answer to your question. Use the Answer box instead (if your question gets reopened). — Patrick Hofman, Sep 30 '16 at 08:57
I don't think this is the same question, because question not only about MySQL query but about CSV import in DB. — Hazzy, Sep 30 '16 at 09:03
@PatrickHofman I know but I didn't find that box. Because of question on the hold? — Hazzy, Sep 30 '16 at 09:14
Indeed. It has to be reopened first, so that would need work on your question. — Patrick Hofman, Sep 30 '16 at 09:15

score 4 · Accepted Answer · answered Sep 29 '16 at 12:34

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']

score 1 · Answer 2 · answered Sep 29 '16 at 11:37

1

You can try this Update Statement:

UPDATE balance b 
JOIN users u ON u.ID = b.user_id
SET b.user_balance = '@balance'
WHERE u.user_login = '@name'

Where @balance and @name are values from the CSV.

answered Sep 29 '16 at 11:37

Bojan B

2,091
4
18
26

CSV import to MySQL using PHP

2 Answers2