www.webdeveloper.com
Results 1 to 3 of 3

Thread: Find a MySQL record using text field data

  1. #1
    Join Date
    Nov 2008
    Location
    NC, USA
    Posts
    24

    Find a MySQL record using text field data

    I have setup a MySQL database table consisting of 4 fields to be used to display employee pictures in a webpage. The 4 fields are empID, name, picture, bio. There is an HTML form where the user enters the employee name & other data to be changed on the webpage. I wanted to use the employee name as the lookup value. The php code is as follows:

    PHP Code:
    if ($Location == "MA")
      {
        $result = mysql_query("SELECT * FROM employeesMA WHERE name = '$StaffName' ")
            or die("SELECT Error: ".mysql_error());
       } else {
        $result = mysql_query("SELECT * FROM employeesWL WHERE name = '$StaffName' ")
            or die("SELECT Error: ".mysql_error());
       }
        $row = mysql_fetch_row($result);
        $CurrEmpKey = $row[0];
        $CurrStaffName = $row[1];
        $CurrStaffPicURL = $row[2];
        $CurrStaffDescription = $row[3];
        if ($StaffPicFile == "")
            {$StaffPicFile  = $CurrStaffPicURL; } 
        if ($StaffDescription == "")
            {$StaffDescription = $CurrStaffDescription; }
        if     ($_POST['StaffChoice'] == "Update" and $_POST['SubmitRecord'] == true)
            { if ($Location == "MA")
            {
                $result = mysql_query("UPDATE employeesMA SET name = '$StaffName', picture = '$StaffPicFile', bio = '$StaffDescription' WHERE name = '$StaffName' ");
            } else {
                $result = mysql_query("UPDATE employeesWoodlawn SET name = '$StaffName', picture = '$StaffPicFile', bio = '$StaffDescription' WHERE name = '$StaffName' ");
            }
        echo "Employee Updated: " . $StaffName;
        ?>            
        <h2>Record Updated &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <br><br>
        <a href="staffupdate.htm">Return to Update Form</a></h2>
        <?php
        
    }
            
    ?>
    The 1st 3 records in the table are Clay Myers, Lee Myers, Judy Myers. When attempting to update the data for Lee, the table is not changed. There are 2 other employees in the table with the same last name & the 1st employee of the 2 cannot be updated. Is this because of the way that php/MySQL is reading the table? The other employee records tested work without a problem.
    Thanks for the help.

    Bob McP

  2. #2
    Join Date
    Mar 2011
    Posts
    1,136
    When you create a database editor, you always want to insure that the record being changed is uniquely identified, and names are generally a poor choice as an identifier because duplicate names are common, even in relatively small groups. Your database has a field you refer to with the variable $CurrEmpKey, which sounds like a much better choice.
    Similarly, if you let users select a record to edit by looking up the name, you always want to check to see if more than one record exists in the database with the name entered by the user in order to allow the user to select exactly which record they want to edit. Otherwise, if duplicate names do exist, only the first record returned by your SQL query will ever be editable by users, and every time that name is used, your current code would update ALL of the records with that name.

    When I create systems like this, I set up a database listing utility that allows users to select a record to edit from a sortable list of all of the records. This way the user doesn't need to input an exact match for data entries like a name where spelling variations are common. You can let the user sort the list display by name or any other useful field in ascending or descending order to make it easier to locate a particular record, and be assured that the user is editing only the record they actually intend to edit. You might also consider creating separate fields for first and last names to make it easier to locate records.
    Rick Trethewey
    Rainbo Design

  3. #3
    Join Date
    Nov 2008
    Location
    NC, USA
    Posts
    24
    Thanks for the info. I have updated the table to use the EmpKey field as the lookup for the employee record to change. My code for the selection is now:

    $result = mysql_query("SELECT * FROM employeesMA WHERE EmpKey = '$StaffID' ");
    $CurrEmpKey = $row["EmpKey"];

    and to update:

    $result = mysql_query("UPDATE employeesMA SET picture = '$StaffPicFile', bio = '$StaffDescription' WHERE '$CurrEmpKey' = '$StaffID' ");

    However even with using the EmpKey I still cannot get the same 2 records to update. This issue is strange since it is not consistent with all of the records. I have lost track of the amount of time trying to figure this out, searching & reading. Perhaps I still need to have the user select the record to update from a list but I really thought using an exact piece of data from the primary key field would update the record correctly. One other piece I was wondering about is whether the variables are getting cleared out. I am using the unset function at the bottom of the php as follows:

    unset($_POST['StaffID'], $_POST['StaffName'], $_POST['StaffPicFile'] ,$_POST['StaffDescription'], $_POST['Location']);
    unset ($StaffPicFile, $StaffDescription);

    Perhaps I am not clearing the correct variables. Thanks for additional help.

    Bob McP

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles