Page 1 of 1
(#) in text field trouble
Posted: Tue Feb 18, 2003 8:28 am
by virgil
Hi Php's
I'm dumping a Mysql database ( for a backup on my local harddrive) using PhpMyadmin, This works fine. The trouble is when I re-upload the .sql file I get an error if the user had entered a # sign in any of 40+ text form fields. I think the Mysql sees these as comments in the middle of the values causing it to ignore the closing of the command.
Code: Select all
INSERT INTO table ( field1, field2, field3 )
VALUES ( 'data', 'any text #5 train' , NULL);
All I can think to do is use a cumbersome list of eregi replaces for 40+ fields that are displayed as html and as a repopulated form data (for updating the form by the user) and there are 3 different types of these long forms...+ preview displays, on 3 diferent pages.This is an awful lot of replacing and re-replacing.....
Anyone know a quick and easy(LOL

) way to get Mysql to ignore a # tag if within single quotes in a list of values?
Thanks a million
Virgil
Posted: Wed Feb 19, 2003 5:36 am
by Elena Mitovska
I tried yor code and it works fine!
INSERT INTO table ( field1, field2, field3 )
VALUES ( 'data', 'any text #5 train' , NULL);
I didn't have any trouble inserting data that includes special sybmol #, maybe problem is in other place?
Posted: Wed Feb 19, 2003 6:47 am
by virgil
The above code was just an example to show what I was talking about,
and it will insert into th DB fine UNLESS....
it is being uploaded(executed with phpMyadmin) from a .sql file on my hard drive.
The first part of the actual code is below and it errors at the # sign in the values...
Code: Select all
SQL-query:
# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: localhost:0000 Database : database1
# --------------------------------------------------------
#
# Table structure for table 'advertiser_mail_list'
#
DROP TABLE IF EXISTS advertiser_mail_list;
CREATE TABLE advertiser_mail_list (
advertiser_email_address varchar(100) NOT NULL,
PRIMARY KEY (advertiser_email_address)
);
#
# Dumping data for table 'advertiser_mail_list'
#
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( '#anyname@cs.com');
Thanks again for any help
Virgil

Posted: Wed Feb 19, 2003 7:55 am
by Elena Mitovska
I really tried your script on my phpMyAdmin 2.3.3pl1 with MySQL 3.23.54, I tried to insert data into table by running query with Insert in SQL block and from text file, and in both cases it worked fine, inserted data correctly and didn't generate any errors.
I don't know what may be the problem with your phpMyAdmin

Posted: Wed Feb 19, 2003 8:13 am
by Rob the R
Although you might not want to do this as a permanent solution, you can try changing your insert statement to this:
Code: Select all
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( CONCAT(CHAR(35),'anyname@cs.com'));
CHAR(35) will return the ASCII character number 35, which is "#". If the pound sign is truly making phpMyAdmin think the rest of that line is a comment, the above insert statement should work.
If that turns out to be the case, maybe you need to upgrade your phpMyAdmin to at least Elena's version.
Posted: Wed Feb 19, 2003 5:34 pm
by virgil
Hi again...
My host is using phpMyadmin 2.1.0 with MySQL 3.23.54
I think the problem you may be having in getting the error to reoccur is that there is no following statement to confuse it.
Just noticed that ...very sorry...
If you have a chance.....
Code: Select all
SQL-query:
# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: localhost:0000 Database : database1
# --------------------------------------------------------
#
# Table structure for table 'advertiser_mail_list'
#
DROP TABLE IF EXISTS advertiser_mail_list;
CREATE TABLE advertiser_mail_list (
advertiser_email_address varchar(100) NOT NULL,
PRIMARY KEY (advertiser_email_address)
);
#
# Dumping data for table 'advertiser_mail_list'
#
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( '#anyname@cs.com');
INSERT INTO advertiser_mail_list (advertiser_email_address) VALUES ( 'anyothername@cs.com');
If thats not it, maybe its a server setting or something. It is definitely happening though....
If I remove the # sign by hand it successfully executes. Put it back and it errors
Either way you have been very gracious in your help!
Again, my sincere thanks
Virgil