phillw Posted September 13, 2009 Share Posted September 13, 2009 Hi, honest - I've googled - looked etc !! I have a search routine using FULLTEXT which works fine. But the only pagination ones for display all use LIMIT, which I can't tag onto FULLTEXT. Obviously, I don't want to keep calling a fulltext search of the DB, so I've got an array with the results in. So far, so good. What I can't find anywhere is how do I get search.php to call display.php (which is self-calling, as they page up & down). This has got to be a noobie question !! - But, I can't find it anywhere !!! Regards, Phill. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/ Share on other sites More sharing options...
trq Posted September 13, 2009 Share Posted September 13, 2009 include ? Your question isn't exactly clear. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917580 Share on other sites More sharing options...
ToonMariner Posted September 13, 2009 Share Posted September 13, 2009 why can't you apply limit on your search? http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html that document states you can... Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917586 Share on other sites More sharing options...
phillw Posted September 13, 2009 Author Share Posted September 13, 2009 thanks ... i ermmmm think I understand.. From the HTML page with the form in, I would set the action to display.php and the top of display.php include_once(search.php) Would this cause display.php to call search.php the 1st time it is called using the action button from the html code, but when display.php calls itself when the customer presses <previous>, <next> it would not then re-call search.php ? To explain hopefully a bit more clearly... An HTML page has a form in it, where customer can type in the search question - this variable is $test. Upon hitting the submit button, it needs to call search.php which returns $result ONCE It then goes onto display.php which slices the result from search.php into page size chunks and displays them. display.php calls itself as they go forwards & backwards through the results, taking care of offsetting within the $result array returned by search.php. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917591 Share on other sites More sharing options...
phillw Posted September 13, 2009 Author Share Posted September 13, 2009 why can't you apply limit on your search? http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html that document states you can... Two reasons 1) I don't want to be doing full text searches of the database repeatedly, for the same query (overhead) 2) - I couldn't get the LIMIT function to work with fulltext, as I already have a multiple ORDER BY at the end of the SELECT. $q="SELECT * FROM $table WHERE MATCH $fulltext AGAINST $test ORDER BY Master_Group,Sub_Group,Description,Dimensions"; Phill. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917598 Share on other sites More sharing options...
ToonMariner Posted September 13, 2009 Share Posted September 13, 2009 if you are paginating your results then you need to run two queries - 1 to get the count of all the rows that meet criteria and 2 to get a set of results (10 rows). its fine to run these queries in the next call to the page (just altering which range of rows to return) - not limiting the number of rows will mean your query takes A LOT longer to run and use MORE resource to complete Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917611 Share on other sites More sharing options...
phillw Posted September 13, 2009 Author Share Posted September 13, 2009 More about performance: Fulltext search in MySQL isn't slow really. It's slower than normal index selects, but that's not noticable. I'm playing tables that each contains ca 4million records, about 6GB of text that needs to be indexed. The problem lies in the optimization of the queries, that use the MATCH() and AGAINST() functions. So far as I found out MySQL can use in a query only one index. The optimizer looks for the index that will possibly give the smallest amount of rows, then goes trough all of these, and removes those records, that fall out because of the other WHERE statements. When you enter a fulltext search, the server _has_ to use that index. Any other statments could be applied only to the rows that were returned by the search. If you have lots of records, most of the searches will return lots of results. The rest of your query will be executed like if you were executing it against a table that contains the results, and no indexes - obviously that will be processed sequentially. Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied. If you _have_ to use the fulltext, these are some hints how you could get quite good results: - Try to avoid any group, where, order and any statements for what it's necessary to get all the results. I know, usually this is impossible. - If you need to show the results on a web page, try to find other methods to get the total number of results than using SQL_CALC_FOUND_ROWS. Better if you don't tell the user at all how many results there are - If indexing text that is in other language than english, before doing anything create a stopword file for your language! (That could reduce index size about 30%) But most important: think a lot, before you decide to use fulltext search! My understanding of that comment from the MySQL Reference manual seems to me that ... Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied. Thus, I don't really see the advantage in either getting the total number of rows, nor the use of the limit function ? - I may as well take the hit, once, and be done with it. Or, am I missing something ? My array isn't going to overflow what is allowed in terms of records it will hold - In the same manual people have been up at 400,000 records returned into an array - I don't need that many !!! Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917617 Share on other sites More sharing options...
phillw Posted September 13, 2009 Author Share Posted September 13, 2009 So, as my limited understanding is this ... So, you want to search for the word 'safety' in the table 'text'. And, you want to limit it to chunks of 10 records so as to display it in pages .... SELECT * FROM table WHERE text = safety LIMIT x, y ... 1st time: x = 1, y =10 2nd time: x = 11, y = 10 3rd time: x = 21, y = 10 . . you get the drift .. 15th time: x = 151, y = 10 So what ? - I hear you ask ... Well, 1st time - MySQL dutifully trots off and gets 10 records .. 2nd time - it gets 20 records & throws away the 1st 10 3rd time - it gets 30 records & throws away the 1st 20 . . 15th time - it gets 160 records & throws away the 1st 150 .. Your poor little Server !!!! Oh, and SQL_CALC_NUM_ROWS makes it get them all, as well - just to rub salt in the wound !!! At least SELECT * FROM table WITH ID = x Is using the inbuilt indexed, unique record of what I'm retrieving - gotta be faster to pull my 10 records that way - than make the server trawl through your 'MATCHES' and ORDER BY commands each time <<<----- Lights the Blue touch-paper & retires to a safe distance ------>>> Phill. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-917749 Share on other sites More sharing options...
phillw Posted September 14, 2009 Author Share Posted September 14, 2009 thanks ... i ermmmm think I understand.. From the HTML page with the form in, I would set the action to display.php and the top of display.php include_once(search.php) Would this cause display.php to call search.php the 1st time it is called using the action button from the html code, but when display.php calls itself when the customer presses <previous>, <next> it would not then re-call search.php ? To explain hopefully a bit more clearly... An HTML page has a form in it, where customer can type in the search question - this variable is $test. Upon hitting the submit button, it needs to call search.php which returns $result ONCE It then goes onto display.php which slices the result from search.php into page size chunks and displays them. display.php calls itself as they go forwards & backwards through the results, taking care of offsetting within the $result array returned by search.php. We seem to have digressed into the finer points of searches - we are all entitled to our opions. So, I ask again .. would the include_once (search.php) be called just the once regardless of the times display.php called itself ? Thanks, Phill. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-918011 Share on other sites More sharing options...
trq Posted September 14, 2009 Share Posted September 14, 2009 So, I ask again .. would the include_once (search.php) be called just the once regardless of the times display.php called itself ? No. It will be called each time display.php is called. Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-918042 Share on other sites More sharing options...
phillw Posted September 23, 2009 Author Share Posted September 23, 2009 Thanks - I understand now. What I've done is to put include: "display.php" at the END of search, that seems to work well - Thanks for explaining it to me Quote Link to comment https://forums.phpfreaks.com/topic/174067-solved-call-php-from-php/#findComment-923365 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.