MySQL/phpMyAdmin查询行计数(1行) vs mysql_num_rows‘计数(0行计数)

Question

下面是有问题的代码：

<?php // If search option is selected
 include 'db_connect.php';

 database_connect();
 $limit = 5;  // No of result per page
 $criteria = 1; // Search criteria
 $allResults=array();  // Array of results
 $searchOption = $_REQUEST['searchoption'];
 $searchField = $_REQUEST['searchfield'];
 $searchField = str_replace('+',' ', $searchField) ;
 $dbcolumn = "user_id";
 $dbtable = "profiles";
 $order = "user_id";

 if ($searchField){
    if ($searchOption == "People"){
    $words = explode(' ', trim($searchField));
    $searchField = $words[0];
    $dbcolumn = "profile_display_name";
    $dbtable = "profiles P, user_roles U, roles R";
    $order = "profile_first_name";
    $criteria =     " P.user_id = U.user_id AND U.role_id = R.role_id 
                    AND(`profile_display_name` LIKE '%$searchField%'   
                    OR `profile_first_name` LIKE '%$searchField%'   
                    OR `profile_last_name` LIKE '%$searchField%' 
                    OR `profile_email` LIKE '%$searchField%' 
                    OR `profile_state` LIKE '%$searchField%' 
                    OR `profile_zip_code` LIKE '%$searchField%'
                    OR `business_summary` LIKE '%$searchField%')";
    }
    //focus here folks (my comment to readers, not actual part of code
    else if ($searchOption == "Jobs"){
    $dbcolumn = "job_title";
    $dbtable = "job_postings";
    $order = "`created_date` DESC";
    $criteria = "(`job_title` LIKE '%$searchField%'   
    OR `job_description` LIKE '%$searchField%'   
    OR `client_name` LIKE '%$searchField%' 
    OR `city` LIKE '%$searchField%' 
    OR `state_abbr` LIKE '%$searchField%' 
    OR `zipcode` LIKE '%$searchField%') 
    AND `job_status` = 'Open'";
    }
    else if($searchOption == "News"){
    $dbcolumn = "";
    $dbtable = "";
    $order = "";
    $criteria = "1";   
    }

  //Pagination  
 if($dbtable){
    $rs = mysql_query("SELECT * FROM $dbtable WHERE $criteria");
    $no_of_rec =  mysql_num_rows($rs);
    $page = ($_REQUEST['page'])? $_REQUEST['page'] : 1;
    $no_of_pages = ceil($no_of_rec/$limit);
    $offset  = ($page - 1)*$limit;

    echo "pagination #rows: $no_of_rec ";
 } 
}

// If the user enters a value in the search
if ($searchField){ 
   $resultcount = 0;
   if($dbtable)   {
    //  Search query executes
    $query = "SELECT * FROM $dbtable WHERE $criteria ";
    $query .= "ORDER BY $order ";
    $query .= "LIMIT $offset, $limit";
    $result = mysql_query($query) or die(mysql_error());  
    $resultcount = mysql_numrows($result);  

    echo "query: $query ";
    echo "result: $result ";
    echo "result count: $resultcount ";     
 }

 if ($resultcount <= 0){
     $allResults[] = "No match found";
 }
 else{
// Get search results from database
    while ($row = mysql_fetch_array($result)){
$allResults[]=$row;
  }

 }
}
    else{
     $allResults[] = "No value entered";
    }
  ?>

下面是echo语句显示的内容：pagination #rows: 0 query: SELECT * FROM job_postings WHERE (`job_title` LIKE '%Help Wanted%' OR `job_description` LIKE '%Help Wanted%' OR `client_name` LIKE '%Help Wanted%' OR `city` LIKE '%Help Wanted%' OR `state_abbr` LIKE '%Help Wanted%' OR `zipcode` LIKE '%Help Wanted%') AND `job_status` = 'Open' ORDER BY `created_date` DESC LIMIT 0, 5 result: Resource id #36 result count: 0

(我保留了echo打印输出的原样，这样您就可以看到我看到了什么以及我是如何看到它的。)

当我在MySQL和phpMyAdmin中运行查询时，我得到了我要查找的行。

有人能解释一下为什么mysql_numrows()认为结果为0吗？

我已经在PHP.net上查过了，我也用谷歌搜索过了。到目前为止还没有发现。

非常感谢。

Answer 1

此行中有一个输入错误

 $resultcount = mysql_numrows($result); 

您应该调用mysql_num_rows()

您也可以使用此代码

$isExist = mysql_query("Select count(id) from ..."); 
$r = mysql_fetch_array($isExist);
if($r['COUNT(id)'] > 0){
//item exists
}else{
//item doesnt exist
}