azukah Posted March 13, 2012 Share Posted March 13, 2012 hi- i'm trying to browse for a CSV file and then upload it to my mysql database. i found the code below and not working. i don't get any errors. the connection to the database is ok bcz i get the existing results but not the ones from the CSV file added to the db.. any ideas? <?php ob_start(); require_once('../../connections/congif.php'); mysql_select_db($dbname, $db); $sql_get_project="SELECT * FROM gifts_tbl ORDER BY autoID DESC LIMIT 25"; $get_project = mysql_query($sql_get_project, $db) or die(mysql_error()); $row_get_project = mysql_fetch_assoc($get_project); //database connect info here //check for file upload if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){ //upload directory $upload_dir = "csv_dir/"; //create file name $file_path = $upload_dir . $_FILES['csv_file']['name']; //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("BEGIN"); while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) { //Access field data in $data array ex. $name = $data[0]; //Use data to insert into db $sql = sprintf("INSERT INTO gifts_tbl (player_id) VALUES ('%s)", mysql_real_escape_string($name) ); mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql")); } unlink($file_path); } ob_flush(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>Gifts</title> </head> <body> <h1>Testing for CSV upload</h1> <form action="" method="post"> <input type="file" name="csv_file"> <input type="submit" name="csv_submit" value="Upload CSV File"> </form> <h2>Results</h2> <?php do { ?> <ul> <li><?php echo $row_get_project['player_id']; ?></li> </ul> <?php } while ($row_get_project = mysql_fetch_assoc($get_project)); ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/258851-browse-upload-php-file-to-mysql-database-wphp/ Share on other sites More sharing options...
mikosiko Posted March 13, 2012 Share Posted March 13, 2012 to start check for syntax error on this lines... echo your $sql to see. ... //Use data to insert into db $sql = sprintf("INSERT INTO gifts_tbl (player_id) VALUES ('%s)", mysql_real_escape_string($name) ); ... Quote Link to comment https://forums.phpfreaks.com/topic/258851-browse-upload-php-file-to-mysql-database-wphp/#findComment-1327005 Share on other sites More sharing options...
azukah Posted March 13, 2012 Author Share Posted March 13, 2012 thanks. fixed that error syntax but still nothing is being inserted in the db. Quote Link to comment https://forums.phpfreaks.com/topic/258851-browse-upload-php-file-to-mysql-database-wphp/#findComment-1327025 Share on other sites More sharing options...
azukah Posted March 13, 2012 Author Share Posted March 13, 2012 I figured it out!! <?php ob_start(); mysql_select_db($database, $makeconnection); $sql_get_players=" SELECT * FROM table ORDER BY id ASC"; $get_players = mysql_query($sql_get_players, $makeconnection) or die(mysql_error()); $row_get_players = mysql_fetch_assoc($get_players); // $message = null; $allowed_extensions = array('csv'); $upload_path = '.'; //same directory if (!empty($_FILES['file'])) { if ($_FILES['file']['error'] == 0) { // check extension $file = explode(".", $_FILES['file']['name']); $extension = array_pop($file); if (in_array($extension, $allowed_extensions)) { if (move_uploaded_file($_FILES['file']['tmp_name'], $upload_path.'/'.$_FILES['file']['name'])) { if (($handle = fopen($upload_path.'/'.$_FILES['file']['name'], "r")) !== false) { $keys = array(); $out = array(); $insert = array(); $line = 1; while (($row = fgetcsv($handle, 0, ',', '"')) !== FALSE) { foreach($row as $key => $value) { if ($line === 1) { $keys[$key] = $value; } else { $out[$line][$key] = $value; } } $line++; } fclose($handle); if (!empty($keys) && !empty($out)) { $db = new PDO('mysql:host=name;dbname=database', 'user', 'pw'); $db->exec("SET CHARACTER SET utf8"); foreach($out as $key => $value) { $sql = "INSERT INTO `table` (`"; $sql .= implode("`id`", $keys); $sql .= "`) VALUES ("; $sql .= implode(", ", array_fill(0, count($keys), "?")); $sql .= ")"; $statement = $db->prepare($sql); $statement->execute($value); } $message = '<span class="green">File has been uploaded successfully</span>'; } } } } else { $message = '<span class="red">Only .csv file format is allowed</span>'; } } else { $message = '<span class="red">There was a problem with your file</span>'; } } ob_flush();?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>CSV File Upload</title> </head> <body> <h2>Search & Upload file</h2> <section id="wrapper"> <form action="" method="post" enctype="multipart/form-data"> <table cellpadding="0" cellspacing="0" border="0" class="table"> <tr> <th><label for="file">Select file</label> <?php echo $message; ?></th> </tr> <tr> <td><input type="file" name="file" id="file" size="30" /></td> </tr> <tr> <td><input type="submit" id="btn" class="fl_l" value="Submit" /></td> </tr> </table> </form> </section> <br/> <h2>Results:</h2> <?php do { ?> <p><?php echo $row_get_players['id'];?></p> <?php } while ($row_get_players = mysql_fetch_assoc($get_players)); ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/258851-browse-upload-php-file-to-mysql-database-wphp/#findComment-1327059 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.