0

I got a very annoying problem at the moment. I try to create a CSV upload that writes to a MySQL database. The system works, but only when i got 1 row. When I got more rows, the error "Column count doesn't match value count at row 1" shows up.

Here's my php code:

DEFINE('DB_HOST', 'localhost');     // Server naam
DEFINE('DB_USERNAME', 'root');      // Gebruikersnaam
DEFINE('DB_PASSWORD', 'root');      // Wachtwoord
DEFINE('DB_DATABASE', 'wp3');   // Database naam

// Initialisatie
$databasetable = "users";
$fieldseparator = ",";
$lineseparator  = "\n";
$csvfile        = "backups/compleet.csv";
$output         = "";

// Database connection
$con = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
mysqli_set_charset($con, "utf8");

if (mysqli_connect_errno())
{
    die('Service kan niet geladen worden.');
}

// Perform checks with CSV file
if(!file_exists($csvfile))
{
    echo "File not found. Make sure you specified the correct path.\n";
    exit;
}

$file = fopen($csvfile, "r");

if(!$file)
{
    echo "Error opening data file.\n";
    exit;
}

$size = filesize($csvfile);

if(!$size)
{
    echo "File is empty.\n";
    exit;
}

// Get content from CSV file
$csvcontent = fread($file, $size);
fclose($file);

// Initialize CSV file variables
$lines      = 0;
$queries    = "";
$linearray  = array();

// Read each line from CSV file, clean data and put data in database
foreach(explode($lineseparator, $csvcontent) as $line)
{
    $lines++;
    $line      = trim($line, " \t");    
    $line      = str_replace("\r", "", $line);
    $line      = str_replace("'", "\'", $line);
    $linearray = explode($fieldseparator, $line);
    $linemysql = implode("','", $linearray);

    $query     = "INSERT INTO $databasetable VALUES('', '$linemysql')";

    // $queries .= $query . "\n";

    if (!mysqli_query($con, $query))
    {
        echo 'Fout: ' . $sql . '<br>' . mysqli_error($con);
    }
}

// Read all the rows from the table and print to screen
$sql            = "SELECT * FROM $databasetable";
$result         = mysqli_query($con, $sql);
$columns_total  = mysqli_num_fields($result);

while ($row = mysqli_fetch_array($result))
{
    for ($i=0; $i<$columns_total; $i++)
    {
        $output .= $row["$i"] . ', ';
    }

    $output .= '<br>';
}

echo "<p>Er zijn $lines regels aan de tabel toegevoegd.</p>";
echo "<p>De database bevat nu de volgende data:<br>$output</p>";

And here is my csv file:

id,username,password,firstname,lastname,permissions,image,thumb,header 3,Frank,password,Frank,VanDeursen,author,frankImg.jpg,frankImg.jpg,tropical.jpg 4,Kevin,password,Kef,Borgh,author,kev.jpg,kev.jpg,ocean.jpg

Thank for the help!

frankonus
  • 1
  • 1
  • Possible duplicate of [How to import csv file in PHP?](http://stackoverflow.com/questions/5813168/how-to-import-csv-file-in-php) – Naktibalda Nov 26 '15 at 14:42
  • Do you also get the error when you remove the leading double single quotes in VALUES in this line? `$query = "INSERT INTO $databasetable VALUES('', '$linemysql')";` – The fourth bird Nov 26 '15 at 19:08

1 Answers1

0
David, password, david, backname, author, stock.jpg, image.jpg, stock.jpg
Frank, password, frank, backname, admin, stock,jpg, image,jpg, stock,jpg

Your csv file has more columns in the second row check carefully stock,jpg, image,jpg, stock,jpg all having comma's instead of dots!

Wolfeh
  • 402
  • 2
  • 6
  • Sorry, that was just a wrong-example. It isn't the error i'm searching for. I have changed it. Thanks for your comment. – frankonus Nov 26 '15 at 15:25