Please help with syntax errors - Escape Charactors

[scottsdalelady]

I'm helping someone import a rather large product data base into Mysql. (approx 4000 products). My problem is the product_description table. The descriptions are rather long and contain tons of quotes, commas, and parentheses and periods. I've exported that column into a php editor that I use, as it has a super search and replace functionality. Excell was impossible to use for this purpose. My problem is that reading documentation, I've become confused when to use \ and when to incase inside ' ' Is there someone that could advise me here with some basic rules? Each time I think I've solved the issue I get an error on running the INSERT to the db.

Here's an example that contains just about all of the charactors I'm dealing with outside of the parentheses which I have a ton of those. The way I've handled this is not working for me (or I guess I wouldn't be here - lol). I'm really burned out here and any help would be greatly appreciated. Please forgive my ignorance, but I'm just really stuck. I thought I would have this done in a snap... well best laid plans......

INSERT INTO `products_description`
VALUES ( 51, 1, 'AAFTL: Amulet: After Life Goddess - Protectress of the dead', 'After Life Goddess Protectress of the dead\; copper finish. Approx 1' "" ' long X5 '/'16wide '"' ', NULL , 11, NULL , NULL , NULL );

 

[tshot]

what i gather from ur listing is that u hev only a fwe fileds but dlot of data. Hve u tried add_slashes of PHP.

e.g.

$fld1 = addslashes('AAFTL: Amulet: After Life Goddess - Protectress of the dead') ;
$fld2 = addslahes("After Life Goddess Protectress of the dead\; copper finish. Approx 1' long X5 /16wide") ;
 
INSERT  INTO  `products_description` 
VALUES ( 51, 1, '$fld1', '$fld2', NULL , 11,  NULL ,  NULL ,  NULL );

ur 2nd fld has had some ft symbol so it was done in double quotes




----------------
ur feedback is a very welcome desire

 

[scottsdalelady]

That would work fine, but I have over 4000 product entries. I'm drivin' myself crazy on this. The simplist string seems to be failing. My thoughts were to use an editor and escape out using \ and '' where certain characters are used, but I'm not a super db person and I get easily confused on this. (where to use\ vs ' ') What's nuts is the export came from another mysql db. I originally thought that I would add the new fields and re-upload the db without a hitch (boy was I mistaken), I can't figure out how to handle the "blobs" where the characters in the data don't stop the process dead in its tracks.

Is there anyone that can make a suggestion on this. I'm really stuck and I've been at this for a couple of days.

Thanks

 

[KarveR]

if you use (from a command line) mysqldump --complete insert databasename you should get a file that will load with no problems

Thats *if* you can use mysqldump.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[tshot]

if it is from another mysql db then KarveR suggestion would work. an dif they are connected u can direc output of mysqldump to ur new /c as

mysqldump --extend-insert | mysql --compress -h <ur server> <u db>

----------------
ur feedback is a very welcome desire

 

[scottsdalelady]

Unfortunately my customer has no shell access and even if her host provided it would be jailed access which I don't believe would give her access to mysqldump, but I'll check and see. I wish she were on my server then it would make this all much simpler.

 

[KarveR]

hrm .. fun fun fun :

ok if you can run php on that server, heres a quickie page - fill in the connection / table details, and upload.

<?
// get a connection
$user=&quot;root&quot;;
$pass=&quot;&quot;;
$server=&quot;localhost&quot;;
$db=&quot;&quot;;

$connection=mysql_connect($server,$user,$pass);
mysql_checkerror();
mysql_select_db($db,$connection);
mysql_checkerror();


if(!isset($sql)){
echo &quot;No Query&quot;;
exit;
}else{
$file=temp.&quot;.csv&quot;;
}

header(&quot;Content-Type: application/vnd.ms-excel&quot;
header(&quot;Content-Disposition: attachment;filename=&quot;.$file );
header('Pragma: no-cache');
header('Expires: 0');

$result = mysql_query($sql);
mysql_checkerror();

if($result){
$columns=@mysql_num_fields($result);
for ($i = 0; $i < mysql_num_fields($result); $i++) {

print &quot;\&quot;&quot;.mysql_field_name($result,$i).&quot;\&quot;,&quot;;

}
echo &quot;\n&quot;;

while ($myrow = mysql_fetch_array($result)){
for ($i = 0; $i < ($columns); $i++) {
echo &quot;\&quot;&quot;.$myrow[$i].&quot;\&quot;,&quot;;
}
echo &quot;\n&quot;;
}
}else{
echo &quot;No results&quot;;
}
// functions

function mysql_checkerror(){

$err_no=mysql_errno();

if ($err_no > 0 ){

echo &quot;<h2> Error:</h1> &quot; . mysql_errno() . &quot;: &quot; . mysql_error() . &quot;<br>\n&quot;;

exit;

}
}
?>

call it like

http://server.com/page.php?sql=select

* from yourtable

dont worry about the spaces, if you do it from IE you should be rewarded with acorrect csv of the database which will hopefully work.

delimited by &quot;,&quot; between all the fields.

hope it helps and works for you.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.