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

????: NamePros.com http://www.namepros.com/programming/19184-mysql-search-ammount-per-page-total.html
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!

__________________
Online Time Tracking :)

Jeanco

just use some php... something like:

__________________
Ryan Price - Webmaster
www.HostDurham.com - For Hosting | www.jeanco.ca - For Webdesign

adam_uk

yeh i do its called id
????: NamePros.com http://www.namepros.com/showthread.php?t=19184

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 ...

__________________
Online Time Tracking :)

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:

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):

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:

CreativeLogic

That is actually incorrect. It should be:

__________________
Online Time Tracking :)

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.

__________________
Newsback.com -- Commentary and backstories of the news
-----------------------------------------------

adam_uk

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.

__________________
Online Time Tracking :)

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!

__________________
Newsback.com -- Commentary and backstories of the news
-----------------------------------------------