duanqinjiao5244
2013-12-02 06:27 阅读 60
已采纳

如何在SQL语句中检索单个数据?

I have a table in my database. The structure of the database is like this :

network table

PhoneModel                              Status  
Cisco Catalyst 2960 8port               Available
Cisco Catalyst 2960 8port               Available
Cisco Catalyst 3750G POE24              Available
Cisco Catalyst 3750E POE-48             Available
Cisco Catalyst 3750E POE-48             Available

I want to create an email notification for this table. For example, if Cisco Catalyst 3750E POE-48 is less than 3, than an email will be sent out to user. I've tried SQL statement like this, but it display two same item if the database consist two available item.

The SQL statement is like this :

$sql = "SELECT PhoneModel, Status FROM network WHERE PhoneModel='Cisco Catalyst 2960   
8port' AND Status='Available'";
$result = mysql_query( $sql ) or die( 'Query failed.'.mysql_error() );

$sql2 = "SELECT PhoneModel, Status FROM network WHERE PhoneModel='Cisco Catalyst 3750G     
POE24' AND Status='Available'";
$result2 = mysql_query( $sql2 ) or die( 'Query failed.'.mysql_error() );

$to       = 'my@email.com';
$subject  = 'Notification of less on stock';
$message = 'Dear IT Infra,<br/><br />Listed below are the accessories currently less in  
stock. <br/>Please prepare to purchase a new one if necessary.';
$headers  = 'From: company.com' . "
";

while($row = mysql_fetch_array($result)) {
    $PhoneModel = $row['PhoneModel'];
    $Available = $row['Available'];
}
if( $row['Available'] < 1 ){
$message .= "<div style='margin:30px 0;'>
            Item : $PhoneModel<br />
            Available: $Available<br /></div>";
}


while($row = mysql_fetch_array($result2)) {
    $PhoneModel = $row['PhoneModel'];
    $Available = $row['Available'];
 }
if( $row['Available'] < 3 ){
$message .= "<div style='margin:30px 0;'>
            Item : $PhoneModel<br />
            Available: $Available<br /></div>";
 }

For above coding, the email should be sent out and contain only 'Cisco Catalyst 3750G POE24 ' but it also have 'Cisco Catalyst 3750G POE24 ' in the email. May I know what is lacking in my coding ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    drd43058 drd43058 2013-12-02 06:36

    In MySQL, you can limit your result set to one row with "Limit"

    SELECT PhoneModel, Status
    FROM network WHERE PhoneModel='Cisco Catalyst 3750G POE24' AND Status='Available'
    LIMIT 1
    

    PS: Here's a link to the MySQL PDO and MySQLi APIs that have superceded mysql_query:

    Here's a good link on why the original MySql API is now deprecated:

    点赞 评论 复制链接分享

相关推荐