SQL Syntax error while using MySQL SET field

[jponte]

Hi,

I have this script to insert a few radius and checkboxes into a few SET and ENUM fields in a table. I get a syntax error but the print out of the query seems to be fine. Let me know if another pair of eyes can see the error:

 

Error:

INSERT INTO network_options (company_id, network_type, area, ggsn, egress, redundancy, connection, radius, roaming, blackberry) VALUES ('', 'GPRS', 'West, Central', 'TO5, MO2', 'MO2, VA2', 'GGSN, Egress', 'VPN', 'Y', 'N', 'N')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 'connection, radius, roaming, blackberry) VALUES ('', 'GPRS', 'West, Central', '' at line 1

 

Table:

[pre]company_id  smallint(4)

network_type  set('GSM', 'GPRS', 'UMTS', 'HSPA')

area  set('East', 'West', 'Central')

ggsn  set('TO5', 'VA2', 'MO2') 

egress  set('TO5', 'VA2', 'MO2')

redundancy  set('GGSN', 'Egress')

connection  set('FR', 'VPN', 'MPLS', 'GRE')

radius  enum('Y', 'N')

roaming  enum('Y', 'N')

blackberry  enum('Y', 'N')

[/pre]

 

 

Code:

//Convert the GGSN array choices into a value to enter in SET MySQL field
$ggsn_array = $_POST['ggsn']; 
foreach ($ggsn_array as $one_ggsn) { 
$srcggsn .= $one_ggsn.", "; 
} 
$setggsn = substr($srcggsn, 0, -2); 

//Convert the connection array choices into a value to enter in SET MySQL field
$connection_array = $_POST['connection']; 
foreach ($connection_array as $one_connection) { 
$srcconnection .= $one_connection.", "; 
} 
$setconnection = substr($srcconnection, 0, -2); 

//Convert the Egress array choices into a value to enter in SET MySQL field
$egress_array = $_POST['egress']; 
foreach ($egress_array as $one_egress) { 
$srcegress .= $one_egress.", "; 
} 
$setegress = substr($srcegress, 0, -2); 

//Convert the Redundancy array choices into a value to enter in SET MySQL field
$redundancy_array = $_POST['redundancy']; 
foreach ($redundancy_array as $one_redundancy) { 
$srcredundancy .= $one_redundancy.", "; 
} 
$setredundancy = substr($srcredundancy, 0, -2);

//Convert the Network type array choices into a value to enter in SET MySQL field
$networktype_array = $_POST['networktype']; 
foreach ($networktype_array as $one_networktype) { 
$srcnetworktype .= $one_networktype.", "; 
} 
$setnetworktype = substr($srcnetworktype, 0, -2);

//Convert the Coverage array choices into a value to enter in SET MySQL field
$coverage_array = $_POST['coverage']; 
foreach ($coverage_array as $one_coverage) { 
$srccoverage .= $one_coverage.", "; 
} 
$setcoverage = substr($srccoverage, 0, -2);

// now we insert the options into the network distribution list
$insert_networkoptions = "INSERT INTO network_options (company_id, network_type, area, ggsn, egress, redundancy, connection, radius, roaming, blackberry)
VALUES ('".$_POST['comp_id']."', '$setnetworktype', '$setcoverage', '$setggsn', '$setegress', '$setredundancy', '$setconnection', '".$_POST['radius']."', '".$_POST['roaming']."', '".$_POST['blackberry']."')";

echo $insert_networkoptions;
$add_networkoptions = mysql_query($insert_networkoptions)
or die(mysql_error());

 

Before executing the query the data looks OK on an echo:

 

INSERT INTO network_options (company_id, network_type, area, ggsn, egress, redundancy, connection, radius, roaming, blackberry) VALUES ('', 'GPRS', 'West, Central', 'TO5, MO2', 'MO2, VA2', 'GGSN, Egress', 'VPN', 'Y', 'N', 'N')

 

Thanks for all your help,

 

JP

[jponte]

Hi,

I have looked at this code forever and I cannot see the fault. Let me know if anyone is able to.

Thanks again,

 

JP

[PFMaBiSmAd]

connection (the part of the query displayed in the error for the right syntax to use near '....) has special meaning and would need to be enclosed in back-ticks in a query or renamed to something else.

[jponte]

Hi,

I knew the code was good. The "connection" word I guess it is reserved so I changed the field name and voila. It works.

Peace,

 

JP