Jump to content
Larry Ullman's Book Forums

Recommended Posts

I have the following problem. On the page where I output the results of the query I also have the form (it is the same page). One of the fields is used by users for keyword searches in boolean mode (MATCH... AGAINST). Now if I click the link to the next set of results p=2&s=10 (let's say) I get an error because the content of the form is empty and even if I gave the parameters for "page" and "start" I do not pass the input for user's search/keywords (that input text field). I was thinking to put the input into a hidden field and the value of that hidden field will always be the $_POST for the keywords field. I am not sure this is the best approach, but I guess it is better than encoding the user input and passing it in the url. Maybe I am wrong. Could you please let me know what you think?

 

Also wouldn't be better if we calculate the $start page based on the $page and $display (so we don't have to pass it as parameter in URL)?

 

On a separate note I noticed that using prepared statements there are a few methods that I guess may be used for paginating results, like "mysqli_stmt_store_result" and "mysqli_stmt_data_seek". Is it possible / better to re-write the script using these?

 

I also noticed that there are another methods for multi-query and I was thinking that they may be used very nicely on a pagination script because of the "mysqli_stmt_more_results" and "mysqli_stmt_next_result" which seems to fit perfect for the task. I would appreciate your help. This is probably one good example of beautiful things that may be achieved with prepared questions and cannot be obtained otherwise (multi-query). <suggestion> on the next edition of your book, please extend the prepared statements chapter or please write a book about it.

 

Thank you.

Link to comment
Share on other sites

I have the following problem. On the page where I output the results of the query I also have the form (it is the same page). One of the fields is used by users for keyword searches in boolean mode (MATCH... AGAINST). Now if I click the link to the next set of results p=2&s=10 (let's say) I get an error because the content of the form is empty and even if I gave the parameters for "page" and "start" I do not pass the input for user's search/keywords (that input text field). I was thinking to put the input into a hidden field and the value of that hidden field will always be the $_POST for the keywords field. I am not sure this is the best approach, but I guess it is better than encoding the user input and passing it in the url. Maybe I am wrong. Could you please let me know what you think?

 

In order to paginate any query results, the page must have access to the same data from page to page of results. So you have to pass the user's keywords, too, in the links. Also, search forms generally use GET.

 

Also wouldn't be better if we calculate the $start page based on the $page and $display (so we don't have to pass it as parameter in URL)?

 

Not sure what you mean. The first page is always the start page, it's never calculated. Do you mean the $start variable, as in where the next query should start fetching?

 

On a separate note I noticed that using prepared statements there are a few methods that I guess may be used for paginating results, like "mysqli_stmt_store_result" and "mysqli_stmt_data_seek". Is it possible / better to re-write the script using these?

 

That's for storing the result set from one large query for a single use. Unless you're using permanent connections (which you generally shouldn't), the result won't be stored from one loading of the page to the next.

 

I also noticed that there are another methods for multi-query and I was thinking that they may be used very nicely on a pagination script because of the "mysqli_stmt_more_results" and "mysqli_stmt_next_result" which seems to fit perfect for the task. I would appreciate your help. This is probably one good example of beautiful things that may be achieved with prepared questions and cannot be obtained otherwise (multi-query). on the next edition of your book, please extend the prepared statements chapter or please write a book about it.

 

The multi query option exists whether you're using prepared statements or not. And it's unrelated to the storing of results and next results. The multi query functions let you send multiple MySQL queries to the database using one PHP function call. I almost never use it and normally recommend people don't either. Multi query has no built in error checking, and will automatically execute query 2, 3, and... regardless of what happens with the queries that came before.

 

Thanks for the suggestion of writing more about prepared statements. I'll keep that in mind.

Link to comment
Share on other sites

Thank you for your answer. I have a search page and a result page. I changed the form method from POST to GET as per your advise.

 

If the form has a text field named "keywords" and the value empty, also a submit button named "search" with the value "search database" when I click on the submit button I noticed a few things and I would like to ask a few questions about it.

 

First I noticed that the URL contain a parameter 'submit' and its value, like following:

http://localhost/results.php?keywords=&search=search+database

 

My first question is if it's possible to eliminate the submit button from the URL (and generally if it's possible to eliminate any form element from being passed in the URL)?

 

Than I noticed the spaces are replaced by "+". When we take the value with $_GET the "+" returns to empty space. Now if I use "+" in the keyword (like "+TV -Samsung") as in a boolean search, the "+" get encoded, where "-" and all the other characters (like double quotes) doesn't. If I search for the expression:

 

+"widescreen tv" -Samsung

 

the result is:

 

http://localhost/results.php?keywords=%2B%22widescreen+tv%22+-Samsung&search=search+database

 

I assume that I have to get the keywords value with $_GET['keywords'] and run it through mysqli_real_escape_string() and this is the search result page. Is this the proper way of doing a search-result page? I am asking because initially I was thinking to apply what I learned in the beginning of the book and use POST to handle the form, than redirect to the results page with the parameters appended to the URL and values URL encoded. I assume this is the wrong way of doing it.

Link to comment
Share on other sites

My first question is if it's possible to eliminate the submit button from the URL (and generally if it's possible to eliminate any form element from being passed in the URL)?

 

Not that I know of, not without JavaScript, at least. But if you look at your Google search URLs, this is pretty much SOP.

 

I assume that I have to get the keywords value with $_GET['keywords'] and run it through mysqli_real_escape_string() and this is the search result page. Is this the proper way of doing a search-result page?

 

Yes, that's correct. Or you could use prepared statements, if you'd prefer.

 

I am asking because initially I was thinking to apply what I learned in the beginning of the book and use POST to handle the form, than redirect to the results page with the parameters appended to the URL and values URL encoded. I assume this is the wrong way of doing it.

 

Yeah, I wouldn't do it that way. As I explain in the book, POST is appropriate in many situations, normally where you want to cause a server change or action. GET is appropriate in situations where you're requesting information, such as a search. You also shouldn't use redirections in most situations. If you look at Google, they use GET, the values get passed in URLs, and it's simple and reliable.

 

I forgot to mention earlier, but another reason not to put too much effort into saving pagination results (i.e., the next page of query results) is that most people won't ever go to a second page of search results, so you're spending energy for nothing.

Link to comment
Share on other sites

Thank you for your reply, I put it together and works well. I do not want to beat the death horse, but I have another question related to the issue of pagination.

 

In the script provided we check first for a parameter p for the page, and if it's not provided we query to find out how many records do we have and split the records per page. Certainly, when we anticipate a lot of records and we do not expect (or there are not so important) changes I can certainly see the advantage of querying first for a count of the records than limit the query.

 

Now I am thinking to two situations. First situation, when the changes operated are important (like deleting one of the records), which would require a recount.

Second, when we expect the number of returned records to be small.

 

In these situations, wouldn't be easier or even faster than instead of using two queries, one to do the count the second to retrieve the records, to use just the second query to retrieve the records and use something like

 

$totalRecords = mysqli_stmt_num_rows($stmt);

 

to get the number of records retrieved and operate the pagination based on this value. I understand that this way we have to retrieve all the results every time, which is a more expensive query in case of many records than the quick count and the second limited by LIMIT to the number entered in $display. But for the circumstances mentioned when changes are important and we do not expect a very large number of returned records, will be a mistake to make the pagination this way?

Link to comment
Share on other sites

I can see what you're thinking, and limiting queries is a good thing. You'd need to do some specific testing to benchmark the different between doing one SELECT COUNT(x) query plus a limited SELECT query vs. a full SELECT. However, the important question I would have is: how would you know in advance which approach would be best when queries are dynamically generated on the fly? In other words, how could you tell that this query would have a small result set and doesn't need to be paginated whereas this query does need to be paginated?

Link to comment
Share on other sites

Because we plan for it and this is the client's specifications. It is a job site, so there will be many pages with job listings, split by various criteria like country, domain...etc. Here it make sense the approach learned from your book example.

 

People apply for jobs. The number of people that apply for a certain job it's a lot smaller... we should have less than 2-3 pages with results (as opposed to hundreds and maybe thousands). Here I was thinking to make the pagination based on the number returned by mysqli_stmt_num_rows($stmt). Both are generated on the fly, but it is a safe assumption that we have a small set of results returned for certain type of pages/queries.

 

Thank you for your answer.

Link to comment
Share on other sites

 Share

×
×
  • Create New...