duanqian1888 2014-04-05 19:23
浏览 27
已采纳

无法在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 2014-04-07 04:52
    关注

    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
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

悬赏问题

  • ¥15 如何在scanpy上做差异基因和通路富集？
  • ¥20 关于#硬件工程#的问题，请各位专家解答！
  • ¥15 关于#matlab#的问题：期望的系统闭环传递函数为G(s)=wn^2/s^2+2¢wn+wn^2阻尼系数¢=0.707，使系统具有较小的超调量
  • ¥15 FLUENT如何实现在堆积颗粒的上表面加载高斯热源
  • ¥30 截图中的mathematics程序转换成matlab
  • ¥15 动力学代码报错，维度不匹配
  • ¥15 Power query添加列问题
  • ¥50 Kubernetes&Fission&Eleasticsearch
  • ¥15 報錯：Person is not mapped，如何解決？
  • ¥15 c++头文件不能识别CDialog