CODEWITHSUNDEEP
phphtmlsql
Timothy Wong
Timothy Wong

Reputation: 95

updating database update

I am having trouble getting the database to update. Is there something wrong with my sql update statement? I checked the sql statement and it says that there were no records in the database. I am not sure what to do.

<!-- template for mySql database access. -->
    <!DOCTYPE html>
    <html>
       <head>
          <title>CRUD</title>
          <link href="/sandvig/mis314/assignments/style.css" rel="stylesheet" type="text/css">
       </head>
       <div class="pageContainer centerText">
          <h3>CRUD (Create, Read, Update, & Delete) Database</h3>
          <?php
          //include database connection
          include("DatabaseConnection2.php");

          //connect to database
          $link = fConnectToDatabase();

          //Retrieve parameters from querystring and sanitize
          $nameF = fCleanString($link, $_GET['nameF'], 15);
          $nameL = fCleanString($link, $_GET['nameL'], 15);
          $deleteID = fCleanNumber($_GET['deleteID']);
          $updateID = fCleanNumber($_GET['updateID']);
          $updateID2 = fCleanNumber($_GET['updateID2']);



           //Populate Textbox  
          if (!empty($updateID)) {
             $sql = "SELECT NameL, NameF
                     FROM customertbl
                     WHERE custID  = '$updateID'";
              mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));

             $result = mysqli_query($link, $sql)
                  or die('SQL syntax error: ' . mysqli_error($link));
             $row = mysqli_fetch_array($result); 
             $strFName2 = $row[NameF];
             $strLName2= $row[NameL];





          }




          ?>
          <hr>
          <form class="formLayout">
             <div class="formGroup">
                <label>First name:</label>
                <input name="nameF" type="text" autofocus value="<? echo $strFName2; ?>">
             </div>
             <div class="formGroup">
                <label>Last name:</label>
                <input name="nameL" type="text"  value="<? echo $strLName2; ?>">
             </div>
             <div class="formGroup">
                <label> </label>
                <button>Submit</button>
                <input type="hidden" name="updateID2" value="<? echo  $updateID; ?>">
             </div>
          </form>
          <?php




        //Update
          if (!empty($updateID2))
          {

             $sql = "UPDATE customertbl
                     SET NameL = '$strFName2', NameF ='$strLName2'
                     WHERE custID = '$updateID2' ";
             mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));


          }    


          //Insert
          if (!empty($nameF) && !empty($nameL)) {
             $sql = "Insert into customertbl (NameL, NameF)
                    VALUES ('$nameL', '$nameF')";
             mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));
          }

          //Delete
          if (!empty($deleteID)) {
             $sql = "Delete from customertbl WHERE CustID= '$deleteID' ";
             mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));
          }
          //List records
          $sql = 'SELECT custID, NameF, NameL
                    FROM customertbl order by custID';




          //$result is an array containing query results
          $result = mysqli_query($link, $sql)
                  or die('SQL syntax error: ' . mysqli_error($link));

          echo "<p>" . mysqli_num_rows($result) . " records in the database</p>";
          ?>
          <table class="simpleTable">
             <tr>
                <th>Cust. ID</th>
                <th>F. Name</th>
                <th>L. Name</th>
                <th>Delete</th>
                <th>Update</th>
             </tr>
             <?php
             // iterate through the retrieved records
             while ($row = mysqli_fetch_array($result)) {
                //Field names are case sensitive and must match
                //the case used in sql statement
                $custID = $row['custID'];
                echo "<tr>
                         <td>$custID</td>
                         <td>$row[NameF]</td>
                         <td>$row[NameL]</td>
                         <td><a href='?deleteID=$custID'>Delete</a></td>
                         <td><a href='?updateID=$custID'>Update</a></td>
                     </tr>";
             }
             ?> 
          </table>
       </div>
    </body>
    </html>

Upvotes: 1

Views: 63

Answers (1)

R Hickman
R Hickman

Reputation: 66

The offending code block

    //Update
      if (!empty($updateID2))
      {

         $sql = "UPDATE customertbl
                 SET NameL = '$strFName2', NameF ='$strLName2'
                 WHERE custID = '$updateID2' ";
         mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));
      }

makes references to variables $strFName2 and $strLName2 which are variables that are only populated conditionally.

       //Populate Textbox  
      if (!empty($updateID)) {
         $sql = "SELECT NameL, NameF
                 FROM customertbl
                 WHERE custID  = '$updateID'";
          mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));

         $result = mysqli_query($link, $sql)
              or die('SQL syntax error: ' . mysqli_error($link));
         $row = mysqli_fetch_array($result); 
         $strFName2 = $row[NameF];
         $strLName2= $row[NameL];
      }

Since the variables $strFName2 and $strLName2 are undefined during the UPDATE SQL query, you're not seeing the desired results.

The query should reference $nameF and $nameL since those variables are always defined (not contained within a conditional) and the form inputs use nameF and nameL in their name attributes. 

$sql = "UPDATE customertbl
    SET NameL = '$nameF', NameF ='$nameL'
    WHERE custID = '$updateID2';";

You also need to fix your DELETE query to reference the column custID and not CustID as it appears your schema uses the former.

$sql = "Delete from customertbl WHERE custID= '$deleteID' ";

Upvotes: 1

Related Questions