PHP and MySQL

[Anon]

I have a database SQL query which updates a table with various fields including strings.

If I include the ' (single quote) literately within the field the query fails.

EG.

$query="UPDATE crew SET last_name='$last_name', first_name='$first_name', other_name='$other_name', village='$village', town='$town', county='$county', tel='$tel', fax='$fax', mobile='$mobile', email='$email', website='$website', comments='$comments', credits='$credits' WHERE crew_id =$crew_id ";

If the comments include a single quote anywhere within the field data the query fails. WHY????

Many thanks.

[stereo]

$result = mysql_query("UPDATE crew SET last_name = '$last_name', first_name = '$first_name', other_name = '$other_name', village = '$village', town = '$town', county = '$county', tel = '$tel', fax = '$fax', mobile = '$mobile', email = '$email', website = '$website', comments = '$comments', credits = '$credits' WHERE crew_id = '$crew_id';");

Try putting it all in one like this. You may want to copy ans paste this statement in. I added some ' and ; in.

Hope it works.

Stereo

[kennethl]

It fails because if there's a single quote in your $comments variable, say $comments = "This ' is a comment", MySQL receives an SQL statement that looks like this:

"...comments='This ' is a comment'..."

So it looks to MySQL like the value for comments is "This " and then it has no idea what " is a comment" might mean.
Use the function addslashes() on your variables before sending them to the database, like:

"...comments='".addslashes($comments)."'..."