Several Query Fails - Need Help
Posted: Thu Oct 22, 2009 2:21 pm
Hi All,
I'm working with an existing db_admin page for franchise locations and related service areas. The portion that I'm having trouble with is on the franchise location details page which has a list of related service areas. The PK of the location is VARCHAR(6), the PK of the service area table is VARCHAR(50). I can get the listing of service areas to work but am having trouble with changing the data via the list display field. Clicking the "Submit" link on the page will run several functions including the one included below that is causing/reporting the errors. I've tagged the 'or die' with text that helps identify the SQL that caused the error.
I appreciate any help that can be offered. Here is a rundown of Error Message/Preceding Action/Comments
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by clicking the "Submit" link without changing any data/In this example, 'Anaheim' is the PK (PlaceID) for tblPlaces and is the first PK listed in the data display
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by deleting one or more PK's from the data display
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by adding one or more PK's to the data display
"Query failed THREE: 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 'fk_franchiseid = Null, WHERE fk_franchiseid = 'f3' AND PlaceID NOT IN ('0')' at line 1"/caused by manually selecting all data and overwriting it with one or more PK's (place name strings)/Interestingly enough, the new data has been added to the table and the overwritten ones are still associated with the location so the query partially worked...
"Query failed THREE: 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 'fk_franchiseid = Null, WHERE fk_franchiseid = 'f3' AND PlaceID NOT IN ('0','1','' at line 1"/caused by adding one or more PK's (place name strings) to an empty data list display/Here again, after returning to the display page, the data has been added so the query worked regardless of the error message?
"Query failed FOUR: 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 ')' at line 1)"/caused by deleting all PK's from the data display list.
Here's the code:
Let me know if you need anything else and thank you in advance for your help.
I'm working with an existing db_admin page for franchise locations and related service areas. The portion that I'm having trouble with is on the franchise location details page which has a list of related service areas. The PK of the location is VARCHAR(6), the PK of the service area table is VARCHAR(50). I can get the listing of service areas to work but am having trouble with changing the data via the list display field. Clicking the "Submit" link on the page will run several functions including the one included below that is causing/reporting the errors. I've tagged the 'or die' with text that helps identify the SQL that caused the error.
I appreciate any help that can be offered. Here is a rundown of Error Message/Preceding Action/Comments
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by clicking the "Submit" link without changing any data/In this example, 'Anaheim' is the PK (PlaceID) for tblPlaces and is the first PK listed in the data display
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by deleting one or more PK's from the data display
"Query failed TWO: Duplicate entry 'Anaheim' for key 'PRIMARY'"/caused by adding one or more PK's to the data display
"Query failed THREE: 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 'fk_franchiseid = Null, WHERE fk_franchiseid = 'f3' AND PlaceID NOT IN ('0')' at line 1"/caused by manually selecting all data and overwriting it with one or more PK's (place name strings)/Interestingly enough, the new data has been added to the table and the overwritten ones are still associated with the location so the query partially worked...
"Query failed THREE: 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 'fk_franchiseid = Null, WHERE fk_franchiseid = 'f3' AND PlaceID NOT IN ('0','1','' at line 1"/caused by adding one or more PK's (place name strings) to an empty data list display/Here again, after returning to the display page, the data has been added so the query worked regardless of the error message?
"Query failed FOUR: 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 ')' at line 1)"/caused by deleting all PK's from the data display list.
Here's the code:
Code: Select all
function UpdatePlaceNames($IsUpdate)
{
global $c_Key;
$AllPlaces = $_REQUEST['tblPlaces'];
// split the phrase by any number of commas or space characters,
// which include " ", \r, \t, \n and \f
$PlacesArray = preg_split ("/[\s,]+/", $AllPlaces, -1, PREG_SPLIT_NO_EMPTY);
// Arrange the place names into a proper SQL list
for (reset($PlacesArray), $count = 0; list($Places) = each($PlacesArray); $count++) {
if ($count)
$placesSQLList .= ",";
$placesSQLList .= "'$Places'";
}
// Get place_name records that match the ones we entered
$sqlSelect = "SELECT PlaceID, fk_franchiseid FROM tblPlaces WHERE PlaceID IN ($placesSQLList)";
print "<!--UpdatePlaceNames (sqlSelect): $sqlSelect-->\n";
$PlacesResult = mysql_query($sqlSelect) or die("Query failed FOUR: " . mysql_error());
// Add all of the above place names to an associative array
while ($PlacesRow = mysql_fetch_assoc($PlacesResult))
$PlacesLookup[$PlacesRow['PlaceID']] = $PlacesRow['fk_franchiseid'];
// Mark the place names that we need to INSERT or UPDATE
for (reset($PlacesArray); list(, $Places) = each($PlacesArray); ) {
$r_id = & $PlacesLookup[$Places];
if (!isset($r_id))
$r_id = "i";
else if ($r_id != $c_Key)
$r_id = "u";
}
// Generate the INSERT and UPDATE statements
$sqlUpdate = "UPDATE tblPlaces SET fk_franchiseid = '$c_Key' WHERE PlaceID IN (";
$sqlInsert = "INSERT INTO tblPlaces (PlaceID, fk_franchiseid) VALUES ";
for (reset($PlacesLookup), $cI = 0, $cU = 0; list($Places, $id) = each($PlacesLookup); ) {
switch ($id) {
case "i":
if ($cI) $sqlInsert .= ", ";
$sqlInsert .= "('$Places','$c_Key')";
$cI++;
break;
case "u":
if ($cU) $sqlUpdate .= ",";
$sqlUpdate .= "'$Places'";
$cU++;
break;
}
}
$sqlUpdate .= ")";
if ($cU) {
print "<!--UpdatePlaceNames (sqlUpdate): $sqlUpdate-->\n";
mysql_query($sqlUpdate) or die("Query failed ONE: " . mysql_error());
}
if ($cI) {
print "<!--UpdatePlaceNames (sqlInsert): $sqlInsert-->\n";
mysql_query($sqlInsert) or die("Query failed TWO: " . mysql_error());
}
// Update all place_names entries that were previously associated with this
// Franchisee but are not now to have a NULL fk_franchiseid value
$sqlClear = "UPDATE tblPlaces SET fk_franchiseid = Null ".
"WHERE fk_franchiseid = '$c_Key' AND PlaceID NOT IN ($placesSQLList)";
print "<!--UpdatePlaceNames (sqlClear): $sqlClear-->\n";
mysql_query($sqlClear) or die("Query failed THREE: " . mysql_error());
}