Results 1 to 4 of 4
Thread: PHP pagination problem
Mar 28, 2012, 14:50 #1
- Join Date
- Oct 2005
- 0 Post(s)
- 0 Thread(s)
PHP pagination problem
well I'm trying to formulate pagination view of my results from a Mysql query in my database. The thing is that the query is not known beforehand, since the user of the database can enter several search terms in text fields of the webpage (text_search.php) and when he hits the "Submit" button, the new page (retrieve_text.php) looks about which text fields have been filled and appends the values in the mysql query like this:
$text_query = ...... WHERE [user-defined conditions]
Now comes the problem: the first page looks Ok, and brings only 25 results, which I want to be the limit of results to show per page. BUT, when I click on the "Next button", I see nothing. The reason? In pages 2,3...N, the $text_query is empty since there are no values submitted through the $_POST variable..., So, in the 1st page, if I print the SQL query I have something like that:
SELECT DISTINCT protein.protein_id, protein.protein_name FROM protein LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id LEFT JOIN families ON protein.protein_families_id = families.families_id WHERE protein.protein_name LIKE '%protein%' ORDER BY protein.protein_id LIMIT 0, 25
SELECT DISTINCT protein.protein_id, protein.protein_name, protein.protein_seq_len, protein.protein_organism, protein_ncbi FROM protein LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id LEFT JOIN families ON protein.protein_families_id = families.families_id WHERE ORDER BY protein.protein_id LIMIT 25, 25
SELECT * from protein WHERE name LIKE '%protein%';
What can I do? Should/Could I somehow "send" the sql query from page to page? I suppose this is BAD practice, but I don't really know how to proceed...
Mar 28, 2012, 16:23 #2
Ideal solution would be to not use post, and instead, use get.
If you can't do that...
You can do it using a simple str_replace.
Build the query and save it to session.
SELECT ? FROM table WHERE x=1 ORDER BY foo
Then for the pagination:
You would do a str_replace on ? with COUNT(table_id)
Then after you calculate the LIMIT,
You would str_replace ? with the correct fields(field1,field2,field3...) and append LIMIT $start,100
Mar 29, 2012, 00:12 #3
- Join Date
- Jul 2006
- Augusta, Georgia, United States
- 16 Post(s)
- 3 Thread(s)
Why does the page need to change the structure o the query? In most cases pagination referrers to a query that does not change except for the limit clause. Anything other than that is not really pagination but bringing in a different set of results. Also, count(*) isn't needed. You can use SQL_CALC_FOUND_ROWS w/ FOUND_ROWS() combination instead.The only code I hate more than my own is everyone else's.
Mar 29, 2012, 09:30 #4