CSV to MYSQL query plus variables?

[gorgon2k]

Ok so I have this script which will take a propery formated .csv file and upload to my database. here's the part i need to know if it's possible. basically it puts a group of codes into the database but i need a field that will specify which user the code pertains to from another table. The most logical way to reference the user is with an id code; but short of having the user reference an id code to put in the csv file, is there anyway I can make a query that will INSERT INTO and upload the csv? here's my current code...

 

<?php

$tmpName = $_FILES['song_codes']['tmp_name'];

include('database.php');

if($sc != ""){

mysql_query('LOAD DATA LOCAL INFILE \''.$tmpName.'\' INTO TABLE music_codes FIELDS ENCLOSED BY "\"" TERMINATED BY "," LINES TERMINATED BY "\n" ;') or die('Error loading data file.<br>' . mysql_error()); 

[gorgon2k]

bump, any ideas?

[nethnet]

Is the issue that you don't want the users to have to bother adding a user ID to the CSV file which would be put into the database?  Or do you simply NOT want a user ID to be in the CSV file altogether?

 

If it's the former, then you could use the filesystem functions to open the CSV file and append a user ID to each line before your LOAD DATA statement.

 

More on Filesystem Functions from php.net

 

[mikosiko]

if you have a clear way to determine the user_id that you need to add in each line of your CSV file you can use the SET clause in the LOAD INFILE sentence to populate the field either as a calculated value or obtaining it with an scalar subquery... read the mysql manual for LOAD INFILE clause... specifically the part of the SET clause.

 

hope this help