如何查找此sql查询的行数？

$common_query_string = "Select a.INC_KEY,a.STATE, a.FDID, a.INC_DATE, a.INC_NO, a.EXP_NO, concat(a.NUM_MILE,' ', a.STREET_PRE,' ', a.STREETNAME,' ', a.STREETTYPE,' ',
          a.STREETSUF, ' ',a.APT_NO,' ', a.CITY,' ', a.STATE_ID,' ', a.ZIP5,' ', a.ZIP4) as address, a.latitude, a.longitude, 
          ( 3959 * acos( cos( radians($lat) ) * cos( radians( a.latitude ) )
          * cos( radians( a.longitude ) - radians($lon) ) 
          + sin( radians($lat) ) * sin( radians( a.latitude ) ) ) ) AS distance,
          a.INC_TYPE, a.FF_DEATH, a.OTH_DEATH, a.PROP_LOSS , a.CONT_LOSS,(b.CODE_DESCR) as INC_CODE_DESC, (d.CODE_DESCR) as CAUSE_CODE_DESC";


    $query = sprintf("$common_query_string ,'nfirs' as SOURCE
      From
        (Select * From incident
        Where latitude > '%s' And latitude < '%s' 
        And longitude > '%s' And longitude < '%s' %s) 
        As a , codelookup b,codelookup d
  Where a.INC_TYPE = b.code_value AND b.fieldid = 'INC_TYPE' 
  And  a.CAUSE_IGN  = d.code_value AND  d.fieldid = 'CAUSE_IGN'
      having distance < '%s'
       ", mysql_real_escape_string($minLat), mysql_real_escape_string($maxLat), mysql_real_escape_string($minLon), mysql_real_escape_string($maxLon), mysql_real_escape_string($query_condition), mysql_real_escape_string($radius));

3 个解决方案

#1

mysql_num_rows($result); 

SELECT COUNT(*) FROM yourtable your join yourcondition;

you have to use this :

 *$final_Query='select count(*)as count from ('.$query.')*

and palce this below
Where a.INC_TYPE = b.code_value AND b.fieldid = 'INC_TYPE' 
And  a.CAUSE_IGN  = d.code_value AND  d.fieldid = 'CAUSE_IGN'
having distance < '%s, mysql_real_escape_string($minLat), mysql_real_escape_string($maxLat), mysql_real_escape_string($minLon), mysql_real_escape_string($maxLon), mysql_real_escape_string($query_condition), mysql_real_escape_string($radius));

#2

mysql_num_rows($result);

gives you the number of selected rows by the query result.

通过查询结果为您提供所选行的数量。

SELECT COUNT(*) FROM yourtable your join yourcondition;

Get total rows when LIMIT is used...

使用LIMIT时获取总行数...

If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

如果您使用了LIMIT子句但想知道没有它的行数,请在查询中使用SQL_CALC_FOUND_ROWS,然后使用SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS FROM yourtable your join yourcondition 
   LIMIT 10;

SELECT FOUND_ROWS();

#3

As you're using the older mysql_ methods, you can use mysql_num_rows() after you've executed the query:

当您使用较旧的mysql_方法时,可以在执行查询后使用mysql_num_rows():

$result = mysql_query($query);
$count = mysql_num_rows($result);

Side Note (mysql_ methods are being deprecated)
I would suggest refactoring your code to use the recommended mysqli_ or PDO methods instead. You can perform the same types of queries in both, but updating will grant you the ability to object-oriented style code, prepared statements which can help improve readability, and also further secure your code against SQL-injection.

附注(mysql_方法已被弃用)我建议重构代码以使用推荐的mysqli_或PDO方法。您可以在两者中执行相同类型的查询,但更新将授予您面向对象样式代码的能力,准备好的语句可以帮助提高可读性,还可以进一步保护代码免受SQL注入。

Here's the same count-syntax with mysqli_num_rows():

这与mysqli_num_rows()的计数语法相同:

$result = mysqli_query($dbLink, $query);
$count = mysqli_num_rows($result);

UPDATE (counting directly in the query)
To get a total number of rows directly in the query, you can use MySQL's COUNT():

UPDATE(直接在查询中计算)要直接在查询中获取总行数,可以使用MySQL的COUNT():

SELECT
    COUNT(*) AS `count`
FROM
    (Select * From incident Where latitude > '%s' And latitude < '%s' And longitude > '%s' And longitude < '%s' %s) As a, codelookup b,codelookup d
WHERE
    a.INC_TYPE = b.code_value AND b.fieldid = 'INC_TYPE' AND  a.CAUSE_IGN  = d.code_value AND  d.fieldid = 'CAUSE_IGN'
HAVING
    distance < '%s'

#1

mysql_num_rows($result); 

SELECT COUNT(*) FROM yourtable your join yourcondition;

you have to use this :

 *$final_Query='select count(*)as count from ('.$query.')*

and palce this below
Where a.INC_TYPE = b.code_value AND b.fieldid = 'INC_TYPE' 
And  a.CAUSE_IGN  = d.code_value AND  d.fieldid = 'CAUSE_IGN'
having distance < '%s, mysql_real_escape_string($minLat), mysql_real_escape_string($maxLat), mysql_real_escape_string($minLon), mysql_real_escape_string($maxLon), mysql_real_escape_string($query_condition), mysql_real_escape_string($radius));

#2

mysql_num_rows($result);