mautorrejon Posted September 20, 2012 Share Posted September 20, 2012 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); } Link to comment Share on other sites More sharing options...
justsomeguy Posted September 20, 2012 Share Posted September 20, 2012 What happens when you run that? Link to comment Share on other sites More sharing options...
mautorrejon Posted September 20, 2012 Author Share Posted September 20, 2012 I get 0 on the page...I tried print_r($_FILES[''csv_file]) and nothing is printed.... Link to comment Share on other sites More sharing options...
justsomeguy Posted September 20, 2012 Share Posted September 20, 2012 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. Link to comment Share on other sites More sharing options...
mautorrejon Posted September 21, 2012 Author Share Posted September 21, 2012 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); } Link to comment Share on other sites More sharing options...
justsomeguy Posted September 25, 2012 Share Posted September 25, 2012 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. Link to comment Share on other sites More sharing options...
Ingolme Posted September 25, 2012 Share Posted September 25, 2012 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"> 1 Link to comment Share on other sites More sharing options...
mautorrejon Posted September 25, 2012 Author Share Posted September 25, 2012 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!!!! Link to comment Share on other sites More sharing options...
birbal Posted September 26, 2012 Share Posted September 26, 2012 that is why it is best to use text editor with syntax highlights which is effective to detect this kind of mistakes. Link to comment Share on other sites More sharing options...
mautorrejon Posted September 27, 2012 Author Share Posted September 27, 2012 (edited) 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 27, 2012 Share Posted September 27, 2012 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 Link to comment Share on other sites More sharing options...
mautorrejon Posted September 27, 2012 Author Share Posted September 27, 2012 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 27, 2012 Share Posted September 27, 2012 Is there a way of telling the database what type of variable each attribute would holdYou 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? Link to comment Share on other sites More sharing options...
mautorrejon Posted September 27, 2012 Author Share Posted September 27, 2012 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 27, 2012 Share Posted September 27, 2012 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. Link to comment Share on other sites More sharing options...
mautorrejon Posted September 28, 2012 Author Share Posted September 28, 2012 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);}?> Link to comment Share on other sites More sharing options...
justsomeguy Posted September 28, 2012 Share Posted September 28, 2012 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now