Jackson Jackson - 15 days ago 12
MySQL Question

PHP: CSV import into MYSQL is always less than the actual amount of rows in the CSV file?

This is a very strange issue that I have and I don't understand what's causing it.

Basically, the issue is that I have a simple upload function in my PHP that uploads a CSV file and then imports each row into the MYSQL database.

Now, the issue is that I have around

200+
rows in my CSV file but when i upload and import it into the MYSQL using my PHP page, I only get around
158 of them imported
and I don't get any errors at all either so i don't understand what's causing this.

I have another CSV file that has around
300+ rows
in it and when I upload/import this CSV file, i get around
270 rows imported
into MYSQL.

This is like the import function is always short a few rows and I don't understand it at all.

This is my PHP import code:

error_reporting(-1);
ini_set('display_errors', 'On');

if (isset($_POST['UP'])) {

include "config/connect.php";

$imp= $_FILES["csv"]["name"];
move_uploaded_file($_FILES["csv"]["tmp_name"],"imports/$imp");



// path where your CSV file is located
////////////////////////////////////////////////////////
define('CSV_PATH','');

// Name of your CSV file
$csv_file = CSV_PATH . "imports/".$imp."";
$i = 0;

set_time_limit(10000);


$fp = fopen("imports/".$imp."", "r");

while( !feof($fp) ) {
if( !$line = fgetcsv($fp, 1000, ',', '"')) {
continue;
}
$sql0 = "INSERT INTO `myTable`(`column1`) VALUES('".$line[0]."')";
$query0 = mysqli_query($db_conx, $sql0);

}

fclose($fp);

printf("<script>location.href='mypage.php'</script>");
exit();
}


Using direct import into MYSQL is out of question due to security issues/hole..

Could someone please advice on this issue?

Any help would be appreciated.

Answer

To get around quoting issues, you want to use prepared statements with bind_param.

Procedural style:

$stmt = mysqli_prepare($db_conx, "INSERT INTO `myTable`(`column1`) VALUES(?)");
mysqli_stmt_bind_param($stmt, 's', $line[0] );
mysqli_stmt_execute($stmt);

Object-oriented style:

$stmt = $mysqli->prepare("INSERT INTO `myTable`(`column1`) VALUES(?)");
$stmt->bind_param('s', $line[0]);

$stmt->execute();

Per the docs, use s for strings, i for integers, and d for doubles/decimals.

Comments