duanba5777
duanba5777
2013-05-15 02:53

在PHP中为空数据库查询生成错误消息

已采纳

The following code's purpose is to get an input (from a separate HTML file) for the customerID in a database from the user and then display the order number, order date and shipped status for that customerID. The code works fine and I am able to do this, however I also want to create an error message if a customerID that does not exist in the database is entered, instead of just an empty table. I am new to PHP and any help on how to do this is appreciated. (Please note, it has to be in either PHP or mysql)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Prac 2 Task 8</title>
</head>
<body>
<?php
$conn = mysql_connect("localhost", "<username>", "<password>");
mysql_select_db("warehouse<##>", $conn) 
or die ('Database not found ' . mysql_error() );
$input = $_GET["custID"];
$sql = "select orderNumber, orderDate, shipped from orders where customerID = $input 
order by orderDate"; 
$rs = mysql_query($sql, $conn)
or die ('Problem with query' . mysql_error());
?>
<?php 
if (orderNumber != "") { ?> 
<p>the following information was received from the user:</p>
<p><strong>customerID = </strong> <?php echo "$input"; ?><br/><br/>

<table border="1" summary="Order Details">
<tr>
<th>Order Number</th>
<th>Order Date</th>
<th>Shipped</th>
</tr>
<?php
while ($row = mysql_fetch_array($rs)) { ?>
<tr>
<td><?php echo $row["orderNumber"]?></td>
<td><?php echo $row["orderDate"]?></td>
<td><?php echo $row["shipped"]?></td>

</tr>
<?php }}
else {
$txt ="The CustomerID you entered was either invalid or does not exist"; 
echo $txt;?>
<?php }
mysql_close($conn); ?>
</table>
</body></html>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongpao5127 dongpao5127 8年前
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
    <title>Prac 2 Task 8</title>
    </head>
    <body>
    <?php
    $conn = mysql_connect("localhost", "<username>", "<password>");
    mysql_select_db("warehouse<##>", $conn) 
    or die ('Database not found ' . mysql_error() );
    $input = $_GET["custID"];
    $sql = "select orderNumber, orderDate, shipped from orders where customerID = $input 
    order by orderDate"; 
    $rs = mysql_query($sql, $conn)
    or die ('Problem with query' . mysql_error());
    //validate result set here
    if(mysql_num_rows($rs)>0)
    {
    ?>
    <?php 
    if (orderNumber != "") { ?> 
    <p>the following information was received from the user:</p>
    <p><strong>customerID = </strong> <?php echo "$input"; ?><br/><br/>
    
    <table border="1" summary="Order Details">
    <tr>
    <th>Order Number</th>
    <th>Order Date</th>
    <th>Shipped</th>
    </tr>
    <?php
    while ($row = mysql_fetch_array($rs)) { ?>
    <tr>
    <td><?php echo $row["orderNumber"]?></td>
    <td><?php echo $row["orderDate"]?></td>
    <td><?php echo $row["shipped"]?></td>
    
    </tr>
    <?php }}
    else {
    $txt ="The CustomerID you entered was either invalid or does not exist"; 
    echo $txt;?>
    <?php }
    
    }//endif
    else{
    
    //you error message here
    }
    
    mysql_close($conn); ?>
    </table>
    </body></html>
    
    点赞 评论 复制链接分享
  • dora1989 dora1989 8年前

    You have many ways to do this, and this is one of so many:

    1. encapsulate your code into a try-catch so it is easy to manage errors, much better way than use "or die" stuff
    2. verify the validity of your GET and POST variables to avoid SQL injections for security
    3. you may use a "select count(*) ..." before the main query, or just count the quantity of results of the main query (what I put there)

    this gives approx that:

    <body>
    <?php
    $conn = mysql_connect("localhost", "<username>", "<password>");
    mysql_select_db("warehouse<##>", $conn) 
    or die ('Database not found ' . mysql_error() );
    
    try 
    {
      $input = $_GET["custID"];
      // Protect yourself from SQL injection
      if (!is_numeric($input))
        throw new Exception('Error: the customer ID is not a number');
    
      $sql = "select orderNumber, orderDate, shipped from orders where customerID = $input 
      order by orderDate"; 
      $rs = mysql_query($sql, $conn)
        or die ('Problem with query' . mysql_error());
      ?>
      <?php 
      if ( mysql_num_rows($rs) > 0 )
      { ?> 
      <p>the following information was received from the user:</p>
      <p><strong>customerID = </strong> <?php echo "$input"; ?><br/><br/>
    
      <table border="1" summary="Order Details">
      <tr>
      <th>Order Number</th>
      <th>Order Date</th>
      <th>Shipped</th>
      </tr>
      <?php
      while ($row = mysql_fetch_array($rs)) { ?>
      <tr>
      <td><?php echo $row["orderNumber"]?></td>
      <td><?php echo $row["orderDate"]?></td>
      <td><?php echo $row["shipped"]?></td>
    
      </tr>
      <?php }
        else
        {
          echo "There is no results for this customer";
        }
      }
      else {
      $txt ="The CustomerID you entered was either invalid or does not exist"; 
      echo $txt;?>
      <?php }
    }
    catch (Exception $e)
    {
      echo "Error: ".$e;
    }
    mysql_close($conn); ?>
    </table>
    </body>
    
    点赞 评论 复制链接分享

为你推荐