 |
|
|
|||||||
Before you post, read our:  |
|
|
Thread Tools | Rate Thread |
|
|
|
|||||||
|
|
|
Thread Tools | Rate Thread |
10-28-2009, 11:41 AM
|
PM User | #1 |
|
Regular Coder  Join Date: Jul 2007
Location: Velsen Noord, Netherlands
Posts: 127
Thanks: 3
Thanked 0 Times in 0 Posts
 |
search with next/previous button in php/mysql
I have created a search page (for a contact database), but I would like to add an option that if (searching can be done on surname or zipcode), the surname or zipcode has multiple records, the next/previous button should be displayed.
I only have no idea on how to do that. The searchpage I have, does have multiple inputfields, checkboxes and a dropdown box where the contact info is being displayed. Here is the search code I have (I hope I don't have to change the code completely): PHP Code:
|
|
|
|
10-28-2009, 10:54 PM
|
PM User | #2 |
|
Regular Coder Join Date: May 2009
Location: Horn Toad Ville, CA
Posts: 144
Thanks: 7
Thanked 30 Times in 30 Posts
 |
If more than one result is found then display all of the matching results and then let the user check which one they need then continue. You will need to add a query to search by id.
PHP Code:
|
|
|
|
|
10-28-2009, 11:16 PM
|
PM User | #3 |
|
Regular Coder Join Date: May 2009
Location: Horn Toad Ville, CA
Posts: 144
Thanks: 7
Thanked 30 Times in 30 Posts
|
Another option and probably a closer look to what you were going for is to create a variable to keep track of which record you are on although this is not great db query design to do this because you will be fetching more records than you need.
PHP Code:
|
|
|
|
|
10-28-2009, 11:28 PM
|
PM User | #4 |
|
Regular Coder Join Date: May 2009
Location: Horn Toad Ville, CA
Posts: 144
Thanks: 7
Thanked 30 Times in 30 Posts
|
The way I believe it should be done is using a LIMIT in the query to only allow for one record retrieval at a time and another query to count the records available.
Code:
SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" LIMIT $record, 1" Code:
SELECT COUNT(*) FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" " |
|
|
|
|
10-29-2009, 04:53 AM
|
PM User | #5 |
|
Senior Coder  
Join Date: Feb 2009
Location: Snohomish, WA
Posts: 4,061
Thanks: 18
Thanked 660 Times in 652 Posts
|
Yes, Coyote, that's the whole point of paging and the only reasonable way to do it in MySQL queries.
But the missing piece here is that you must *REMEMBER* the query from one page to the next. He is building the query by looking at form field posting values. The next time he gets to the page (that is, when the user clicks on the NEXT button), those form field post valies will *NOT* be there! So the answer is to *save* the query in a session value. And each time on the page you look to see if the SUBMIT button's post value is found. If so, you rebuild the query as he is doing here. If not, you just go get the SQL query you saved in the session value and use it. I'm not a PHP person, so I don't want to try to mangle the PHP code, but the concept is dead simple. The important part here is to have an *ALWAYS THERE* form field post to the page, as an indication that a new search is to be performed, instead of using the one saved in the session value. I generally use a hidden form field: Code:
<form action="search.php" method="post"> <input type="hidden" name="DOSEARCH" value="YES"/> ... Code:
if ( $_POST["DOSEARCH"] == "YES" )
{
... build the query as he is doing now ...
... then SAVE that query in a session value! ...
} else
$sql_query = ...retrieve it from session value...
}
// here you add the paging logic.
$page = ... however you get it ...
$sql_query .= " LIMIT " . ($page_size * ($page - 1 ) ) . ", " . $page_size;
...
|
|
|
|
|
11-03-2009, 10:25 AM
|
PM User | #6 | |
|
Regular Coder Join Date: Jul 2007
Location: Velsen Noord, Netherlands
Posts: 127
Thanks: 3
Thanked 0 Times in 0 Posts
|
Quote:
PHP Code:
PHP Code:
What do I do wrong? |
|
|
|
|
|
11-03-2009, 03:24 PM
|
PM User | #7 |
|
Regular Coder Join Date: Jul 2007
Location: Velsen Noord, Netherlands
Posts: 127
Thanks: 3
Thanked 0 Times in 0 Posts
|
Ok, I've played a little around with the code, and came till the part that I can get 1 record to display and made a select link to display the full contact info of that record.
But when I search, on surname (I know there are multiple records with the same surname) but it only displays 1 records and not all the records with the same surname. How to solve that? Here is the code I played with: PHP Code:
|
|
|
|
|
11-04-2009, 02:27 AM
|
PM User | #8 | ||
|
Regular Coder Join Date: May 2009
Location: Horn Toad Ville, CA
Posts: 144
Thanks: 7
Thanked 30 Times in 30 Posts
|
Quote:
Quote:
So without testing this code I cannot be sure but give this a try... PHP Code:
|
||
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Rate This Thread | |
|
|
