RE: [PHP] Mysql Insert from select problem with php
| From: | John Holmes | Date: | Fri, 21 Jun 2002 08:52:14 +0000 |
| Subject: | RE: [PHP] Mysql Insert from select problem with php | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-103245@lists.php.net to get a copy of this message | ||
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
mysql> (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:david@dataovation.com]
> Sent: Friday, June 21, 2002 1:57 AM
> To: php-general@lists.php.net
> 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