Import CSV file directly into MySQL database

Question

Possible Duplicate:
Import CSV to mysql

Right I need some help with this:

I am trying to import a .csv file into a mysql database using php, rather than doing it manually through phpmyadmin.

This is the code I have at the moment:

if($_REQUEST['func'] == "iid"){
    $db->conn = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or 
                      die('There was a problem connecting to the database.');
    $csv = $_POST['csv-file'];
    $path = $csv;
    $row = 1;
    if (($handle = fopen($path, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
            $row++;
            $data_entries[] = $data ;

        }
        fclose($handle);
    }
    // this you'll have to expand
    foreach($data_entries as $line){
        $sql = $db->conn->prepare('INSERT INTO `bd_results`');

        $db->execute($line);
    }
}

However I get the following error:

Fatal error: Call to undefined method stdClass::execute() in /homepages/19/d372249701/htdocs/business-sites/bowlplex-doubles-new/admin/scores.php on line 44

For reference I am using this code taken from: Here

I am not well versed in the $db->conn business I'm used to mysql_connect!! so any help would be appreciated.

What do you get if you use $db->conn->execute() ? - rephrase - is it $db or $db->conn that's the database object? — MyStream, Jul 12 '12 at 21:58
@MyStream `Fatal error: Call to undefined method mysqli::execute() in /homepages/19/d372249701/htdocs/business-sites/bowlplex-doubles-new/admin/scores.php on line 44` — David Passmore, Jul 12 '12 at 21:59
That's better =) that's the object you need - but see below for the right syntax to use for the query. — MyStream, Jul 12 '12 at 22:00
Did you consider using LOAD DATA INFILE? http://dev.mysql.com/doc/refman/5.1/en/load-data.html — Mahn, Jul 12 '12 at 22:05

Tech163 · Accepted Answer · 2012-07-13T13:32:57.627

1

Try this simple one.

if (($handle = fopen("google.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $db->conn->query("INSERT INTO values('" . implode('\',\'', $data) . "');");
    }
    fclose($handle);
}

Your script might also need to add quotes to the CSV values if they don't have quotes already. If you'll be needing to deal with quotes and all in your CSV files, I recommend you look at my blog post at http://www.fusionswift.com/2012/07/php-import-csv-to-mysql/

edited Jul 13 '12 at 13:32

answered Jul 12 '12 at 22:00

Tech163

4,176
8
33
36

All I get is this `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1` using `$db->conn->error` – David Passmore Jul 12 '12 at 22:11
I am using ; as the delimeter and "" as quotes what do I change – David Passmore Jul 12 '12 at 23:04
What does your CSV look like? Theoretically the new code should work. – Tech163 Jul 12 '12 at 23:19
Example Row 1 : `1;"LAURA";"OBRIEN";0;"WATFORD";161;1;104836;263;"F";"14/09/1995";186;147;173;133;210;192;164;148;1353;169.1;3083` – David Passmore Jul 12 '12 at 23:24
In `while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {`, change the `","` to `";"` and that should work – Tech163 Jul 12 '12 at 23:45
No errors. but it is missing the first record? – David Passmore Jul 13 '12 at 00:08
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13824/discussion-between-tech163-and-david-passmore) – Tech163 Jul 13 '12 at 00:10

ksiimson · Answer 2 · 2012-07-12T22:07:55.743

foreach($data_entries as $line) {
    $stmt = $db->conn->prepare('INSERT INTO `bd_results` (field1, field2) VALUES (?, ?)');
    $stmt->bindParam('ss', $field1Value, $field2Value);
    list($field1Value, $field2Value) = $line
    $stmt->execute();
}

Where $field1Value is first CSV column, $field2Value is second CSV column and both are of type string, specified as such in bindParam() method.

Basically you will need to prepare the query in its entirety, then you can assign variables to it, and once the variables have desired values you execute the query using execute() method.

This is how you use prepared statements. Personally I'd go with Mahn's suggestion though and avoid using prepared statements for such a task unless you need to process the data while on it.

mysqli_stmt::bind_param mysqli_stmt::execute

Import CSV file directly into MySQL database

2 Answers2

Linked