[mySQL] Script file failing HELP!

[tritium]

I'm trying to use a script file to insert information into a table called, pages, belonging to the database, brenthoard. This is probably something n00bish ...

Here are the table fields:

index_number AUTO_INCREMENT
content TEXT
update_date TIMESTAMP

Here's my script:

#select table
use brenthoard;

# insert Experience page into Database
INSERT INTO pages VALUES
(
1, "<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function popUp(URL) {
day = new Date();
id = day.getTime();
eval("page" + id + " = window.open(URL, '" + id + "', 'toolbar=0,scrollbars=0,location=0,statusbar=0,menubar=0,resizable=0,width=430,height=490,left = 20,top = 20');");
}
// End -->
</script>

<!--backing images off the navbar-->
<table width="653" border="0" cellspacing="12" cellpadding="12">
<tr>
<td width="93" align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=twd.wmv&title=TWD&videob=twd_large.wmv')"><img src="images/twd.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=twd.wmv&title=TWD&videob=twd_large.wmv')">Guided Tour </a></p></td>
<td width="476" align="left" valign="top"> <a href="http://www.twd3.com">http://www.twd3.com</a>: I began working for Third Wave Digital in May of 2003 and have since become the resident search engine placement (SEP) expert. It has become my goal to optimize each page that is created or maintained by Third Wave Digital for placement in the major search engines (Yahoo.com, Google.com, Altavista.com, etc.) with the expectation of high listings for targeted keywords (within the top 30 of total results). </td>
</tr>
<tr>
<td align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=swsphoto.wmv&title=SchroederPhotography&videob=swsphoto_large.wmv')"><img src="images/swsphoto.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=swsphoto.wmv&title=SchroederPhotography&videob=swsphoto_large.wmv')">Guided Tour </a></p></td>
<td align="left" valign="top"> <a href="http://www.swsphoto.com">http://www.swsphoto.com</a>: For over 10-years, Steve Schroeder has been taking Macon 's portraits and has been quite successful in operating his own studio, Schroeder Photography Studios, without any major form of advertising. In May of 2003, Steve came to a business decision to begin a marketing campaign to boost his business and contacted Third Wave Digital about producing a website for his company. I worked independently on this project for Third Wave Digital as the lead designer and developer. </td>
</tr>
<tr>
<td align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=voc.wmv&title=VOC&videob=voc_large.wmv')"><img src="images/voc.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=voc.wmv&title=VOC&videob=voc_large.wmv')">Guided Tour </a></p></td>
<td align="left" valign="top"><a href="http://www.vidaliaonion.org">http://www.vidaliaonion.org</a>: The Vidalia Onion Committee (VOC) contacted Third Wave Digital in April of 2003 seeking assistance with building their own web presence. Over the course of 3 months, the teams of graphic artists and web programmers at Third Wave Digital developed a multimedia platform for the Committee that launched in mid-June. As a member of the VOC team, I worked enthusiastically on the artistic development and the coding aspects of this project. </td>
</tr>
<tr>
<td align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=tcemc.wmv&title=TriCountyEMC&videob=tcemc_large.wmv')"><img src="images/tcemc.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=tcemc.wmv&title=TriCountyEMC&videob=tcemc_large.wmv')">Guided Tour </a></p></td>
<td align="left" valign="top"> <a href="http://www.tricountyemc.com">http://www.tricountyemc.com</a>: TriCounty EMC provides electricity for a large portion of rural-Macon, Georgia 's community. As a large utility, TriCounty needed a webservice to provide information not only to its customers, but also to its personnel. The resulting website developed at Third Wave Digital is coded heavily in Cold Fusion with features that enabled the client, TriCounty EMC, to handle all maintenance. </td>
</tr>
<tr>
<td align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=hydroblast.wmv&title=IndustrialHydroblast&videob=hydroblast_large.wmv')"><img src="images/hydroblast.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=hydroblast.wmv&title=IndustrialHydroblast&videob=hydroblast_large.wmv')">Guided Tour </a></p></td>
<td align="left" valign="top"> <a href="http://www.industrialhydroblast.com">http://www.industrialhydroblast.com</a>: Industrial Hydroblast was one of my first projects for Third Wave Digital. The Industrial Hydroblst website was nearly complete when I joined the TWD team, but I have been steadily working since the site's official launch in May of 2003 to ensure high search engine placement results. As one of the sites under my SEP care, Industrial Hydroblast has produced exciting results on Altavista and MSN, even though its content limitations have made it an extremely difficult site to place. </td>
</tr>
<tr>
<td align="center" valign="top"><p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=seo.wmv&title=SEO&videob=seo_large.wmv')"><img src="images/seo_thumb.jpg" width="79" height="79" border="0"></a></p>
<p><a href="javascript:popUp('http://www.brenthoard.com/brenthoard.com-asp/video.asp?video=seo.wmv&title=SEO&videob=seo_large.wmv')">Guided Tour </a></p></td>
<td align="left" valign="top">- http://www.1842inn.com/ <br />
- http://www.access-env.com/ (11/03 - Access-Env.com is officially closed) <br />
- http://www.basscustomlandscapes.com/ <br />
- http://www.covenantcareadoptions.com/ <br />
- http://www.dreisner.com/ <br />
- http://www.industrialhydroblast.com/ <br />
- http://www.leschwartz.com/ <br />
- http://www.maconmagazine.com/ <br />
- http://www.maconworks.com/ <br />
- http://www.museumofaviation.org/ <br />
- http://www.pilotinternational.org/ <br />
- http://www.schwartzprecision.com/ <br />
- http://www.spwkits.com/ <br />
- http://www.superlawntrucks.com/ <br />
- http://www.ohtrail.com</td>
</tr>
</table>");

# show all data pertaining to this page
SELECT * FROM pages;

Here's the error I receive:

mysql> source experience.sql;
Database changed
ERROR 1064: You have an error in your SQL syntax near 'JavaScript">
<!-- Begin
function popUp(URL) {
day = new Date();
id = day.get' at line 3
ERROR 1064: You have an error in your SQL syntax near '}
// End -->
</script>

<!--backing images off the navbar-->
<table width=' at line 1

[/quote]

[Rawb]

Watch your quotes. You need to put anything text-wise into a database enclosed in quotes. But when it hits another set of those quotes, the quoting stops, and it tries to go back into plain old sql mode.

So everywhere in your actual text, you need to escape all of the " with \" (most systems have a way to do this, like php's addslashes() )

Example:

Before:
INSERT INTO pages VALUES
(
1, "<SCRIPT LANGUAGE="JavaScript">
<!-- Begin

After:
INSERT INTO pages VALUES
(
1, "<SCRIPT LANGUAGE=\"JavaScript\">
<!-- Begin

[tritium]

I went through and changed all the single quotation marks to the \" and received a series of errors:

ERROR:
Unknown command '\"'.

I went back and changed all the \ to / and received the original error message again.

[Fallon]

Wanna post your updated script? I spent about 10 minutes going thru it(should have used a text editor and done search/replace) and gave up when I looked and I wasn't even halfway.

[cthulu23]

One possible quick fix...replace all double quotes in the code itself with single quotes and then wrap the code in double quotes. You have an awful lot of special characters in your javascript/html, so you have to be careful to segregate SQL significant characters from your code.

Edit: the suggestion to escape any double quotes with a "\" (ie - \") should work, but only if you leave the outermost, wrapping quotes alone...for instance: "<SCRIPT LANGUAGE=\"JavaScript\">...</table>");

[tritium]

I'll post a link to the script tomorow. I wound up using ' instead of " ... this allowed me to input the text to the database, but trashed my javascripting...

I'll need to figure out why the /" thing doesn't work correctly.

[cliche]

I'll need to figure out why the /" thing doesn't work correctly.

Careful - there's a difference between '/' (forward slash) and '\\' (backslash). Use the backslash to escape the quotes.