browse & upload php file to mysql database w/php

azukah · 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>

mikosiko · 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)
				);
...

azukah · March 13, 2012

thanks. fixed that error syntax but still nothing is being inserted in the db.

azukah · 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>

Sign In

browse & upload php file to mysql database w/php

Recommended Posts

azukah

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

azukah

Link to comment

Share on other sites

azukah

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information