csv to mysql help

[eo92866]

the code below isn't populating the mysql fields... i believe it's not running correctly at the while and for loop areas.

 

also, would like to know of a way to have the 1000 to have the ability to look at the end of the row and then look at the next row of data, because there are sometimes +/-1000 characters in a row.

 

can someone please assist?

many thanks.

 

<?php

//connect to the database
include "opendb.php";


#if the first row of csv file contains column headings:
$columnheadings = 1;

if (($handle = fopen("/my/file/destination/name.csv", "r")) !== FALSE)
        {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
                {
                $num = count($data);
#               echo "<p> $num fields in line $columnheadings: <br /></p>\n";
                $columnheadings++;
                        for ($i=0; $i<$num; $i++)
                                {
                                $line = trim($data[$i],'",');
                                $arr = explode(',',$line);

                                //mysql insert of values
                                $sqlData = ("INSERT INTO thisTable(name1, name2, name3, name4, name5) VALUES('".$arr[0]."', '".$arr[1]."', '".$arr[2]."', '".$arr[3]."', '".$arr[4]."')");

                        }
                }
        fclose($handle);
        }


else
        {
        mysql_query($sqlData) or
        die(mysql_error("The specified file does not exist. Thanks."));
        }

mysql_close($con);

?>

[requinix]

If PHP and MySQL are running on the same server, (and if your MySQL user has permissions,) you can tell MySQL to load data from a CSV. You don't have to do it manually.

 

But

or die(mysql_error("The specified file does not exist. Thanks."))

Not quite right. mysql_error does not want a message to display - it wants a connection resource (such as the one returned by mysql_connect). You don't have to give it one, though.

If you want to display a message and the MySQL error message too,

or die("The specified file does not exist. Thanks. " . mysql_error())

Keep in mind that mysql_error can reveal important information that a malicious user could use against you. It's great for testing or development work, but once you go to a live server you should remove it and just display your custom error message.

[eo92866]

my script doesn't get to the point of even displaying an error from mysql.

[BlueSkyIS]

where does it stop? i would add debug echo's to see what's going on.

[eo92866]

not sure as to why it stops... i've added echo's a few times over and commented out at various points with no luck... so i'm turning here.

[eo92866]

so i've come up with this...

 

still not populating mysql. if someone can please assist.

 

many thanks.

 

<?php

//connect to the database
include "opendb.php";


#if the first row of csv file contains column headings:
$columnheadings = 1;

$i=0

$handle = fopen("/my/file/destination/name.csv", "r");
        while ($i<10000)
                {
                 //splitting data using fgetcsv and into array $data
                $data = fgetcsv($handle, 1000, ",")
#               echo "<p> $num fields in line $columnheadings: <br /></p>\n";

                //mysql insert of values
                $sqlData = mysql_query("INSERT INTO thisTable(name1, name2, name3, name4, name5) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."')");

                $i++

                }
fclose($handle);


mysql_close($con);

?> 

[BlueSkyIS]

no echos so you can't tell where processing is going, if anywhere. i'd start by adding some and checking sql execution. also, i suspect your error_reporting is not turned on.

 

<?php
ini_set('display_errors', 1);
error_reporting(-1);

echo "in script <br />";

//connect to the database
include "opendb.php";

#if the first row of csv file contains column headings:
$columnheadings = 1;

$i=0

$handle = fopen("/my/file/destination/name.csv", "r") or die("failed to open file");

echo "before loop... <br />";

while ($i<10000) {
//splitting data using fgetcsv and into array $data
$data = fgetcsv($handle, 1000, ",")

    // mysql insert of values
    // Build SQL
    $sql = "INSERT INTO thisTable(name1, name2, name3, name4, name5) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."')";
    
    // Execute SQL, checking for error
$sqlData = mysql_query($sql) or die(mysql_error(). " IN $sql");
$i++

}

echo "after loop... <br />";

fclose($handle);
mysql_close($con);

echo "done.<br />";
exit;
?>

[eo92866]

the web page didn't display any errors or messages. executing the code via command line returned an error of:

 

./file.php: line 3: syntax error near unexpected token `'display_errors','

./file.php: line 3: `ini_set('display_errors', 1);'

 

finally, error_reporting is set to E_ALL

and display_errors is set to Off in php.ini