problem inserting the char '

[54uydf]

Hi,
how do I add (using SQL) string that has the char '
?
I have many words that look like this- dsf'dfdf or dfsdf'
but when I'm trying to add them the query command thinks that the ' is the end of the string and then messes up everything after that.

for example-

$query = "INSERT INTO tbl (Id, courseName) ".
"VALUES('$Id','$Name');";

so if the $Name is "abc'def" the query thinks that the "def" part is SQL syntex and gives me errorssssssssss

I should also add that there's no way to change the string that needs to be inserted. there are thousands of records

[smantscheff]

You have to escape the apostrophe with a backslash.
In PHP there is the function mysql_real_escape_string with adds all the necessary backslashes for feeding text into mysql.
The query must read:

INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\'d name');

In PHP you have to make sure that the escape character itself gets escaped:

$query = "INSERT INTO tbl (Id, courseName) VALUES(123,'Apostroph\\'d name')";

[54uydf]

ok, but I'm not writing a query for every word I want to insert..
I'm moving many records from 1 db to another.. so in the loop my $Name string gets a word each time and needs to insert it.

maybe I need to write a little function that replaces all the ' with \\'
that's the only idea I have at the moment. but then, I'll still have the ' in the update query!

[smantscheff]

To move records from database A to database B just use this:

insert into B (column1, column2) select column_1, column_2 from A

That way you don't have to tackle any special characters.
Since you seem to be using PHP you don't have to write that "little function" yourself but you can use mysql_real_escape_string (as I told you already).

$query = "INSERT INTO tbl (Id, courseName) VALUES('$Id','"  . mysql_real_escape-string($Name) . "')";

[54uydf]

I'm not exactly copying db A into db B..
it's more of an update of db A. I add records from B that aren't in db A already and update those in A that have difference in some of the values (but have the same key in A and B)
I will try to use mysql_real_escape-string()

thank you very much!

[54uydf]

worked perfectly!!
thank you so much!