henricar Posted April 2, 2011 Share Posted April 2, 2011 Hey guys, I'm looking for some sort of tutorial on how to code a search script which would allow users to select number of results per page (and take care of the page 1, page 2, etc. links accordingly) as well as order them by date, most views, etc. I thought I'd be good to do this on my own but my script is looking very dirty and I'm pretty sure it isn't very secure.. anyone ever consult a tutorial with main guidance as how to code the kind of script I'm interested in?? thanks Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted April 2, 2011 Share Posted April 2, 2011 I would be able to help with this, not an easy task by any means. You need multiple items to be done to accomplish your goal, like an entire script, indexes in the database, custom queries and forms. Plus pagination for it all. I ended up doing what you are looking for, took me a long time and lots of trial and error. You can browse my profile posts for some suggestions and ideas, or can try my dynaindex.com out while being logged in and see how the search/navigation actually works. You can post your current code here just to see what you are currently doing. Quote Link to comment Share on other sites More sharing options...
henricar Posted April 2, 2011 Author Share Posted April 2, 2011 hi QuickOldCar, thanks for answering. Currently, I have a table named "posts" with [id, title, description, category, date posted and views] as rows. What the sresults.php script does is it takes the text input from the form on the previous page and brings back any post that is LIKE %$keyword% in the title or in the description. I then display the results using a while loop. What I currently have for "sorting" is this: $keywords = $_POST['keywords']; $orderby = $_GET['oby']; $cat = $_GET['cat']; $keywords = strtoupper($keywords); $keywords = trim($keywords); if ($keywords == '' AND $orderby == '' AND $cat == '') { echo 'please enter a keyword'; } else { if (!($orderby == '') and !($cat == '')) { if ($orderby == 'ddesc') { $fetchsresults = mysql_query("SELECT * FROM posts WHERE (upper(title) LIKE '%$keywords%' OR upper(description) LIKE '%$keywords%') AND category='$cat' ORDER BY id DESC LIMIT 10"); } if ($orderby == 'dasc') { $fetchsresults = mysql_query("SELECT * FROM posts WHERE (upper(title) LIKE '%$keywords%' OR upper(description) LIKE '%$keywords%') AND category='$cat' ORDER BY id ASC LIMIT 10"); } } elseif (!($orderby == '') and ($cat == '')) { if ($orderby == 'ddesc') { $fetchsresults = mysql_query("SELECT * FROM posts WHERE (upper(title) LIKE '%$keywords%' OR upper(description) LIKE '%$keywords%') ORDER BY id DESC LIMIT 10"); } } elseif ($orderby == '' and !($cat == '')) { $fetchsresults = mysql_query("SELECT * FROM posts WHERE (upper(title) LIKE '%$keywords%' OR upper(description) LIKE '%$keywords%') AND category='$cat' LIMIT 6"); } else { $fetchsresults = mysql_query("SELECT * FROM posts WHERE (upper(title) LIKE '%$keywords%' OR upper(description) LIKE '%$keywords%') LIMIT 10"); } $colors = Array('itemsmainpageA','itemsmainpageB'); $col = 0; while($sresultsarray = mysql_fetch_array($fetchsresults)){ .... and the links to "sort by" go like this: $currenturl = (!empty($_SERVER['HTTPS'])) ? "https://".$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI'] : "http://".$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']; <a href="'. $currenturl .'&oby=ddesc">Most Recent</a> so basically if no keyword is entered and no category or orderby methed is provided, the script returns "please enter keywords" in any other case, the script detects if a sorting method and/or category is selected and provides the corresponding posts thanks for any help Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted April 2, 2011 Share Posted April 2, 2011 Well here's a breakdown of how I approached this. I have a search/navigation form using get. I do multiple gets in the same form with a combination of text fields and dropdowns with all my values, that also includes a page number text input. On my display page i first discover the url and if there are any queries, if no queries i display a default basic query going by last id's and show 10...for speed purposes. For display page if no page number is set it defaults to page=1 With a few checks for isset or empty I set default values for the other GET requests For the pagination I do similar to this, I actually use similar to the second for my dynaindex.com http://dynainternet.com/paginate/ This one can do any amount of posts per page http://dynaindex.com/paginate.php This one I hard set it to 10 posts per page but works differently Now that I have GET values for everything I do checks with a multi if else statement that contains different mysql queries, each of those queries are variables for what type of search it is and what to look in mysql for. I also use full text indexing and boolean mode, a few likes and even use match against. As per http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which * + stands for AND * - stands for NOT * [no operator] implies OR With that said here would be an example of the multi query I actually use a multi post status for logged in or admin, we'll just set this to publish $post_status = "WHERE post_status='publish'"; $search I use as type of search doing $search_words is the search terms or words $display = "post_date"; as default, but I have get values of name,id and so on $order is for ASC or DESC look in my pagination scripts $startrow is the mysql starting row $posts_per_page is amount of posts per page //search get variables from search-navigation if ($search == "Date") { // $result = mysql_query("SELECT * FROM posts $post_status AND post_date LIKE //'".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); //$total_count = mysql_query("SELECT * FROM posts $post_status AND post_date LIKE //'".$search_words."%'"); $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_date) AGAINST ('\"$search_words\"' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_date) AGAINST ('\"$search_words\"' IN BOOLEAN MODE)"); } elseif ($search == "ID") { $result = mysql_query("SELECT * FROM posts $post_status AND ID LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND ID LIKE '".$search_words."%'"); } elseif ($search == "url_begins_characters") { $result = mysql_query("SELECT * FROM posts $post_status AND post_title LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND post_title LIKE '".$search_words."%'"); } elseif ($search == "url_contains_characters") { $result = mysql_query("SELECT * FROM posts $post_status AND post_title LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND post_title LIKE '%"."$search_words"."%'"); } elseif ($search == "feed_single_word") { $result = mysql_query("SELECT * FROM posts $post_status AND link_rss LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND link_rss LIKE '%"."$search_words"."%'"); } elseif ($search == "one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "exact_words") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('\"$search_words\"' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('\"$search_words\"' IN BOOLEAN MODE)"); } elseif ($search == "least_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "exclude_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE)"); } elseif ($search == "title_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "title_exact_words") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('\"$search_words\"' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('\"$search_words\"' IN BOOLEAN MODE)"); } elseif ($search == "title_least_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "title_exclude_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (title_2) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE)"); } elseif ($search == "description_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "description_exact_words") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('\"$search_words\"' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('\"$search_words\"' IN BOOLEAN MODE)"); } elseif ($search == "description_least_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "description_exclude_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_description) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE)"); } elseif ($search == "keyword_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "keyword_exact_words") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('\"$search_words\"' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('\"$search_words\"' IN BOOLEAN MODE)"); } elseif ($search == "keyword_least_one_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('$search_words' IN BOOLEAN MODE)"); } elseif ($search == "keyword_exclude_word") { $result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_keywords) AGAINST ('+$search_words -$search_words' IN BOOLEAN MODE)"); } else { //if anything goes wrong above or nothing selected, this will be used as the default query instead /* all posts //$result = mysql_query("SELECT * FROM posts $post_status ORDER BY $display $order //LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status"); */ //just show last 10 by id main page if ($url == "http://get.blogdns.com/dynaindex/index.php") { $result = mysql_query("SELECT * FROM posts ORDER BY ID DESC LIMIT 0,10"); $total_count = $result; } else { //todays results new and updated $result = mysql_query("SELECT * FROM posts $post_status AND post_date LIKE '".$today_date."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" ); $total_count = mysql_query("SELECT * FROM posts $post_status AND post_date LIKE '".$today_date."%'"); } I believe my method is a bit different than most pagination you will see, most define a page number and do a plus or minus 1 by using hyperlinks, I go by the total count and the current page and insert it into a query. The results of the total count determine how many there are, then through calculations determines how many pages there are. Then clicking or inserting the page number would then insert the correct startrow and posts per page. I created full text indexes in mysql on anything that would use a where or and clause. You must be using MyISAM to use full text indexing, also is a min word length and stop words which you may change. change your my.ini like this if want it 3, can use 1 if really wanted to, add any excluded words into your stop.txt: [mysqld] ft_min_word_len=3 ft_stopword_file="C:\\MySQL\\stop.txt" restart your mysqld at services.msc reindex your table using REPAIR TABLE tbl_name QUICK; If using Innodb is entirely different, I would then recommend trying sphinxsearch.com or lucene.apache.org lemme tell you, I use myisam and full text, but if were to do it again I'd opt for innodb, I really hate how it locks the tables every insert or update. I put up with it for now but one day I think I'm gonna switch over to cassandra.apache.org as the database and use python as the search method. Quote Link to comment 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.