upload csv file to mysql

[mautorrejon]

Im trying to upload a file to mysql using php and for some reason it doesnt work...could anyone help please..thanks in advance //Here goes the db connection //check for file upload if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['spredSheet'])){ //upload directory $upload_dir = "csv_dir/"; //create file name $file_path = $upload_dir . $_FILES['csv_file']['name']; echo ($_FILES['csv_file']); //echo "asd"; //move uploaded file to upload dir if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) { //error moving upload file echo "Error moving file upload"; } //open the csv file for reading $handle = fopen($file_path, 'r'); //turn off autocommit and delete the product table mysql_query("SET AUTOCOMMIT=0"); mysql_query("BEGIN"); mysql_query("TRUNCATE TABLE $table") or die(mysql_error()); while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) { //Access field data in $data array ex. $name = $data[0]; $quantity = $data[1]; //Use data to insert into db $sql = sprintf("INSERT INTO product_table (product_name, product_quantity) VALUES ('%s',%d)", mysql_real_escape_string($name), $quantity ); mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql")); } //commit the data to the database mysql_query("COMMIT"); mysql_query("SET AUTOCOMMIT=1"); //delete csv file unlink($file_path); }

[justsomeguy]

What happens when you run that?

[mautorrejon]

I get 0 on the page...I tried print_r($_FILES[''csv_file]) and nothing is printed....

[justsomeguy]

Use print_r($_FILES) instead, maybe you have the wrong name. Check that your upload form is correct to allow file uploading, specifically the enctype of the form. Add some else statements to your if statements to print messages in case of a problem.

[mautorrejon]

when I do print_r($_FILES) I get Array( )..this is my compromise code for this particular problem, please let me know what im doing wrong. Thanks handler.php echo '<form method="post" action="upload.php" enctype="multipart/form-data">';echo '<input type="file" name"csv_file" id="csv_file">';echo '<input type="submit" name="submit" value="Upload CSV File">';echo '</form>'; upload.php if ($_FILES["csv_file"]["error"] > 0){echo "Error: " . $_FILES["csv_file"]["error"] . "<br />";}else{echo "Upload: " . $_FILES["csv_file"]["name"] . "<br />";echo "Type: " . $_FILES["csv_file"]["type"] . "<br />";echo "Size: " . ($_FILES["csv_file"]["size"] / 1024) . " Kb<br />";echo "Stored in: " . $_FILES["csv_file"]["tmp_name"];} print_r($_FILE["csv_file"]);//check for file upload if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['spredSheet'])){ //upload directory $upload_dir = "csv_dir/"; //create file name $file_path = $upload_dir . $_FILES['csv_file']['name']; echo ($_FILES['csv_file']);//echo "asd"; //move uploaded file to upload dir if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) { //error moving upload file echo "Error moving file upload"; } //open the csv file for reading $handle = fopen($file_path, 'r'); //turn off autocommit and delete the product table mysql_query("SET AUTOCOMMIT=0"); mysql_query("BEGIN"); mysql_query("TRUNCATE TABLE $table") or die(mysql_error()); while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) { //Access field data in $data array ex. $name = $data[0]; $quantity = $data[1]; //Use data to insert into db $sql = sprintf("INSERT INTO product_table (product_name, product_quantity) VALUES ('%s',%d)", mysql_real_escape_string($name), $quantity); mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql")); } //commit the data to the database mysql_query("COMMIT"); mysql_query("SET AUTOCOMMIT=1"); //delete csv file unlink($file_path); }

[justsomeguy]

when I do print_r($_FILES)

Since the point is to see what was submitted by the form, you should put it at the top of the form handler script outside of any other control structures.

I get Array( )

If $_FILES is an empty array then no files were submitted. Check the code for your form.

[Ingolme]

I can see why there's nothing in the $_FILES array. The name attribute not put correctly here. <input type="file" name"csv_file" id="csv_file">

[mautorrejon]

O.O !!! yes... you have no idea how many times I looked at it...I would look at the rest of the code later and in class now...Thanks a lot!!!!

[birbal]

that is why it is best to use text editor with syntax highlights which is effective to detect this kind of mistakes.

[mautorrejon]

does anyone have a better way to upload a file dynamically into a database(mysql). I know that when doing it from my admin in mysql you have to set the columns and data type, Im trying to make an script to do it dynamically so anyone can upload their file into a database...is that possible?

Edited September 27, 2012 by mautorrejon

[justsomeguy]

You can write a script to get the column names from the first row in the file. I usually use fgetcsv to read CSV files, and the while loop that loop over all of the rows will check if it is on the first row and use that row as the column names. It will build an array of column names and then another array of data for the rest of the rows and each time it does an insert into the database it will use the array of column names to tell it which columns to use. http://php.net/manual/en/function.fgetcsv.php

[mautorrejon]

Is there a way of telling the database what type of variable each attribute would hold(i.e. numberic, char, varchar) without going into phpadmin and manipulating it. I guess what I want to accomplish is an interface in which the user only has to upload the csv_file and be able to display it, edit it or delete. My major concern is EDIT. Thanks in advance to anyone

[justsomeguy]

Is there a way of telling the database what type of variable each attribute would hold

You don't have to. The database already knows. Are you talking about importing records into an existing table or creating an entirely new database structure from a CSV file?

[mautorrejon]

My main concern is when I need to upload a csv file to create a entire new table in my DB, without any data definition so later on someone can edit a value within the table. Suppose my DB is full of records of classes, and admins usually edits values, but sometimes they have to upload an entire file which will become a table and all the values in the file would populate the table.

[justsomeguy]

Well, a CSV file really is only text, so the only fields the table needs are varchar or text fields. If they want advanced editing (like a date picker for a date field) then you need to build in a way for them to tell the system that the field is a date field so that you can account for that in the database and interface. Otherwise, they upload text files and they edit text values. If they want anything else then it's not going to be the simple system that you want. Simple means text-only, with no relationships between tables.

[mautorrejon]

I know how to display all the content andn I also know how many columns my file has so I can make it fit dynamically. I am having trouble creating the table and inputting the header into the table as well as the data itself. This is what I have so far...any help please? <?php //db connections goes here $tableName = "tableToTest"; if ($_FILES["file.csv"]["error"] > 0)echo "Error: " . $_FILES["file.csv"]["error"] . "<br />"; $row = 1;if (($handle = fopen("file.csv", "r")) !== FALSE) {while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {$num = count($data); if ($row==1) { //checks for the headers of the tablefor ($j=0; $j<$num; $j++)$data[$j]."<br/>\n"; while ($j<=$num)mysql_query ("CREATE TABLE ".$table."();"); }//end of IF$row++; } fclose($handle);}?>

[justsomeguy]

You have the create table query in a loop, how many tables are you trying to create? You're not changing the name so that's going to fail at some point, you can't have multiple tables with the same name. You also need to specify the fields you want to create in the table. This is the manual page for the create table query that shows all of the options and a few examples, there are several other examples online: http://dev.mysql.com/doc/refman/5.1/en/create-table.html