Why use explode/implode with MySql insert?

Wysocki · 01-27-2005, 12:39 PM

I have a sample file for inserting the contents of a csv file into a MySql table as follows:

Code:

$fcontents = file ('./csv2web.csv');
for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);
$sql = "INSERT INTO contacts VALUES ('".implode("','", $arr) ."')";
mysql_query($sql);
...more...

I'm puzzled on two issues: 1) What on earth is that crazy syntax in the values and 2) why couldn't I just do it this way:

Code:

$fcontents = file ('./csv2web.csv');
for($i=0; $i<sizeof($fcontents); $i++) {
$sql = "INSERT INTO contacts VALUES ($fcontents[$i])";
mysql_query($sql);
...more...

sde · 01-27-2005, 01:10 PM

values is part of mysql insert syntax.

let's forget about the csv and look at sql syntax. let's say we have a 2 field contacts table "firstname" "lastname". to insert a row into a database, my sql query would look something like this:

Code:

INSERT INTO contacts VALUES('John','Smith')

since there is only 2 fields, we dont' have to identify field names. the following sql would do the same thing:

Code:

INSERT INTO contacts (firstname,lastname) VALUES('John','Smith')

this time we are specifying what fields we are inserting into. if there were more than just firstname lastname, and we were only inserting those 2 fields, we would have to specify field names before values.

in your '$sql=' line, you are just creating a query string that you are inserting into mysql. $fcontents is an array, and if you just echo $fcontents, it will return Array. remember, we are just creating a STRING to send to mysql.

for example, if you did what you show in your #2 question, your sql statement would look like this:

Code:

INSERT INTO contacts VALUES(Array)

now i'm probably going backwards here, but what explode does is takes a string, and creates an array using the argument supplied as a seperator. 1 line of csv may look like this:

Quote:

john,smith

$arr=explode(","$line) would be the same as this:

PHP Code:

  <?

$arr[0] = "john";

$arr[1] = "smith";

?>

you can see that it takes what is in between each comma and creates a new element of the array.

sooo, the big question is why do i explode it if i just turn right around and impode it right? well, in sql syntax, you need to seperate each value in between single quotes. look at my first example above, both names have single quotes around it.

since your csv file does not have single quotes around the names, your script creates an array, then cleverly impodes the array, except it doesn't just add back in the comma that was there before, it adds in the single quotes too.

implode("','", $arr);

it's difficult to see exactly what is there, but the first argument is a single quote, comma, single quote. so for element 1, john, it will make it 'john',

did that make sense?

Wysocki · 01-27-2005, 01:31 PM

Therefore, IF my csv file had single quotes around each field ('General Motors','Ford Corporation','Microsoft, Inc.','IBM') then I WOULD be able to do it just like the second example because a given row of the csv would have single quotes already in it? Further in this example, I'd HAVE to already have the quotes in the csv since Microsoft, Inc. would be interpreted as two fields, right?

In my second example:
$sql = "INSERT INTO contacts VALUES ($fcontents[$i])";
I'm basically stating that the current [$i] row of the file should be inserted into the table, not the entire csv file array, right?

Also, unless I have garbage at the end of my csv lines I don't know why I'd need the trim function.

Lastly, could you please explain this syntax (specifically, what are the double quotes and the dots for?):
VALUES ('".implode("','", $arr) ."')
I guess I need to understand how php interprets nested quotes.

sde · 01-27-2005, 01:41 PM

correct, if you had single quotes around each of your items in the csv file, you would not have to implode or expload each line.

the dots in a string, take you in and out of a string. it's always good to exit out of a string before you use a variable. for example:

$sql = "INSERT INTO contacts VALUES(".$fcontents[$i].")";

the quotes just end the string, but the dot concatinates what is next into the string. you see after the variable, we again concatinate and start the string again.

Wysocki · 01-27-2005, 02:53 PM

Ahh, so the dot is a concatenate operator! Got it. I'm forseeing deeper issues with how to handle a csv file here. My data fields could contain just about any characters like apostrophes, commas, quotes, etc. Like these field values:

Code:

Denny's Restaurant
Black 4" heels
Microsoft, Inc.

To prevent the commas from triggering new fields, I have to quote each field, but if the field contains quotes, I have to use apostrophes, etc. What if a field contains multiple special characters?

I took a simple csv record like: 'apple','banana','cherry' and did this:

Code:

$fcontents = file ('./csv2web.csv');
$arr = explode("','", $fcontents[$i]);
echo "$arr[0] $arr[1] $arr[3]<br>";

The output read:

Code:

'apple banana cherry'

So the leading and trailing apostrophes were kept as part of the first and last elements in the array. Looks like this is turning into a much larger discussion on the handling of csv data and not just a php/mysql issue. I guess I'll have to research the web tonite!

idx · 01-27-2005, 05:03 PM

Sounds like you need to filter some of the data and a better bet would be to not use CSV. I'd recommend a pipe-delimited file format since pipes aren't generally contained within the data.

If you have access to perl I can post some code that should deal with the imbedded comma that will throw things off..

-r

sde · 01-27-2005, 06:20 PM

also, does your data contain tabs? of not, then could you make it a tab delimited file?

PHP Code:

  <?

$arr = explode("\t",$line);

?>