RopeADope Posted February 15, 2011 Share Posted February 15, 2011 Hi all. I'm not sure if this post belongs here or in the MySQL forum because I'm not entirely sure where the problem is. I have a script that takes data from a form and puts it into a database. There are several tables that were hand built and the script works fine with those. There are several tables that were converted from M$ Access to MySQL and the script does not work with those. All tables are InnoDB (if that matters). My inkling is that the converted tables that have weird field names, some containing "%","#", and "-" characters, are interfering with the MySQL statement to do the insert. Any thoughts on this? Would those odd characters for field names in fact affect the MySQL statement to enter data? P.S. I don't have access to the code at the moment. Can post it later unless the solution to this problem is simple (i.e. weird characters are the problem). Thanks in advance! Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/ Share on other sites More sharing options...
BlueSkyIS Posted February 15, 2011 Share Posted February 15, 2011 some characters are special in mysql, including %, so having that in a field name could be a problem. you should check mysql_error() after executing a query to see what fails in the query, e.g., $result = mysql_query($sql) or die(mysql_error() . " IN $sql"); Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1174614 Share on other sites More sharing options...
RopeADope Posted February 15, 2011 Author Share Posted February 15, 2011 Ah, thanks. I'll give that a test and post anything I find out. On a side note, is there a way to just change every field name and remove/replace the problematic characters? Preferably something I could write with PHP? Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1174619 Share on other sites More sharing options...
RopeADope Posted February 18, 2011 Author Share Posted February 18, 2011 Ok, so its definitely the bad field name characters causing a problem. I added the line that you suggested so my code looks like this... $sql="INSERT INTO {$selected_table} ({$key_string}) VALUES ({$value_string})"; $result=mysql_query($sql) or die(mysql_error() . " IN $sql"); And the output looks like... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%P1AppsCovered,%TestFailures,%IT_services_tested,MTBStratDays,MTBBiADays,MTBRADa' at line 1 IN INSERT INTO kpiscmmin (KPISCMMinID,KPISCMMinDate,%P1AppsCovered,%TestFailures,%IT_services_tested,MTBStratDays,MTBBiADays,MTBRADays,MTBTests-Days,SCMViabIndex) VALUES ("","","","","","","","","","") I'm not sure how I go about fixing this. I tried adding single quotes around the field names but that didn't help at all. Any ideas? Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1176245 Share on other sites More sharing options...
kenrbnsn Posted February 18, 2011 Share Posted February 18, 2011 Try putting backticks ` around the field names, not single quotes. Ken Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1176248 Share on other sites More sharing options...
RopeADope Posted February 18, 2011 Author Share Posted February 18, 2011 Ah, backticks worked. But I've come into another interesting problem. After adding the backticks, the statement made it past the "%" characters in the field names but stopped on a field name of "%IT_services_tested". Unknown column '%IT_services_tested' in 'field list' IN INSERT INTO kpiscmmin (`KPISCMMinID`,`KPISCMMinDate`,`%P1AppsCovered`,`%TestFailures`,`%IT_services_tested`,`MTBStratDays`,`MTBBiADays`,`MTBRADays`,`MTBTests-Days`,`SCMViabIndex`) VALUES ("","","","","","","","","","") In the MySQL table and in the input form, this field is "%IT services tested". I have mysql_real_escape_string(trim($fieldname)) earlier in my code. Would either of those be adding the underscores to the field name? If so...how should I approach this problem? My initial thought is to just change the field name... Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1176258 Share on other sites More sharing options...
Pikachu2000 Posted February 18, 2011 Share Posted February 18, 2011 Having spaces in a db table/field name is not usually a great idea. It would probably be best to just go ahead and change it, as you were thinking. Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1176261 Share on other sites More sharing options...
RopeADope Posted February 18, 2011 Author Share Posted February 18, 2011 Changed the field name to %ITServicesTested and everything works fine now. Thanks to all for the help! Link to comment https://forums.phpfreaks.com/topic/227773-php-and-mysql-not-sure-what-the-problem-is/#findComment-1176269 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.