Page 1 of 1

Need help with "Updating db" function

Posted: Thu Oct 15, 2009 4:32 pm
by rpw
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:

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());
}
 
Next, here's the place names alteration:

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());
}
 
Any help you can provide is greatly appreciated. Thanks in advance for!

Re: Need help with "Updating db" function

Posted: Fri Oct 16, 2009 5:24 am
by litarena
What error message are you getting?

Re: Need help with "Updating db" function

Posted: Fri Oct 16, 2009 11:03 am
by rpw
Not getting any error message. Simply, changes to the data are not transmitted to the db.

More explanation:
* Access the page and a list of stores is displayed
* Click on one store link and the details of the store is displayed along with related data such as zip codes and places serviced (each piece of store data is in a separate "box" while the related data is ALL in separate combined "box" with each piece separated by a "space" or a "|" --- so zip codes are displayed "92801 92802 92803" etc. and places are displayed "Boston, MA | Springfield, MA | Braintree, MA", etc.)
* The data in each box can be edited but the db is not modified until the "Submit" link is clicked and then several update functions run. Any change to the zip codes is then reflected upon returning to the display. Any changes to the places returns to the previous state - no error messages, just no change to the data.

If I manually update the table, then those changes are reflected in the display, so I know that other portions of the code is working properly - it's only the page edits for places that is not.

I don't know for sure (because I don't "read" PHP) but I think that the update zips is working because the zip is the PK and it looks like there's a "go find the zip PK's already related to this store, compare them to the current list of zip PK's on this page and change whatever needs to be changed" going on. So, with the copied and modified code, because the place data is not the PK, the "make changes" portion is just simply running and not finding any changes to make because it cannot find the place PK. Does that make sense? Could that be what's happening or am I delusional? Ha!