PHP and MySQL--Not sure what the problem is...

[RopeADope]

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!

[BlueSkyIS]

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");

[RopeADope]

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?

[RopeADope]

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?

 

[kenrbnsn]

Try putting backticks ` around the field names, not single quotes.

 

Ken

[RopeADope]

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...

[Pikachu2000]

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.

 

 

[RopeADope]

Changed the field name to %ITServicesTested and everything works fine now.  Thanks to all for the help!