-5

Good day. I am using php to read the data from excel file and then insert them into mysql table. Everything works well but one extra empty row is inserted in the table and it is causing me to get
#1062 error, duplicate entry ' ' for key primary.

How am I suppose to solve this problem? Thank you.

These are the codes...

$DBconnect = mysqli_connect("localhost","root","","lala") Or die("<p>unable to connect</p>") ; 
$sql="INSERT INTO temp(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6, COL_7, COL_8, COL_9) VALUES ('$call','$corporate','$isbn','$author','$title','$publication','$description','‌​    $subject','$url')"; 
$result = mysqli_query($DBconnect,$sql) Or die('Error, query failed');

Edit:

The result after I added

echo $sql; 
$result = mysqli_query($DBconnect,$sql) Or die('Error, query failed');

is 2 rows of these

INSERT INTO `temp`(`COL_1`, `COL_2`, `COL_3`, `COL_4`, `COL_5`, `COL_6`, `COL_7`, `COL_8`, `COL_9`) VALUES ('','','','','','','','','')
user3684573
  • 1
  • 1
  • 1
    Then don't insert empty rows. – Sverri M. Olsen May 28 '14 at 16:58
  • 2
    There is not enough information given for us to help. – Popnoodles May 28 '14 at 16:58
  • I did not insert any empty row on my own. – user3684573 May 28 '14 at 16:59
  • I can't post any photo here because stated i need 10 reputations in order to do so. – user3684573 May 28 '14 at 17:01
  • The overall coding are 3 pages long. – user3684573 May 28 '14 at 17:01
  • @user3684573 No need whole code. Post the "query" that the problem occurs. Eg. `INSERT INTO ... ;` – Guilherme Nascimento May 28 '14 at 17:02
  • http://stackoverflow.com/questions/14628269/error-code-1062-duplicate-entry-1-for-key-primary – fortune May 28 '14 at 17:02
  • Try adding error reporting to the top of your file(s) `error_reporting(E_ALL); ini_set('display_errors', 1);` see if it yields anything more. – Funk Forty Niner May 28 '14 at 17:02
  • @user3684573 try this: `echo $sql; $result = mysqli_query($DBconnect,$sql) Or die('Error, query failed');`, after exec post result from `echo` in your QUESTION. – Guilherme Nascimento May 28 '14 at 17:18
  • Which is the primary column? – Popnoodles May 28 '14 at 17:18
  • This could be an issue or a contributing factor `'$description','‌​ $subject','$url')` there's a bunch of spaces. Try changing it to `'$description','‌​$subject','$url')` You may also need to show us what your input file looks like. – Funk Forty Niner May 28 '14 at 17:19
  • COL_1 is the primary column – user3684573 May 28 '14 at 17:25
  • Did you try adding `error_reporting(E_ALL); ini_set('display_errors', 1);` like I suggested? – Funk Forty Niner May 28 '14 at 17:34
  • i did.Maybe I'm not sure about that function, so it shows nothing. How I should insert that? – user3684573 May 28 '14 at 17:47
  • @user3684573 is clear is that the variables are empty. How do you set the variables `$call`, `$corporate`, `$isbn`, `$author`, `$title`, `$publication`, `$description`, `$subject` and `$url`? This is a script ready, who developed or did you? If it was you who developed, try to create a smaller test version (without visual interface) for debugging, if a code is ready, enter the name. I suspect you are making some confusion with the variables or can even be a problem for character conversion. – Guilherme Nascimento May 28 '14 at 17:55
  • Hi guys. I have tried most methods mentioned here but I still can't figure out why 2 extra empty rolls are inserted. So, I added one line of code to ensure that any row with null value as primary key will not be inserted. Ya, can't figure out the real problem but that is the only thing I can do for now and it works. – user3684573 May 29 '14 at 08:05
  • $DBconnect = mysqli_connect("localhost","root","","lala") Or die("

    unable to connect

    ") ; if($call != NULL) { $sql="INSERT INTO `temp`(`COL_1`, `COL_2`, `COL_3`, `COL_4`, `COL_5`, `COL_6`, `COL_7`, `COL_8`, `COL_9`) VALUES ('$call','$corporate','$isbn','$author','$title','$publication','$description','$subject','$url')"; echo $sql; $result = mysqli_query($DBconnect,$sql) Or die('Error, query failed'); } } echo 'successfully inserted';     – user3684573 May 29 '14 at 08:06
  • @user3684573 is clear is that the variables are empty. How do you set the variables `$call`, `$corporate`, `$isbn`, `$author`, `$title`, `$publication`, `$description`, `$subject` and `$url`? – Guilherme Nascimento May 30 '14 at 18:33

1 Answers1

0

The quick solution I can propose is to verify data before inserting to database.

if your primary key is the first element in the row - following code is an example:

// read from excel to $data array here
$verifiedData = [];
foreach($data as $row) {
    if (!empty($row[0])) {
        $verifiedData[] = $row;
    }
}
// insert data from $verifiedData array to database here

For more correct and detailed answer it would be better to look on the code that performs mentioned operation.

Sergii Ivashchenko
  • 523
  • 6
  • 12