counting total result from mysql, then limiting

robertaccettura

I need to search a mySQL table. But here's what I haven't done before:

I need the total matches, but I don't need them all at once.

I need the number of total matches, and then a limit (as the code below illustrates).

My question is:

Is there a more efficient way than searching the table twice? Can I search once and only pull data as per the limit? How can I do so?

I get the idea that this is not efficient (it's pretty obvious). But what's the nciest way to get the total matches, and only return the limited data.

This function can be called with different limits (0,20; 20,40; 1,3... etc.)

	mysql_connect($server, $db_user, $db_pass) or die("Fatal Error: ".mysql_error());
	mysql_select_db($database);
	// Lets see how many results in total
	$query = mysql_query($querystring.$querystringend) or die(mysql_error());
	// Lets get the intervaled amount
	$more = mysql_num_rows($query);
	$querystring .= " LIMIT $bounds , $interval";
	$query = mysql_query($querystring.$querystringend) or die(mysql_error());
	$thelist = array();
	while ($row = mysql_fetch_array($query)) {
		$item = "";
		if ($row['status'] == "1") {
			$item = array($row['item'], $row['item1'], $row['item2'], $row['item3'], $row['item4'], $row['item5']);
			$gotsomething = 1;	    

			array_push($thelist, $item);
		}
		else if (($row['status']== "0") && $class == "admin") {
			$item = array($row['item'], $row['item1'], $row['item2'], $row['item3'], $row['item4'], $row['item5']);
			$gotsomething = 1;	    

			array_push($thelist, $item);
		}
	}
	mysql_close();

njm

Maybe you could pull all the records in one query to get the total count, and then do the limiting in the while loop?

hth, njm

robertaccettura

Originally posted by njm
Maybe you could pull all the records in one query to get the total count, and then do the limiting in the while loop?

hth, njm

But wouldn't that put all the records into memory?

Isn't that also really inefficicent?

leatherback

Hi,

What is the problem with doing it with selecting all, getting count, and then reselecting?

Unless you have huge databases & very complex queries, there is going to be no (Well, olympic) differences in processing time..

superwormy

$count_result = mysql_query ("SELECT COUNT(*) AS count FROM ... WHERE ... ");

$real_result = mysql_query ("SELECT ... FROM ... WHERE ... LIMIT 30 ");

njm

Originally posted by robertaccettura
But wouldn't that put all the records into memory?

Isn't that also really inefficicent?

It's probably not the most efficient way, depends upon how big your recordsets are - but allows you to do things with one query. As leatherback says, unless you're querying mammoth databases, the difference may not be that noticable.

njm