jpokusa Posted September 8, 2013 Share Posted September 8, 2013 I built a basic working search engine that will pull articles from my MYSQL tables. I want to make the search is stricter for getting my results. I want to add a preg_match line in my php code to make sure that the match has to be found at the start of the string. So in my search engine when the letter a is typed in the search box I dont want that to pull results. I want the search to be no less then three characters and it must match the word. So you have to type in cat exactly to retrieve my cat article. Here is link to my webpage. I thought adding this code $search = preg_match('/^ /', $search); would work but I have feeling I am approaching this wrong, http://www.trueacewebdesign.com/searchsite/main.html link to the files http://www.trueacewebdesign.com/searchsite.zip Here is my current PHP code. <?php/************************************************************************************** * Main Search Page - search.php **************************************************************************************/ //Get variables from config.php to connect to mysql serverrequire 'config.php'; // connect to the mysql database server.mysql_connect ($dbhost, $dbusername, $dbuserpass);//select the databasemysql_select_db($dbname) or die('Cannot select database'); //search variable = data in search box or urlif(isset($_GET['search'])){$search = $_GET['search'];} //trim whitespace from variable$search = trim($search);$search = preg_replace('/\s+/', ' ', $search); //seperate multiple keywords into array space delimited$keywords = explode(" ", $search); //Clean empty arrays so they don't get every row as result$keywords = array_diff($keywords, array("")); //Set the MySQL queryif ($search == NULL or $search == '%'){} else {for ($i=0; $i<count($keywords); $i++) {$query = "SELECT * FROM table_name " ."WHERE column3 LIKE '%".$keywords[$i]."%'"." ORDER BY column1";} //Store the results in a variable or die if query fails$result = mysql_query($query) or die(mysql_error());}if ($search == NULL or $search == '%'){} else {//Count the rows retrived$count = mysql_num_rows($result);} //If search variable is null do nothing, else print it.if ($search == NULL) {} else {echo "You searched for <b><FONT COLOR=\"blue\">";foreach($keywords as $value) { print "$value ";}echo "</font></b>";}echo "<p> </p><br />";echo "</center>"; //If users doesn't enter anything into search box tell them to.if ($search == NULL){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";} elseif ($search == '%'){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";//If no results are returned print it} elseif ($count <= 0){echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";//ELSE print the data in a table} else {//Table headerecho "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";echo "<tr>";echo "<td><b>Results:</b></td>";echo "<tr>";echo "</table></center>"; //While there are rows, print it.while($row = mysql_fetch_array($result)){ //table background color = row_color variableecho "<table bgcolor=".$row_color.">";echo "<tr>";echo "<td>".$row['column1']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>".$row['column2']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "</table>"; $row_count++;//end while}//end if} echo "</body>";echo "</html>";if ($search == NULL or $search == '%') {} else {//clear memorymysql_free_result($result);}?> Link to comment Share on other sites More sharing options...
HartleySan Posted September 8, 2013 Share Posted September 8, 2013 I'm a little confused but what exactly you want to do, but if you simply want to verify whether a string is at the beginning of an article or not, all you need is the strpos function. If strpos returns 0 (not false), then you know that the substring starts at the very beginning of the larger string. That help? Link to comment Share on other sites More sharing options...
jpokusa Posted September 8, 2013 Author Share Posted September 8, 2013 Hi HartleySan Thanks but imdont think this is what I need. If you go to my search engine please see link in my first post and you type in the search box the two letters ra and hit the search button then unfortunately my article on rabbits appears. I want my rabbit article only to show up in the result only if the user types in the word rabbit. My search currently is to greedy. What I am asking help with is what type of php code to place in my current code. I believe it is just a basic line of preg match or something. Link to comment Share on other sites More sharing options...
HartleySan Posted September 8, 2013 Share Posted September 8, 2013 Well, I typed "ra" into the search box and got the following hit: Rabbits tips on breeding In other words, typing "rabbit" and getting an article that starts with "Rabbits" (plural and with an uppercase R) still seems greedy to me. Are you saying that you want a search feature that requires a user to type in the first word in the article exactly or else they won't get the article back in the search results? Link to comment Share on other sites More sharing options...
jpokusa Posted September 9, 2013 Author Share Posted September 9, 2013 No it doesn't matter if it is the first word in the article. For the rabbit article I only want that article to appear if you type in rabbits or if you type in tips or if you type in breeding. I do not want this article to pop up if you type in ra. Because I am only choosing important keywords in my database for each article. For this example ra is not a keyword I have chosen. I am looking for php code that will query for an exact match of a word typed in the search box. I hope that clears this up. Link to comment Share on other sites More sharing options...
HartleySan Posted September 9, 2013 Share Posted September 9, 2013 Well, if you're only querying keywords in the DB, then just search for the search word in the DB, and grab only the rows that have an exact match with the keyword. That's actually much simpler then any sort of fuzzy matching. Link to comment Share on other sites More sharing options...
jpokusa Posted September 9, 2013 Author Share Posted September 9, 2013 That's exactly what I want to do. I just can't figure out how to write the code and put in my search.php Link to comment Share on other sites More sharing options...
HartleySan Posted September 9, 2013 Share Posted September 9, 2013 Here's a quick mock-up that's part real code, part pseudo code: <?php $search_term = $_POST['search']; // Scrub search term for malicious values. // Also, what are you going to do when someone types more than one word? if ($search_term === OK) { // For the DB structure, I'm assuming that you have a table of articles, and // another table containing keywords that link to the IDs in the articles table. $q = "SELECT a.* FROM articles AS a, keywords AS k WHERE a.id = k.article_id AND k.keyword = '$search_term';" // Perform the query here and display the results accordingly. } else { // Something is rotten with the user input. // Kick them back to the search input with a friendly message. } Link to comment Share on other sites More sharing options...
jpokusa Posted September 9, 2013 Author Share Posted September 9, 2013 Thank you for your help so far. Typing more than one word is fine. Here is my table structure above. I am still a newbie at php and I understand that this may not be the most secure and proper way of doing this basic search engine. I have one table called (table_name) I have three fields in my table. The field called hotwords is queried to display my article and content field. Really basic. Here is my query //Set the MySQL queryif ($search == NULL or $search == '%'){} else {for ($i=0; $i<count($keywords); $i++) {$query = "SELECT * FROM table_name " ."WHERE hotwords LIKE '%".$keywords[$i]."%'"." ORDER BY article";} I believe I need to replace LIKE with AND. When I tried it I couldn't search anyword. Or I need to do something such as = keywords. I am so close and I know it is simple. below is my full code. <?php/************************************************************************************** * Main Search Page - search.php * Author: Your Name <email@something.com> * This file searches the database **************************************************************************************/ //Get variables from config.php to connect to mysql serverrequire 'config.php'; // connect to the mysql database server.mysql_connect ($dbhost, $dbusername, $dbuserpass);//select the databasemysql_select_db($dbname) or die('Cannot select database'); //search variable = data in search box or urlif(isset($_GET['search'])){$search = $_GET['search'];} //trim whitespace from variable$search = trim($search);$search = preg_replace('/\s+/', ' ', $search); //seperate multiple keywords into array space delimited$keywords = explode(" ", $search); //Clean empty arrays so they don't get every row as result$keywords = array_diff($keywords, array("")); //Set the MySQL queryif ($search == NULL or $search == '%'){} else {for ($i=0; $i<count($keywords); $i++) {$query = "SELECT * FROM table_name " ."WHERE hotwords LIKE '%".$keywords[$i]."%'"." ORDER BY article";} //Store the results in a variable or die if query fails$result = mysql_query($query) or die(mysql_error());}if ($search == NULL or $search == '%'){} else {//Count the rows retrived$count = mysql_num_rows($result);} //If search variable is null do nothing, else print it.if ($search == NULL) {} else {echo "You searched for <b><FONT COLOR=\"blue\">";foreach($keywords as $value) { print "$value ";}echo "</font></b>";}echo "<p> </p><br />";echo "</center>"; //If users doesn't enter anything into search box tell them to.if ($search == NULL){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";} elseif ($search == '%'){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";//If no results are returned print it} elseif ($count <= 0){echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";//ELSE print the data in a table} else {//Table headerecho "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";echo "<tr>";echo "<td><b>Results:</b></td>";echo "<tr>";echo "</table></center>"; //While there are rows, print it.while($row = mysql_fetch_array($result)){ //table background color = row_color variableecho "<table bgcolor=".$row_color.">";echo "<tr>";echo "<td>".$row['article']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>".$row['content']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "</table>"; $row_count++;//end while}//end if} echo "</body>";echo "</html>";if ($search == NULL or $search == '%') {} else {//clear memorymysql_free_result($result);}?> Link to comment Share on other sites More sharing options...
HartleySan Posted September 9, 2013 Share Posted September 9, 2013 Honestly, I think there are several issues with your implementation, but the biggest thing I would recommend is breaking up your one table into two. The main reason why is because you currently have multiple keywords stored together as one string with spaces between them in your hotwords field. If you do things that way, then you will have to do one of three things to check for keyword matches: 1) Run a regex on the hotwords field via your MySQL query. (Not fun.) 2) Run a regex on the returned hotwords field string in PHP. 3) Split the returned string of keywords on spaces in PHP and then loop through them, searching for a match. Unfortunately, all three of those methods are both more difficult to implement and more computationally inefficient then just breaking your one table into two (i.e., creating a more ideal DB structure), and performing a join in the DB. As such, for the one example row in your DB that you provided, I would redefine the DB schema as follows: Table name: articles id url content 1 http://rabbitbreeders.us/rabbit-breeds Believe it or not? … Table name: keywords id article_id keyword 1 1 rabbits 2 1 eat 3 1 carrots 4 1 rabbit 5 1 tips 6 1 breed 7 1 breeding Then, you can run the following query (as I recommended before): SELECT a.* FROM articles AS a, keywords AS k WHERE a.id = k.article_id AND k.keyword = '$search_term'; Does that help? 1 Link to comment Share on other sites More sharing options...
jpokusa Posted September 11, 2013 Author Share Posted September 11, 2013 Hi HartleySan, Here is the page http://www.trueacewebdesign.com/animal-search/main.html I created the two tables as you mentioned. I am not able to get any result for the query when I type in rabbits. I put you code in my page. Do I need to change anything besides just putting in your query code? <?php/************************************************************************************** * Main Search Page - search.php * Author: Your Name <email@something.com> * This file searches the database **************************************************************************************/ //Get variables from config.php to connect to mysql serverrequire 'config.php'; // connect to the mysql database server.mysql_connect ($dbhost, $dbusername, $dbuserpass);//select the databasemysql_select_db($dbname) or die('Cannot select database'); //search variable = data in search box or urlif(isset($_GET['search'])){$search = $_GET['search'];} //trim whitespace from variable$search = trim($search);$search = preg_replace('/\s+/', ' ', $search); //seperate multiple keywords into array space delimited$keywords = explode(" ", $search); //Clean empty arrays so they don't get every row as result$keywords = array_diff($keywords, array("")); //Set the MySQL queryif ($search == NULL or $search == '%'){} else {for ($i=0; $i<count($keywords); $i++) {$query = "SELECT a.* FROM articles AS a, keywords AS k WHERE a.id = k.article_id AND k.keyword = '$search_term';";"ORDER BY content";} //Store the results in a variable or die if query fails$result = mysql_query($query) or die(mysql_error());}if ($search == NULL or $search == '%'){} else {//Count the rows retrived$count = mysql_num_rows($result);} //If search variable is null do nothing, else print it.if ($search == NULL) {} else {echo "You searched for <b><FONT COLOR=\"blue\">";foreach($keywords as $value) { print "$value ";}echo "</font></b>";}echo "<p> </p><br />";echo "</center>"; //If users doesn't enter anything into search box tell them to.if ($search == NULL){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";} elseif ($search == '%'){echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";//If no results are returned print it} elseif ($count <= 0){echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";//ELSE print the data in a table} else {//Table headerecho "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";echo "<tr>";echo "<td><b>Results:</b></td>";echo "<tr>";echo "</table></center>"; //While there are rows, print it.while($row = mysql_fetch_array($result)){ //table background color = row_color variableecho "<table bgcolor=".$row_color.">";echo "<tr>";echo "<td>".$row['articles']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>".$row['content']."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "<tr>";echo "<td>"."</td>";echo "</tr>";echo "</table>"; $row_count++;//end while}//end if} echo "</body>";echo "</html>";if ($search == NULL or $search == '%') {} else {//clear memorymysql_free_result($result);}?> Link to comment Share on other sites More sharing options...
HartleySan Posted September 11, 2013 Share Posted September 11, 2013 You have to set $search_term to a string before you use it in the query. Link to comment Share on other sites More sharing options...
jpokusa Posted September 12, 2013 Author Share Posted September 12, 2013 So in my current code I need to replace $keyword with $search_term. Is it an easy fix to my current code? Thank you for your help Link to comment Share on other sites More sharing options...
HartleySan Posted September 12, 2013 Share Posted September 12, 2013 I suggest you try it before you come back here asking. The basic idea is that you need to take the search term entered by the user and use that to determine which DB records to pull. I think you can get it if you try a bit more. Good luck. Link to comment Share on other sites More sharing options...
Recommended Posts