PHP Fatal error: Column 'country_code' cannot be nullPHP Fatal error: Column 'country_code' cannot be null

Question

I have seen that some fellows have been having this same problem, but I could not find the solution.

My query seems ok until but it still return me this strange error

foreach ($file as $row) {

$query = query("INSERT INTO places (country_code, postal_code, place_name, admin_name1, admin_code1, admin_name2, admin_code2, admin_name3, admin_code3, latitude, longitude, accuracy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", $row[COUNTRY_CODE], $row[POSTAL_CODE], $row[PLACE_NAME], $row[ADMIN_NAME1], $row[ADMIN_CODE1], $row[ADMIN_NAME2], $row[ADMIN_CODE2], $row[ADMIN_NAME3], $row[ADMIN_CODE3], $row[LATITUDE], $row[LONGITUDE], $row[ACCURACY] );

PHP Fatal error: Column 'country_code' cannot be null in /home/jharvard/vhosts/pset8/includes/functions.php on line 57

Call Stack:

0.0003     126972   1. {main}() /home/jharvard/vhosts/pset8/bin/import:0

14.7066 55399132 2. query() /home/jharvard/vhosts/pset8/bin/import:45

14.7094 55403640 3. trigger_error() /home/jharvard/vhosts/pset8/includes/functions.php:57

At the same time it seems to break my phpAdmin not giving me access anymore to the data stored.

Fatal error: Maximum execution time of 30 seconds exceeded in /usr/share/phpmyadmin/libraries/DisplayResults.class.php on line 2959

Any help? Thank you!

Answer 1

How are you getting the data from the csv file and populating $file and, by extension, $row? I would start by doing a dump of $row just before the query. I suspect that there is no $row[COUNTRY_CODE], or it could be as simple as you've forgotten to put quotes around "COUNTRY_CODE". ;-)

In my code, I used the fgetcsv() function in a while loop and, for example, used $data[0] instead of $data[COUNTRY_CODE] and so on.

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

Answer 2

You will get this error

PHP Fatal error: Column 'country_code' cannot be null in /home/jharvard /vhosts/pset8/includes/functions.php on line 57

if you use while(!feof($yourfile)) to iterate over the input file.

From the Week 4 -> Shorts -> File I/O transcript:

Thus, a while loop with the condition (!feof(fp)) might seem like a perfectly appropriate solution. However, say we have one line left in our text file. We'll enter our while loop and everything will work out as planned. On the next round through, our program will check to see if feof of fp is true, but--and this is the crucial point to understand here-- it won't be true just yet. That's because the purpose of feof is not to check if the next call to a read function will hit the end of the file, but rather to check whether or not the end of the file has already been reached. In the case of this example, reading the last line of our file goes perfectly smoothly, but the program doesn't yet know that we've hit the end of our file. It's not until it does one additional read that it encounters the end of the file.

Your loop processes one "extra" read, which is not actual data, and tries to insert a row, thus provoking the error. My import works with this while condition: while ($data = fgetcsv($infile,.....

Or you could execute "query" conditionally (something like $row != null or !empty($row))

---

You will get this error

Fatal error: Maximum execution time of 30 seconds exceeded in /usr/share/phpmyadmin/libraries/DisplayResults.class.php on line 2959

if you choose "Show all" in phpMyAdmin. It's a little buggy. Don't use that option.