Advanced Search

Results 1 to 2 of 2

Thread: Edit MySQL Column & Update (Part 2)

  1. #1
    Join Date
    Oct 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Edit MySQL Column & Update (Part 2)

    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 Code:
    <?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($query0strlen($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 Code:
    <?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

  2. #2
    Join Date
    Oct 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

Bookmarks

Posting Permissions

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