Need help with "Updating db" function
Posted: Thu Oct 15, 2009 4:32 pm
OK, I'm new here, I don't know PHP and I need help because I can't figure out what is wrong with what I'm doing.
My site has a db admin section where the admin can update records via the web. In this case, store location information (one table) and zip codes serviced (another table) are edited on the same webpage. I want to add place names to the page, so I've created a table and I've been able to display associated place names with the appropriate store location, but when I edit those place names and click "Submit", the edits are not posted to the table.
Basically, I copied the code for updating the zip codes and changed the references to different tables and fields. I've also experimented around with different table structure in an effort to get this to work, but I don't know what's wrong. Maybe someone here will spot the problem. BTW, in the zip code version the zip code is the ID/key. I suspect that because it is the key that is being displayed and updated, my changing the fields around for the place names is wrecking the code. In the place names version, I tried making the place name the primary key (because each city/place will also have a state abbreviation so that each would be unique) but that didn't work because it's a text field, so I then added another field for the PK and named it "RealID". Hope you can make sense of my mess...
First, here's the update zip codes function that works:
Next, here's the place names alteration:
Any help you can provide is greatly appreciated. Thanks in advance for!
My site has a db admin section where the admin can update records via the web. In this case, store location information (one table) and zip codes serviced (another table) are edited on the same webpage. I want to add place names to the page, so I've created a table and I've been able to display associated place names with the appropriate store location, but when I edit those place names and click "Submit", the edits are not posted to the table.
Basically, I copied the code for updating the zip codes and changed the references to different tables and fields. I've also experimented around with different table structure in an effort to get this to work, but I don't know what's wrong. Maybe someone here will spot the problem. BTW, in the zip code version the zip code is the ID/key. I suspect that because it is the key that is being displayed and updated, my changing the fields around for the place names is wrecking the code. In the place names version, I tried making the place name the primary key (because each city/place will also have a state abbreviation so that each would be unique) but that didn't work because it's a text field, so I then added another field for the PK and named it "RealID". Hope you can make sense of my mess...
First, here's the update zip codes function that works:
Code: Select all
function UpdateZipCodes($IsUpdate)
{
global $c_Key;
$AllZips = $_REQUEST['zipcodes'];
// split the phrase by any number of commas or space characters,
// which include " ", \r, \t, \n and \f
$ZipArray = preg_split ("/[\s,]+/", $AllZips, -1, PREG_SPLIT_NO_EMPTY);
// Arrange the zip codes into a proper SQL list
for (reset($ZipArray), $count = 0; list(, $Zip) = each($ZipArray); $count++) {
if ($count)
$zipSQLList .= ",";
$zipSQLList .= "'$Zip'";
}
// Get zipcode records that match the ones we entered
$sqlSelect = "SELECT zip, fk_franchiseid FROM zipcodes WHERE zip IN ($zipSQLList)";
print "<!--UpdateZipCodes (sqlSelect): $sqlSelect-->\n";
$ZipResult = mysql_query($sqlSelect) or die("Query failed : " . mysql_error());
// Add all of the above zip codes to an associative array
while ($ZipRow = mysql_fetch_assoc($ZipResult))
$ZipLookup[$ZipRow['zip']] = $ZipRow['fk_franchiseid'];
// Mark the zip codes that we need to INSERT or UPDATE
for (reset($ZipArray); list(, $Zip) = each($ZipArray); ) {
$r_id = & $ZipLookup[$Zip];
if (!isset($r_id))
$r_id = "i";
else if ($r_id != $c_Key)
$r_id = "u";
}
// Generate the INSERT and UPDATE statements
$sqlUpdate = "UPDATE zipcodes SET fk_franchiseid = '$c_Key' WHERE zip IN (";
$sqlInsert = "INSERT INTO zipcodes (zip, fk_franchiseid) VALUES ";
for (reset($ZipLookup), $cI = 0, $cU = 0; list($Zip, $id) = each($ZipLookup); ) {
switch ($id) {
case "i":
if ($cI) $sqlInsert .= ", ";
$sqlInsert .= "('$Zip','$c_Key')";
$cI++;
break;
case "u":
if ($cU) $sqlUpdate .= ",";
$sqlUpdate .= "'$Zip'";
$cU++;
break;
}
}
$sqlUpdate .= ")";
if ($cU) {
print "<!--UpdateZipCodes (sqlUpdate): $sqlUpdate-->\n";
mysql_query($sqlUpdate) or die("Query failed : " . mysql_error());
}
if ($cI) {
print "<!--UpdateZipCodes (sqlInsert): $sqlInsert-->\n";
mysql_query($sqlInsert) or die("Query failed : " . mysql_error());
}
// Update all zipcode entries that were previously associated with this
// Franchisee but are not now to have a NULL fk_franchiseid value
$sqlClear = "UPDATE zipcodes SET fk_franchiseid = Null ".
"WHERE fk_franchiseid = '$c_Key' AND zip NOT IN ($zipSQLList)";
print "<!--UpdateZipCodes (sqlClear): $sqlClear-->\n";
mysql_query($sqlClear) or die("Query failed : " . mysql_error());
}
Code: Select all
function UpdatePlaceNames($IsUpdate)
{
global $c_Key;
$AllPlaces = $_REQUEST['tblPlaces'];
// split the phrase by | character only
$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, RealID FROM tblPlaces WHERE PlaceID INTO ($placesSQLList)";
print "<!--UpdatePlaceNames (sqlSelect): $sqlSelect-->\n";
$PlacesResult = mysql_query($sqlSelect) or die("Query failed : " . 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 PlaceID SET fk_franchiseid = '$c_Key' WHERE PlaceID IN (";
$sqlInsert = "INSERT INTO tblPlaces (PlaceID, fk_franchiseid, RealID) 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 : " . mysql_error());
}
if ($cI) {
print "<!--UpdatePlaceNames (sqlInsert): $sqlInsert-->\n";
mysql_query($sqlInsert) or die("Query failed : " . 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 : " . mysql_error());
}