duanqian1888
duanqian1888
2014-04-05 19:23

无法在MySQL PHP表单中显示记录字段更新结果

已采纳

After pressing "Update" button, It should update and then to display the result of an updated record record (a single updated record with selected columns). However, the page only displays table column names without any records and a few errors. Please help.

<html>
<head>
<strong><font size="6">Sales Log - Main</font><font size="5"><br />
(Transaction Status Update)</font></strong>
</head>
<body bgcolor="#6E6E6E" text="Azure">

<?php
if(isset($_POST['update']))
{
$dbhost = 'localhost';
$dbuser = 'jack';
$dbpass = 'somepassword';
$myDBname = 'sales';
$conn = mysql_connect($dbhost, $dbuser, $dbpass, $myDBname);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$FileNumber = mysql_real_escape_string($_POST['FileNumber']);
$Status = mysql_real_escape_string($_POST['Status']);

//$sql = "UPDATE saleslog SET Status = '".$Status."' WHERE FileNumber = '".$FileNumber."'"; //this worked fine then code below was introduced

$sql = "UPDATE saleslog 
       SET Status = '$Status'
       WHERE FileNumber = '$FileNumber'" ;

mysql_select_db('realestate');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully!
";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<br />
<br />
<td width="100">File Number</td>
<td><input name="FileNumber" type="text" id="FileNumber"></td>
</tr>
<tr>
<td width="100">Status</td>
<td><input name="Status" type="text" id="Status" value="Closed"></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="update" type="submit" id="update" value="Update">
<input name="reset" type="reset" onclick="resetForm(''); return false;" />
</td>
</tr>
</table>
</form>
<?php
}
?>


$sql="SELECT FileNumber, Address, Status FROM $tbl_name";
$result=mysql_query($sql);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>FileNumber</strong></td>
<td align="center"><strong>Address</strong></td>
<td align="center"><strong>Status</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td><? echo $rows['FileNumber']; ?></td>
<td><? echo $rows['Address']; ?></td>
<td><? echo $rows['Status']; ?></td>

</tr>

<?php
}
?>

</table>
</td>
</tr>
</table>

<?php
mysql_close();
?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doujiu1447 doujiu1447 7年前

    Various errors in the code.

    Converted directly to 'mysqli'. I have not changed the logic in any way.

    Tested code: PHP 5.3.18. mysql 5.5 window xp.

    Added table layout.

    No need to delete any lines as i have converted them to comments. Just copy and paste.

    <!DOCTYPE HTML">
    <html>
      <head>
        <meta http-equiv="content-type" content="text/html; charset=UTF8">
        <title>
        </title>
      </head>
    <strong><font size="6">Sales Log - Main</font><font size="5"><br />
    (Transaction Status Update)</font></strong>
    </head>
    <body bgcolor="#6E6E6E" text="Azure">
    <?php
        // always connect to the database...
        $mysqlhost   = 'localhost';
        $mysqluser   = 'jack';
        $mysqlpass   = 'test';
        $myDBname    = 'sales'; // should this be 'realestate'
    
    
        // why select a different database then when you connected? was 'sales'
        //  mysql_select_db('realestate'); /* why? */
    
        $mysqli = new mysqli($mysqlhost, $mysqluser, $mysqlpass, $myDBname);
    
        if(mysqli_connect_errno())
        {
          die('Could not connect: ' . mysqli_connect_error());
        }
    
        // set the database table to use...
        $tbl_name = "`saleslog`";
    
    
        if (isset($_POST['update'])) // we have some record to change...
        {
            // do not need to escape input as we will use prepared query and bind variables
            $FileNumber = $_POST['FileNumber'];
            $Status     = $_POST['Status'];
    
            //$sql = "UPDATE saleslog SET Status = '".$Status."' WHERE FileNumber = '".$FileNumber."'"; //this worked fine then code below was introduced
    
            $sql = "UPDATE $tbl_name
                    SET `Status` = ?
                    WHERE `FileNumber` = ?" ;
    
            $updateQuery =  $mysqli->prepare($sql);
            if ($updateQuery === false) { // drat
                die('updateQuery: '. $mysqli->error);
            }
    
            // bind the variables to the query
            $updateQuery->bind_param('si', $Status, $FileNumber);
    
            $allOk = $updateQuery->execute();
            if (!$allOk) { // drat
                die('updateQuery: '. $updateQuery->error);
            }
    
            if ($mysqli->affected_rows >= 1) {
                echo "Updated data successfully!<br />";
            }
            else {
                echo "FileNumber: $FileNumber was not changed<br />";
            }
        }
        else
        {
        ?>
        <form method="post" action="<?php $_PHP_SELF ?>">
          <table width="400" border="0" cellspacing="1" cellpadding="2">
            <tr><br /><br />
              <td width="100">File Number</td><td>
                <input name="FileNumber" type="text" id="FileNumber"></td>
            </tr>
            <tr>
              <td width="100">Status</td><td>
                <input name="Status" type="text" id="Status" value="Closed"></td>
            </tr>
            <tr>
              <td width="100"> </td><td> </td>
            </tr>
            <tr>
              <td width="100"> </td><td>
                <input name="update" type="submit" id="update" value="Update">
                <input name="reset" type="reset" onclick="resetForm(''); return false;" /></td>
            </tr>
          </table>
        </form>
    <?php
    
    }
    
    // always use 'prepare'
    if (!empty($FileNumber)) {
        $whereClause = " WHERE `FileNumber` = ?";
    }
    else {
        $whereClause = " ORDER BY `FileNumber`";
    }
    
    $sql = "SELECT `FileNumber`, `Address`, `Status`
           FROM $tbl_name
           $whereClause";
    
    $selectQuery = $mysqli->prepare($sql);
    if (!empty($FileNumber)) {
        $selectQuery->bind_param('i', $FileNumber);
        $originalFileNumber = $FileNumber;
    }
    
    $allOk = $selectQuery->execute();
    if (!$allOk) { // drat
        die('selectQuery: '. $selectQuery->error);
    }
    
    // results will be placed in these three variables when we fetch the row later
    $selectQuery->bind_result($FileNumber, $Address, $Status);
    
    ?>
    
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <td>
            <table width="400" border="1" cellspacing="0" cellpadding="3">
            <tr>
                <td colspan="4"><strong>List data from mysql </strong> </td>
            </tr>
    
            <tr>
                <td align="center"><strong>FileNumber</strong></td>
                <td align="center"><strong>Address</strong></td>
                <td align="center"><strong>Status</strong></td>
            </tr>
    
            <?php $rowCount = 0; ?>
            <?php while($selectQuery->fetch()):  // the data will be in the 'bind_result' variables ?>
              <tr>
                <td><?= $FileNumber ?></td>
                <td><?= $Address; ?></td>
                <td><?= $Status; ?></td>
              </tr>
              <?php $rowCount++; ?>
            <?php endwhile; ?>
            </table>
        </td>
    </tr>
    <td>
        <?php if ($rowCount >= 1): ?>
          <strong><?php echo $rowCount; echo $rowCount == 1 ? ' row found' : ' rows found'; ?></strong>
        <?php elseif (!empty($originalFileNumber)): ?>
          <strong>No rows found for FileNumber: <?php echo $originalFileNumber; ?></strong>
        <?php else: ?>
          <strong>No rows found in the table!!!></strong>
        <?php endif ?>
    </td>
    </table>
    </body>
    </html>
    <?php
    $mysqli->close(); // free the database connection -- not needed but harmless
    ?>
    

    Table data:

    FileNumber  Status  Address        
    ----------  ------  ---------------
             1  open    here at home!  
             2  Closed  what ever      
    

    Table layout:

    *DDL Information*/
    -------------------
    
    CREATE TABLE `saleslog` (
      `FileNumber` int(11) NOT NULL,
      `Status` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
      `Address` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`FileNumber`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    
    点赞 评论 复制链接分享

为你推荐