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 关于#目标检测#的问题：大概就是类似后台自动检测某下架商品的库存，在他监测到该商品上架并且可以购买的瞬间点击立即购买下单
  • ¥15 神经网络怎么把隐含层变量融合到损失函数中？
  • ¥30 自适应 LMS 算法实现 FIR 最佳维纳滤波器matlab方案
  • ¥15 lingo18勾选global solver求解使用的算法
  • ¥15 全部备份安卓app数据包括密码，可以复制到另一手机上运行
  • ¥20 测距传感器数据手册i2c
  • ¥15 RPA正常跑，cmd输入cookies跑不出来
  • ¥15 求帮我调试一下freefem代码
  • ¥15 matlab代码解决，怎么运行
  • ¥15 R语言Rstudio突然无法启动