MySQL doesn't care about quotes when doing INSERT INTO ... SELECT. It's
got to be something you're doing in PHP. Are you using that exact query?
mysql create table list (clientid int, fname varchar(20));
Query OK, 0 rows affected (0.07 sec)
mysql create table list2 (fname varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql insert into list values (1,'John'),(2,'O\'Hare'),(3,'Smith');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from list;
+--++
| clientid | fname |
+--++
|1 | John |
|2 | O'Hare |
|3 | Smith |
+--++
3 rows in set (0.03 sec)
mysql insert into list2 (fname) select fname from list where clientid =
2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql select * from list2;
++
| fname |
++
| O'Hare |
++
1 row in set (0.00 sec)
Even if you're using double addslashes(), it'll work...
mysql insert into list values (4,'O\\\'Henry');
Query OK, 1 row affected (0.00 sec)
mysql insert into list2 (fname) select fname from list where clientid =
4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql select * from list2;
+--+
| fname|
+--+
| O'Hare |
| O\'Henry |
+--+
2 rows in set (0.00 sec)
---John Holmes...
-Original Message-
From: David McInnis [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 21, 2002 1:57 AM
To: [EMAIL PROTECTED]
Subject: [PHP] Mysql Insert from select problem with php
After posting this on the MySQL list and getting some feedback we were
able to determine that this was not a flaw with MySQL. Any ideas from
the PHP community?
===
Can anyone tell me why this does not work? I am using php and mysql.
When I do an insert from select into a mysql table I get an error
whenever a value from the select portion of the query contains an
apostrophe. Before I insert the initial value I use the php
addslashes() function to escape the ' and characters.
For example:
If clients table contans a field called fname and it has a value of
O'Henry. Remembering that I have used addslashes() before inserting
the value into mysql in the first place.
The following query will fail on insert:
Sql = insert into orders (fname) SELECT fname from clients where
clientid = '$clientid';
David McInnis
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
