Punctuation problems.

[Elric99]

I'm having trouble inserting () and ' in MYSQL. Here's some examples:

INSERT INTO database (something) VALUES ('The Pineapple Express directed by David Gordon Green stars Seth Rogan James Franco and Danny R. McBride. ; ; Lazy stoner Dale Denton (Seth Rogen) has only one reason to visit his equally lazy dealer Saul Silver (James Franco's): to purchase weed specifically a rare new strain called Pineapple Express. ; ; But when Dale becomes the only witness to a murder by a crooked cop (Rosie Perez) and the city's most dangerous drug lord (Gary Cole) he panics and dumps his roach of Pineapple Express at the scene. But the weed is so rare that it can be traced back Saul. Dale and Saul have to run for their lives as the bad guys are hot on their trail! All aboard the Pineapple Express.')

Can someone please remind me what the correct way to insert:

'
"
)

So that it doesn't cause errors?

Thanks!

Tom

[jatar_k]

for any quote inside something that's quoted with the same you put a \ before it

'...crooked cop (Rosie Perez) and the city\'s most dangerous drug lord...'

the ( or ) shouldn't make any difference unless you forgot to quote around the string they are in

[whoisgregg]

You can use mysql_real_escape_string() [php.net] to automatically escape the quotes. :)

[andrewsmd]

I also think that '' (two single quotes) will be parsed as a single ' within the string in mysql. I would follow whoisgregg's advice though. mysql_real_escape is the way to go.

[rocknbil]

In the course of multiple development, I often encounter situations where an application must be ported to Perl, or vice versa.

Although prebuilt PHP functions are the method of choice, to avoid nit-picking over select statements, which are often objects in a function/sub, I use andrew's method.

insert into table (fields...) values ('one''s', 'two''s'....)

Single quoting has two small advantages, in my case. The first is described above, portability of my selects to other languages. The second is it only gives you a single character requiring escape, and you don't really need to escape it - just double it.

It is also one of the suggested methods in the mySQL documentation, so, at the "mySQL level," in ways it's as correct as any other approach.

Using single quotes doesn't interfere with other characters, specifically, the parentheses.

$value = "He's on it! (said the hare)";

$value = preg_replace("/'/","''",$value);

Of course, you'd loop through the insert/update values to avoid tons of variables.

But in PHP only, the provided functions are the method of choice.