Uploading a csv using php into MySQL and update as well

Question

Ok so i have a database table called requests with this structure

mysql> desc requests;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| artist     | varchar(255) | YES  |     | NULL    |                |
| song       | varchar(255) | YES  |     | NULL    |                |
| showdate   | date         | YES  |     | NULL    |                |
| amount     | float        | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Here is some example data

+----+-----------+-------------------------+------------+--------+
| id | artist    | song                    | showdate   | amount |
+----+-----------+-------------------------+------------+--------+
|  6 | Metallica | Hello Cruel World       | 2010-09-15 | 10.00  |
|  7 | someone   | Some some               | 2010-09-18 | 15.00  |
|  8 | another   | Some other song         | 2010-11-10 | 45.09  |
+----+-----------+-------------------------+------------+--------+

I need a way to be able to give user a way to upload a csv with the same structure and it updates or inserts based on whats in the csv. I have found many scripts online but most have a hard coded csv which is not what i need. I need the user to be able to upload the csv...Is that easy with php....

Here is an example csv

id  artist          song           showdate    amount
11  NewBee          great stuff    2010-09-15  12.99
10  anotherNewbee   even better    2010-09-16  34.00
6    NewArtist       New song       2010-09-25  78.99

As you can see i have id 6 which is already in the database and needs to be updated..The other two will get inserted

I am not asking for someone to write the whole script but if i can get some direction on the upload and then where to go from there....thanks

mahesh · Accepted Answer · 2010-11-01 04:51:51Z

2

Create store procedure as below and test it. It is works

CREATE proc csv

(
@id int,
@artist varchar(50),
@songs varchar(100),
@showdate datetime,
@amount float
)
as
set nocount on

if exists (select id from dummy1 where id=@id) -- Note that dummy1 as my table.

begin
update dummy1 set artist= @artist where id=@id
update dummy1 set songs=@songs where id=@id
update dummy1 set showdate=@showdate where id=@id
update dummy1 set amount=@amount where id=@id
end
else
insert into dummy1  (artist,songs,showdate,amount)values(@artist,@songs,@showdate,@amount)
Go

edited Nov 1, 2010 at 4:51

answered Nov 1, 2010 at 4:43

mahesh

1,39010 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2023-11-14 22:25:35Z

1

upload the file to a directory using move_uploaded_file
use fgetcsv to read the uploaded csv and process each row as you like.
delete the csv file

edited Nov 14, 2023 at 22:25

CommunityBot

11 silver badge

answered Nov 1, 2010 at 5:10

subroutines

1,4681 gold badge12 silver badges17 bronze badges

Collectives™ on Stack Overflow

Uploading a csv using php into MySQL and update as well

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related