Edit MySQL Column & Update (Part 2)

[asengr]

Hey guys,
I just recently picked up PHP about 2 months ago. Reason being was because I wanted to create a typical spatial(google maps api) searching structure that held values for a theoretical company. All of these aspirations are for a project that I'm working on for school. Right now one of my overseers is helping me develop the code right now. We ran into a snag with modifying the MySQL database results through an automated method. From what I've seen online, coders like to modify DB values though calling each column individually through the code. We devised a way to call all of the data with functions that took all of the column values. But we can't seem to do this for editing current values already placed in the database.

The PHP structure is comprised of one main function page (Andy Harris's Technique) and then the functions are "included" into separate PHP pages that do different things. Here is the code for the function page.

<?php

function connectToDB(){
//Hidden Info. All you have to know about this function
//it is called to connect the database.  Also, the 
//variable "$conn" is specified in here.
}

function selectAllCustomerRecords(){
    global $conn;
    if (!$conn) {
        print "<p>Connection does not exist!</p>";
        return 0;
    }
    $sql = "SELECT * FROM Customer WHERE 1";
    $result = mysql_query($sql);
    if (!$result) {
        print "<p>" . mysql_error() . "</p>";
        die (mysql_error());
    }
    return $result;
}

function getCustomerName($customerID){
    if (!$customerID){
        return 0;
    }
    global $conn;
    if (!$conn) {
        print "<p>Connection does not exist!</p>";
        return 0;
    }
    $sql = 'SELECT `FirstName`, `LastName` FROM `Customer` WHERE `CustomerID` = \'' . $customerID . '\'';     
    $result = mysql_query($sql);
    if (!$result) {
        return 0;
    }
    $row = mysql_fetch_assoc($result);
    foreach($row as $name=>$value){
        if ($name == "FirstName"){
            $fName = $value;
        }
        if ($name == "LastName") {
            $lName = $value;
        }
    }

    return "$fName $lName";
}

function printSimpleQueryResults($conn, $sql){
    $result = mysql_query($sql, $conn) or die (mysql_error());
    while ($row = mysql_fetch_assoc($result)){
        foreach ($row as $name=>$value){
            print "$name: $value <br />\n";
        }
        print "<br />\n";
        }
}

function printTableQueryResults($conn, $sql){
    $result = mysql_query($sql, $conn) or die (mysql_error());
    $printedHeaders = false;
    print "<table border=1>\n";
    while ($row = mysql_fetch_assoc($result)){
        if (!$printedHeaders)
        {
            print "<tr>\n";
            foreach (array_keys($row) as $header)
            {
                if (!is_int($header))
                {
                    print "<th>$header</th>";
                }
            }
            print "</tr>\n";
            $printedHeaders = true;
        }
        
        print "<tr>\n";
        foreach ($row as $name=>$value){
            switch($name){
            case "CustomerID":
                $customerID = $value;
                print "<td><form action=\"customerDetail.php\" 
                    method=\"POST\">";
                                print "<button type=\"submit\"
                    name=\"CustomerID\"
                    value = \"" . $value . "\">"
                    . $value . "</button>\n";
                print "</td></form>";
            break;
            case "JobID":
                print "<td><form action=\"jobDetail.php\" 
                    method=\"POST\">";
                print "<button type=\"submit\"
                    name=\"ShowJob\"
                    value = \"" .$value . "\">"
                    . $value . "</button>";
                print "</td></form>";
            break;
            default:
                if ($value){
                    print "<td>$value</td>\n";
                } else {
                    print "<td><i>null</i></td>\n";
                }
        }
        }
        print "</tr>\n";
    }

}

function editTable($conn, $sql){
    $result = mysql_query($sql, $conn) or die (mysql_error());
    $printedHeaders = false;
    
    print "<table border=1>\n";
    while ($row = mysql_fetch_assoc($result)){
        if (!$printedHeaders)
        {
            print "<tr>\n";
            foreach (array_keys($row) as $header)
            {
                if (!is_int($header))
                {
                    print "<th>$header</th>";
                }
            }
            print "</tr>\n";
            $printedHeaders = true;
        }
        
        print "<tr>\n";
        foreach ($row as $name=>$value){
        switch($name){
            case "CustomerID":
            case "JobID":
                print "<td><input type= \"text\" 
                    name = \"" . $name . "\" value=\"" 
                    . $value . "\" readonly /></td>\n";
            break;
            default:
                print "<td><input type= \"text\" 
                    name = \"" . $name . "\" value=\"" 
                    . $value . "\" /></td>\n";
        }
        }
        print "</tr>\n";
    }
    print "</table>\n";
}

function updateRecord($conn, $tableName, $fields, $vals){
    $keyname = $fields[0];
    $keyVal = $vals[0];
    $query = "";

    $query .= "UPDATE $tableName SET ";
    for ($i=1; $i < count($fields); $i++){
        $query .=$fields[$i];
        $query .= " = '";
        $query .= $vals[$i];
        $query .= "',";
    }

    //remove last comma
    $query = substr($query, 0, strlen($query -2));

    $query .= " WHERE $keyName = '$keyVal'";

    $result = mysql_query($query, $conn);
    if ($result){
        $output = "<p>Record Updated</p>\n";
    } else {
    $output = "<h3>there was a problem...</h3><pre>$query</pre>\n";
    }


    return $output;
}
?>

I noticed HERE that tomyknoker modified each column individually. Here is the PHP code for the page (customerDetail.php) that we want to pull up SQL queries as well as edit them through an "editCustomer" button. The button will replace all data in each field/column with a modifiable text box. Note that the previous value in the field is the default value in the text box. Also note that security is probably less focused under this scenario because the system would, "theoretically" be hosted on a localized basis. Here is the code involving customerDetail.php:

<?php include "thePageContainingAlltheFunctions.php";
$conn = connectToDB();

    $searchBy = filter_input(INPUT_POST, "CustomerID");
    if($searchBy) {
        $queryString = mysql_real_escape_string($searchBy);
        $table = "Customer";
        $column = "CustomerID";
        $mode = "read";
    }

    $searchBy = filter_input(INPUT_POST, "editCustomer");
    if($searchBy) {
        $queryString = mysql_real_escape_string($searchBy);
        $table = "Customer";
        $column = "CustomerID";
        $mode = "edit";
    }
    
    $searchBy = filter_input(INPUT_POST, "saveCustomer");
    if($searchBy) {
        $queryString = mysql_real_escape_string($searchBy);
        $table = "Customer";
        $column = "CustomerID";
        $mode = "save";
    }
?>
</head>
<body>
<h1 align="center">Customer Detail</h1>
<?php

if($mode == "save"){
while(list($key,$val) = each($_POST)){
    $fieldName = mysql_real_escape_string($key);
    $value = mysql_real_escape_string($val);

    $fields[] = $fieldName;
    $values[] = $value;
    for ($i=0;$i<count($value);$i++){
        print "$fields[$i]<br /><br />";;
    }
    }

    print updateRecord($conn, $table, $fields, $values);
}




    $sql = "SELECT * FROM `" . $table . "` WHERE `" . $column . "` = '" . $queryString . "'";

switch($mode){
    case("read"):
    case("save"):
        print "<form action=\"customerDetail.php\" method=\"POST\">
            <button type=\"submit\" name=\"editCustomer\" 
            value = \"" .$queryString . "\">
            Edit Customer Information</button>\n";
        printTableQueryResults($conn,$sql);
        print "    </form>\n";
    break;
    case("edit"):
        print "<form action=\"customerDetail.php\" method=\"POST\">\n";
        print "<button type=\"submit\" name=\"saveCustomer\" 
            value = \"" . $queryString . "\">
            Save Customer Information</button>\n";
        editTable($conn, $sql);
        print "</form>\n";
    break;
}
?>

Also please note that some of the code was changed in order to find the problem. We never did . Right now, the output looks something like THIS. Whatever help is greatly appreciated. Thanks

[asengr]

If there are any questions for further explanation, please do not hesitate to ask.