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

Question

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       = '[e-mail]';
$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 ?

Answer 1

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:

• http://www.php.net/manual/en/mysqlinfo.api.choosing.php

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

• https://wiki.php.net/rfc/mysql_deprecation