set conditions while uploading from csv file

Question

so, I have this users list which I get it from a csv file.

What I need to do is this: I get the file to upload, I check for each line if the user exists, if he does then all I do is update him, if he doesn't I should create a new account for him.

I do know how to do it as a logic... but I become confused while using the csv import...

So, here it is what I use:

$handle = fopen($_FILES['filename']['tmp_name'], "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $import="INSERT into users (Username,Password,Email,first_name,last_name,phone,user_id,age,sex) values('$data[0]','".md5($data[1])."','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')";
    mysql_query($import) or die(mysql_error());
}

fclose($handle);

What I need is something like this:

foreach (user as $data[7]) {
    if user exists in user table then $query1 
    else $query2    
}

Thanks

You have wrote the logic in pseudo code. Lookup `SELECT` to check if an user exists. Then just write the two queries. — Ed Heal, Jan 04 '13 at 12:17
could you please help me with something visual? because i know i wrot eit in pseudocode, but i don't know the syntax, starting from the part in foreach section... — Alb M, Jan 04 '13 at 12:18
[**Please, don't use `mysql_*` functions in new code**](http://stackoverflow.com/a/14110189/1723893). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — NullPoiиteя, Jan 04 '13 at 12:21
@AlbM - You managed to write the `insert` statement. I therefore inferred you know a little about SQL. So why not go the extra mile with `select`? — Ed Heal, Jan 04 '13 at 12:22
Yes Ed.. i mean, i could do that.. i am more worried about the syntax at the foreach part, if i should do it that way? — Alb M, Jan 04 '13 at 12:25

Lorenzo L · Answer 1 · 2013-01-04T15:16:35.897

If user_id is primary key of your table you can use the ON DUPLICATE KEY UPDATE clause to insert new row or update existing ones in just one statement:

INSERT INTO users (Username,Password,Email,first_name,last_name,phone,user_id,age,sex)  
VALUES('$data[0]','".md5($data[1])."','$data[2]','$data[3]',
       '$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')
ON DUPLICATE KEY UPDATE
        Username = '$data[0]', 
        Password = '".md5($data[1])."', 
        Email = '$data[2]', 
        first_name = '$data[3]', 
        last_name = '$data[4]', 
        phone = '$data[5]', 
        age = '$data[7]', 
        sex = '$data[8]';

Check details in MySQL documentation: http://dev.mysql.com/doc/refman/5.6/en/insert.html

score 0 · Accepted Answer · answered Jan 04 '13 at 16:03

MySQL has a handy little function called LOAD DATA INFILE, which allows it to load CSV files directly, without you needing to do the insert loop in PHP.

You entire code could be as simple as this:

$import="LOAD DATA INFILE '{$_FILES['filename']['tmp_name']}' INTO users (Username,Password,Email,first_name,last_name,phone,user_id,age,sex)";
mysqli_query($connection, $import) or die(mysqli_error($connection));

See the MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

note: I haven't tested the above. If MySQL doesn't have direct read access to the temp file, you may need to move it to a location the MySQL can read first, or use LOAD DATA LOCAL INFILE instead.

note2: I'm using mysqli_xxx() functions instead of mysql_xx() because the mysql_xx() functions are deprecated. Feel free to change it back if you need to, but be aware that mysql_xx() is not recommended for use. I suggest you change all your mysql_xx() calls to mysqli_xxx() as soon as convenient.

set conditions while uploading from csv file

2 Answers2