PHP: Import CSV into MYSQL

Question

I am trying to import some data from a CSV file (with headers) into a MySQL table. I have debugged this script and it seems to be working up until the "Handle" variable. When I run this code, I get a blank screen and the MySQL table is not updated with the CSV data. I may be missing something here. Any help is appreciated!

Thanks,

AME

Here's the code:

<?PHP

error_reporting(E_ALL & ~E_NOTICE);
if (ini_get('display_errors') === FALSE) {
ini_set('display_errors', 1);
}

$dbhost = 'localhost';
$dbuser = 'myusernam';
$dbpasswd = 'mypassword';
$db = "dbname";

$dbh = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Unable to connect to SQL server");
$my_db = @mysql_select_db($db, $dbh) or die("Unable to select database");

$file = $_SERVER['DOCUMENT_ROOT']."/home/test.csv";

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

while (($fileop = fgetcsv($handle,1000,",")) !==false)
{

    $listid = $fileop[0];
    $listclass = $fileop[1];
    $type = $fileop[2];
    $status = $fileop[3];
    $remarks = $fileop[3];
    $streetaddress = $fileop[6];
    $fulladdress = $fileop[6].",".$fileop[7].",".$fileop[8];
    $orgname = $fileop[9];
    $licenseid = $fileop[10];
    $agentname = $fileop[12]." ".$fileop[12];
    $imagecount = $fileop[13];

    $sql = mysql_query("INSERT INTO markers (id,Class,Status,AskingPrice,Remarks,StreetAddres,address,OrgName,AgentLicenseID,AgentName,ImageCount) VALUES ('$listid','$listclass','$type','$status','$remarks','$streetaddress','$fulladdress','$orgname','$licenseid','$agentname','$imagecount')");

}

    if($sql)
    {
        echo 'data uploaded successfully';
    }

?>

white screen of death, error checking disabled or reporting odd — , Dec 16 '12 at 08:59
As a newbie - run your script AFTER each written line. In that case you would know for sure which line is wrong. So now remove all the script and try to write it line by line checking the expected and actual results after each step. This is how we all write scripts (until you're experienced enough to write 100 lines once without checks) — zerkms, Dec 16 '12 at 08:59
To debug your scripts use [proper error reporting settings](http://stackoverflow.com/questions/5680831/php-5-3-does-not-display-error-messages). You have to see error text to detect what is your problem. — Valera Leontyev, Dec 16 '12 at 09:00
Try to use error handling for this script -> fopen($file, "r"); — Yagi, Dec 16 '12 at 09:01
@zerkms I ran this script after each line as you suggested. It seems to be working just fine until the "While" loop. When I Echo the $fileop array, I get all the values I would expect. I hope this helps. — AME, Dec 16 '12 at 09:04
@AME: "When I Echo the $fileop array, I get all the values I would expect" --- and what't the issue then? If you get what expect, why you say "t seems to be working just fine **until** the "While" loop"? — zerkms, Dec 16 '12 at 09:05
@zerkms the script is returning the values from the CSV file as expected, but it is not inserting these values into the MySQL table. I think the issue is with the mysql_query. — AME, Dec 16 '12 at 09:07
@AME: and wasn't your first thought - to output the result query? Why do you think you have the correct query? Programming is about dealing with **facts**, not with guesses — zerkms, Dec 16 '12 at 09:08
Only weird thing I noticed was your field called address, seems odd. It doesn't match your other field schemes. Maybe you wanted to call it FullAddress? — PhearOfRayne, Dec 16 '12 at 09:27
@StevenFarley thanks, for the suggestion. that was intentional. I have another script that references that 'address' field, so i purposely left it lowercase. — AME, Dec 16 '12 at 09:33
Your id field doesn't have auto increment set does it? If you tried to store the same id over an existing id it would prevent this script from working. Also would any of your csv values have single quotes(') in them? If so you need to escape those fields using php addslashes() — PhearOfRayne, Dec 16 '12 at 09:38
For some reason, I can return this: echo $fileop[5]; But this goes to a blank page: echo $remarks; Even though I assigned: $remarks = $fileop[5]; Could this be cause of the error? — AME, Dec 16 '12 at 09:52
Hi all, maybe this problem requires the actual data to solve. Here is a link to my table and the actual csv file.. https://www.dropbox.com/sh/4iq10i51qlqyq8q/UjEQwvXKDA — AME, Dec 17 '12 at 07:22

score 2 · Answer 1 · answered Dec 16 '12 at 09:11

2

Sounds to me like your file permissions are not correct, so your unable to read the file! To get error messages to output to your browser add this just under your <?php tag:

error_reporting(E_ALL & ~E_NOTICE);
if (ini_get('display_errors') === FALSE) {
    ini_set('display_errors', 1);
}

If it's not the file permissions at least it should hopefully output something more useful to us!

answered Dec 16 '12 at 09:11

PhearOfRayne

4,990
3
31
44

Thanks, I added error reporting and still get the white screen – AME Dec 16 '12 at 09:16
Are you using a windows server or linux server, and if your on a linux server have you tried setting your file permissions to something with more access like 0755 or 0775? – PhearOfRayne Dec 16 '12 at 09:23
This script is hosted on my bluehost account. I changed permission to 755 and it didn't change the result. When I changed it to 775, I got a 500 Server Error. – AME Dec 16 '12 at 09:31

PHP: Import CSV into MYSQL

1 Answers1