Mysql, search, amount per page and total results

[adam_uk]

hey people

wondered if you could help me

right ive got a sql query that searches a database, now i want to do is do an advanced search so the user can refine it

ive got the ammount per page of results

this is done by adding and removing from the limit 0 , 10 at the end of the sql code

ive done this bit now what i need to do is make a way so if they specify a limit of 40 results get the query to stop when it hits the 40th result if it exists, if you get me

any ideas?
ive thought about a while but im not sure how it would be done if they didnt specify a limit (ie show all the results)

any suggestions weclome

thanks
adam

 

[CreativeLogic]

Do you have a unique id field in your table? If so it could be easy!

 

[Jeanco]

just use some php... something like:

if ($limit == "") {
   CODE FOR CHOOSING A DEFAULT NUMBER TO DISPLAY
}
else {
   CODE FOR DISPLAYING THEIR SELECTED NUMBER
}

 

[adam_uk]

Originally posted by WhSox21
Do you have a unique id field in your table? If so it could be easy!

yeh i do its called id

jeanco, can u show me more that way, coz im looking at thinking your going to place a sql query there, if so i dont think it will work

 

[CreativeLogic]

I'm not sure if this will work or not for what your trying, I have more ideas though too.

Make a query to get the id of the thing found.
ie:
$sql = "SELECT id FROM table WHERE search='$found' ORDER BY id";
$rs = mysql_query($sql, $cn);
$row = mysql_fetch_assoc($rs);
$id = $row['id'];

$sql = "SELECT * FROM table WHERE id<='$id' ORDER BY id";
... return the rest of your results like you normally would ...

 

[nicholas]

Adam, this is really just a case of putting a variable in place of your limiter (in this case, the number 10). THe content of that variable is of course whatever your user chose (from an html form).

So for example, let's call your variable "$limiter", then your sql statement would simply be:

mysql_query("SELECT * FROM your_database WHERE your_conditionals_here = 'your_conditionals_here' LIMIT 0, $limiter");

That's it.

Or, if you'd also like to let the user control at what row number to start counting (the 40) from, just use another variable (let's call it say, $rowstart):

mysql_query("SELECT * FROM your_database WHERE your_conditionals_here = 'your_conditionals_here' LIMIT $rowstart, $limiter");

 

[adam_uk]

thanks for your input

the problem is though that the limit 0, $limiter is already in use for the page numbers

so next page would turn the query from limit, 0 10 to limit 10, 20 and so on

so unless theres another way to do the page numbers im not sure how i would do it

thanks adam

 

[nicholas]

Unless I read you incorrectly (short of you posting your code here), why don't you add the current value of the "limit" variables in your environment (URL) string (like for a GET or POST), and just extract the value from every page, so that say, if you set $limiter to increment by say, 40 records at a time, you just add that for the next page's $rowstart and pass it via the environment string (like a GET or POST).

You want to retain the same quantity of rows to show, so $limiter remains the same throughout.

What you'd like to do however (if I read you correctly) is that you want to dynamically and automatically update the start of the record set you're showing, so $rowstart is what you increment:

 $rowstart += $limiter;

 

[CreativeLogic]

Originally posted by nicholas

mysql_query("SELECT * FROM your_database WHERE your_conditionals_here = 'your_conditionals_here' LIMIT 0, $limiter");

That is actually incorrect. It should be:

mysql_query("SELECT * FROM your_table WHERE your_conditionals_here = 'your_conditionals_here' LIMIT 0, $limiter");

 

[nicholas]

LOL actually that's a just a semantics issue- the use of "your_database" must be misleading indeed but it isn't meant to be taken literally since it's a reference to whatever code is actually used. but please don't let that distract you from the point, which not about the syntax, but rather about one solution of using variables to limit mysql output properly across pages, which in this example is "LIMIT $rowstart, $limiter", and a note to increase $rowstart by $limiter via passage to the URL/environment string (or decrease it appropriately for a link to the "previous" page), and so on.

Note too to make sure $rowstart does not overshoot the number of rows returned.

 

[adam_uk]

Originally posted by nicholas
Unless I read you incorrectly (short of you posting your code here), why don't you add the current value of the "limit" variables in your environment (URL) string (like for a GET or POST), and just extract the value from every page, so that say, if you set $limiter to increment by say, 40 records at a time, you just add that for the next page's $rowstart and pass it via the environment string (like a GET or POST).

You want to retain the same quantity of rows to show, so $limiter remains the same throughout.

What you'd like to do however (if I read you correctly) is that you want to dynamically and automatically update the start of the record set you're showing, so $rowstart is what you increment:

 $rowstart += $limiter;

ahhhhhh

im a nugget!!!! i seriously am

thanks

think uve just awnsered my question

 

[CreativeLogic]

I was trying to make things WAAAY more complicated than they really were.

 

[nicholas]

Hey you're welcome .. don't worry we all go through those days where things like a missing semicolon or dot can give you the fits

Good luck!