[SOLVED] mysql_num_rows using limit in sql command

[ahs10]

i have a query that uses LIMIT, but i'd like to get the mysql_num_rows() of that query if the limit wasn't there.  what's the most efficient way to do that?

 

my only solution is...

 

$query = "SELECT * FROM table";

$rcResult = mysql_query($query);

$realCount = mysql_num_rows($rcResult);

$query .= " LIMIT 5";

$result = mysql_query($query);

 

.... i didn't know if there was something more clever than that.

 

any help, guidance, advice, or simple hellos are much appreciated.  thanks!

 

 

[revraz]

I think that's a good idea since it does require two queries to do what you want in SQL.  You could also just pull all records and do your limit with PHP if you just want to do a single query.

[rhodesa]

Well, if you are already going to execute the query to get the number of rows, why wouldn't you just use that resource and LIMIT with PHP?

 

<?php
  $limit = 5;
  $query = "SELECT * FROM table";
  $result = mysql_query($query);
  $count = mysql_num_rows($result);
  for($i =0;$i < $limit && $row = mysql_fetch_array($result);$i++){
    print_r($row);
  }
?>

[Psycho]

Why don't you just ALWAYS use the LIMIT. If there are less records than the limit then it will only pull those. Then just use a mysql_num_rows() after the query to determine how many were actually returned. I don't understand why you need to know the number of records to determine what limit to use.

 

$limit = 5;
$query = "SELECT * FROM table LIMIT $limit";
$rcResult = mysql_query($query);
$realCount = mysql_num_rows($rcResult);
echo "Query limit: $limit<br>Returned count: $realCount";

[rhodesa]

I would guess they are doing a pagination of sorts. Where they only want 5 records, but want to display the total number of possible records.

[revraz]

Then a SELECT COUNT would be more efficient for that purpose.

[ahs10]

that's what i get for trying to simplify my problem... i apologize.

 

i'm doing a paging feature, but executed via ajax.  i need to use the offset and maximum parameters that come with sql's limit, but also know how many rows are in the query without the limit.

 

[ahs10]

Then a SELECT COUNT would be more efficient for that purpose.

 

that's perfect... thank you!!!!!