problems with an insert query

bdee1

i am trying to run a SQL query which inserts data about DVDs from amazon.com into a database table.

here is my code:

$conn=odbc_connect('DVDLobby','','');
			$sql="INSERT INTO tblDVD(dvdID,UPC,Title,SortTitle,Rating,Released,RunningTime,Overview,FrontImageURL,MovieLocation,CollNumber,PurchaseDate,ChangerNumber,changerLocation)
			VALUES('$myUPC.','$myUPC','$myMovieTitle','$myMovieTitle','$myRating',$myReleaseDate,'$myRunningTime','$myDescription','$myImageURL','Hard Drive',1,$today,0,0)"; 
			$rs=odbc_exec($conn,$sql);

basically just a standard insert query.

problem is that sometimes when i run it, i get an error like this:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Destined to become a holiday perennial, <I>The Polar Express</I> also heralded a brave new world of all-digital filmmaking. Critics and audiences were divided between those who hailed it as an instant classic that captures the visual splendor and evocative '., SQL state 37000 in SQLExecDirect in C:\MovielistSite\backend\addmovie2.php on line 37

my guess is that it is choking on the description field which often contains various punctuation and HTML code. so i am thinking it hits a quote or comma or something and causes problems - sql thinks its another field or something.

if thats the case how could i make it so that i can insert a string like this(with punctuation and other special characters) and not cause problems?

jbatson

Use the php.net site and try addslashes(), stripslashes(), htmlspecialchars(), htmlentities() functions. Use these on the data before inserted into the databsae. Theese funcitons will convert the special characters to html and insert it w/o problems. you made need to convert it back when retrieving it from the db and displaying it.

bdee1

ok so i tried using the addslashes() functiuon and now my string is as follows:

Destined to become a holiday perennial, <I>The Polar Express</I> also heralded a brave new world of all-digital filmmaking. Critics and audiences were divided between those who hailed it as an instant classic that captures the visual splendor and evocative innocence of Chris Van Allsburg\'s popular children\'s book, and those who felt that the innovative use of \"performance capture\"--to accurately translate live performances into all-digital characters--was an eerie and not-quite-lifelike distraction from the story\'s epic-scale North Pole adventure. In any case it\'s a benign, kind-hearted celebration of the yuletide spirit, especially for kids who have almost grown out of their need to believe in Santa Claus. Tom Hanks is the nominal \"star\" who performs five different computer-generated characters, but it\'s the visuals that steal this show, as director Robert Zemeckis indulges his tireless pursuit of technological innovation. No matter how you respond to the many wonders on display, it\'s clear that The Polar Express represents a significant milestone in the digital revolution of cinema. If it also fills you with the joy of Christmas (in spite of its Nuremberg-like rally of frantic elves), so much the better. <I>--Jeff Shannon</I></p>

seems like it should work right?

but i am still gettign the same error - is there something i am missing?

rincewind456

mysql_real_escape_string()

bdee1

the database is a ms access database - that function wont work unless i am using mysql will it?

rincewind456

An interesting post in the manual for odbc_exec() in the user notes.

If a single quote exists within the field specified by your WHERE statement, ODBC fails because of a parsing error. Although it seems intuitive, using \" around the field does not work (\"$var\"). The only solution I found was to replace all single quotes in my field with two single quotes. ODBC interprets the first single quote as an escape character and interprets the second single quote as a literal. Thanks to http://www.devguru.com/features/knowledge_base/A100206.html for this tip.

bdee1

that seemed to do it - thanks!!!