MySQL Limit without limit

dcav

This may sound confusing and unnecessary to some, but it is pretty much required for me to be able to make limit more flexible.

SELECT * FROM myTable LIMIT 0, 10 (gets first 10 records)

What I need is:
SELECT * FROM myTable LIMIT 0, ROWCOUNT (gets all records)

The problem is I'm trying to find what to put for ROWCOUNT without needing a second query to determine ROWCOUNT.

I know I can just do SELECT * FROM myTable, but this will cause me to duplicate alot of data.

Therefore, I really just want to know how to get ROWCOUNT

Thanks in advance 🙂

laserlight

I know I can just do SELECT FROM myTable, but this will cause me to duplicate alot of data.

What do you mean? A SELECT FROM myTable is equivalent to a SELECT * FROM myTable LIMIT 0, $ROWCOUNT, where the value of $ROWCOUNT is the number of rows in the table.

Perhaps you are looking to use DISTINCT or GROUP BY?

dcav

Thanks for the reply, but maybe I didn't phrase it correctly.

SELECT * FROM myTable (let's say 200 records)

SELECT * FROM myTable LIMIT 0, XXX (I need this to be the same 200 records)

My question is: what is XXX

When I said about duplicating the data, it's because I don't want two separate queries to handle this. I'm using the LIMIT for pagination (duh...)

In other words, I don't want two queries, one to get ALL and one to get within the LIMIT. I want to have one query for GET ALL and GET ALL LIMIT

And my query is really complicated, but what I describe above is really what I need.

laserlight

SELECT * FROM myTable (let's say 200 records)

SELECT * FROM myTable LIMIT 0, XXX (I need this to be the same 200 records)

My question is: what is XXX

You cannot know that without making an extra query, so you should just use SELECT * FROM myTable.

In other words, I don't want two queries, one to get ALL and one to get within the LIMIT. I want to have one query for GET ALL and GET ALL LIMIT

Use SELECT * FROM myTable. Use PHP to manipulate the result set such that it simulates a LIMIT. If this is not possible (or more likely, not practical) then just do two queries.

712011m4n

$mysql = "SELECT * FROM myTable";
$result = mysql_query($mysql);

$numFields = my_sql_num_fields($result);

$mysql = "SELECT * FROM myTable LIMIT 0, ROWCOUNT ($numFields);";

basically....I mean, the logic is there....just watch your syntax for mini errors.
:xbones:🆒:eek::rolleyes:😕

dagon

why not write a function that adds the limit if needed, but leaves it out otherwise

function get_rows($limit = TRUE,$rows){

$q = 'Select * from foo';

if($limit){
	$q .= ' LIMIT 0, '.$rows;
}

	//run $q return results.
}

?>

712011m4n

dagon;10972085 wrote:
why not write a function that adds the limit if needed, but leaves it out otherwise
function get_rows($limit = TRUE,$rows){

$q = 'Select * from foo';

if($limit){
	$q .= ' LIMIT 0, '.$rows;
}

	//run $q return results.
}

?>

See. Now thats perfect!