Inserting CSV to Mysql with PHP

fluiduk · 27 Oct 2006 at 13:19

Before I leave you in a peace

What would be the best way to enter that data into the DB, im guessing I will need to loop an insert command?

How would you go about this?

Sorry to be a pain, this a bit beyond my skillset lol

Aaron

Dj_Jestar · 27 Oct 2006 at 14:30

Depends..

If the format is always the same (i.e. you will always have 'x' number of columns) then create a table that mimicks the CSV file.

If it varies, I'd favor for keeping the file on the server and using a file id or just plain file name in the database, which you can then link to on the website.

fluiduk · 27 Oct 2006 at 16:04

It will also have the same amount of columns, but obviously different amount of rows

How would I make a look that inserts the data, I can do the insert query, just not the looping

Aaron

Dj_Jestar · 27 Oct 2006 at 17:21

Code:

<?php

$query = "INSERT INTO `table` (`column1`, `column2`, `etc`) VALUES ";

foreach (file('file.csv') as $line)
{
    $query .= '(';
    $start = true;
    foreach(explode(',', $line) as $col)
    {
        if (!$start)
        {
            $query .= ', ';
        }
        $start = false;
        $query .= "'" . mysql_real_escape_string($col) . "'";
    }
}

$query .= ')';

echo htmlentities($query);
//mysql_query($query);

?>

echo it before running it to test.

fluiduk · 28 Oct 2006 at 12:32

Heya

Tried that query, its giving me errors, my code is

Code:

mysql_select_db($database_wp_data, $wp_data);
$query = "INSERT INTO `addresses` (`id`,`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`,`9`,`10`,`11`,`12`,`13`,`14`,`15`,`16`,`17`,`18`,`19`,`20`,`21`,`22`,`23`,`24`,`25`,`26`,`27`,`28`,`29`,`30`) VALUES ";

foreach (file('uploads/'.$filename.'') as $line)
{
    $query .= '(';
    $start = true;
    foreach(explode(',', $line) as $col)
    {
        if (!$start)
        {
            $query .= ', ';
        }
        $start = false;
        $query .= "'" . mysql_real_escape_string($col) . "'";
    }
}

$query .= ')';

echo htmlentities($query);
//mysql_query($query);


mysql_query($query, $wp_data) or die(mysql_error());

The error I am getting is
'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 '('Sales Record Number', 'User ID', 'Buyer Fullname', 'Buyer Address 1', 'Buyer A' at line 1'

Sorry to be a pain.

I did have another query for entering the data which seemed to work but then it started missing fields and inserting fields in the wrong order for no reason

meghatronic · 28 Oct 2006 at 12:39

There is a gap after 11 and 21 in the query, could be that.

Or try removing the quotes around `addresses`.

fluiduk · 28 Oct 2006 at 12:47

Neither of those have fixed it

Thanks though

EDIT - I have found out what was causing the random empty fields and data in the wrong order, some people have used comas in their address fields, eek

meghatronic · 28 Oct 2006 at 14:19

fluiduk said:
Neither of those have fixed it

Thanks though

EDIT - I have found out what was causing the random empty fields and data in the wrong order, some people have used comas in their address fields, eek

Yeah, I had some moron give me a data set with ' ' s in it.. had to rewrite... lol

fluiduk · 7 Nov 2006 at 09:33

Another quick issue for you php demigods.

If I have a csv that has wrapped each field in a double quotes how would I strip these out, is it something I need to set in the upload options?

Thanks
Aaron

fluiduk · 7 Nov 2006 at 09:39

Ignore this, found the code in this very post

Code:

   $file[] = explode('","', trim($line, '"'));

Dj_Jestar · 7 Nov 2006 at 09:39

you'll need to change the explode to

Code:

explode('","', trim($line, '"'));