I try to import csv not through phpmyadmin but through php request. I was offered this option (LOAD DATA INFILE on the hosting is closed)

$connection = @mysql_connect($host, $user, $pswd) or die(mysql_error()); $dbs = @mysql_select_db($database, $connection) or die(mysql_error()); $nam="test.csv"; $separator=";"; $fop = fopen($nam , "r+"); $i=0; while (!feof($fop)) { $read = fgets($fop, 3000); list($id, $name_group, $name, $price, $remainder, $description) = split($separator ,$read); mysql_query("INSERT INTO test FROM dbtest SET `id`='".$id."', `name_group`='".$name_group."', `name`='".$name."', `price`='".$price."', `remainder`='".$remainder."', `description`='".$description."' "); $i++; } fclose($fop); echo "Импортировано в базу записей: ".$i; ?>

As a result, the script writes that the correct number of records has been imported, but the base is empty. There are no errors on the hosting in the logs. How to understand why data is not recorded?

  • your script shows how many iterations of the "insert" are done, and not how many of them are actually executed. - teran
  • you would be familiar with the syntax of the command insert - teran
  • I forgot to add that ... FROM dbtest ... there is clearly superfluous. - teran

1 answer 1

You have an error in the sql query.

 INSERT INTO test FROM dbtest SET `id`='".$id."'

The format of the request to insert into the database is as follows:

 INSERT INTO table_name (column1, column2) VALUES (value1, value2)

I would suggest not to rewrite the code a lot using PDO and the standard PHP function for reading csv files, for example like this:

 $host = 'localhost'; $db_name = 'test'; $db_user = 'root'; $db_passwd = ''; $file = 'test.csv'; $delimiter = ','; $verbose = true; try { $db = new PDO("mysql:host=$host;dbname=$db_name", $db_user, $db_passwd, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); $stmt = $db->prepare("INSERT INTO my1 (`id`, `name_group`, `name`, `price`, `remainder`, `description`) VALUES (?, ?, ?, ?, ?, ?) "); if (!file_exists($file)){ throw new Exception("File $file not found!"); } $f = fopen($file, 'r+'); $data = array(); while($data[]=fgetcsv($f, 0, $delimiter)){} if (empty($data)) { throw new Exception("Empty data. Check the source file."); } $i=0; foreach ($data as $entry) { if (!is_array($entry) || empty($entry[0])) { continue; } if ($verbose){ print "\nProcessing entry ID: ". $entry[0]; } $stmt->execute($entry); $i++; } print "\n\n$i rows were successfully processed"; } catch (Exception $e){ print "Error: " . $e->getMessage(); }

Source csv file:

 1,group_1,name_1,400,,description_1 2,group_2,name_1,500,,description_2 3,group_3,name_1,600,,description_3 4,group_4,name_1,400,,description_4 5,group_5,name_1,500,,description_5

Result:

screenshot