Многоуровневое меню на PHP и MySQL. Живой поиск на Битрикс

Updated on April 30, 2016

I"m going to show you how to create simple search using PHP and MySQL. You"ll learn:

• How to use GET and POST methods
• Connect to database
• Communicate with database
• Find matching database entries with given word or phrase
• Display results

Preparation

You should have Apache, MySQL and PHP installed and running of course (you can use for different platforms or WAMP for windows, MAMP for mac) or a web server/hosting that supports PHP and MySQL databases.

Let"s create database, table and fill it with some entries we can use for search:

• Go to phpMyAdmin, if you have server on your computer you can access it at http://localhost/phpmyadmin/
• Create database, I called mine tutorial_search
• Create table I used 3 fields, I called mine articles.
• Configuration for 1st field. Name: id, type: INT, check AUTO_INCREMENT, index: primary

INT means it"s integer
AUTO_INCREMENT means that new entries will have other(higher) number than previous
Index: primary means that it"s unique key used to identify row

• 2nd field: Name: title, type: VARCHAR, length: 225

VARCHAR means it string of text, maximum 225 characters(it is required to specify maximum length), use it for titles, names, addresses
length means it can"t be longer than 225 characters(you can set it to lower number if you want)

• 3rd field: Name: text, type: TEXT

TEXT means it"s long string, it"s not necessary to specify length, use it for long text.

• Fill the table with some random articles(you can find them on news websites, for example: CNN, BBC, etc.). Click insert on the top menu and copy text to a specific fields. Leave "id" field empty. Insert at least three.

It should look something like this:

• Create a folder in your server directory and two files: index.php and search.php (actually we can do all this just with one file, but let"s use two, it will be easier)
• Fill them with default html markup, doctype, head, etc.

• Create a form with search field and submit button in index.php, you can use GET or POST method, set action to search.php. I used "query" as name for text field

GET - means your information will be stored in url (http://localhost/tutorial_search/search.php?query=yourQuery )
POST - means your information won"t be displayed it is used for passwords, private information, much more secure than GET

Ok, let"s get started with php.

• Open search.php
• Start php ()
• Connect to a database(read comments in following code)

< to > $query = mysql_real_escape_string($query); // makes sure nobody uses SQL injection $raw_results = mysql_query("SELECT * FROM articles WHERE (`title` LIKE "%".$query."%") OR (`text` LIKE "%".$query."%")") or die(mysql_error()); // * means that it selects all fields, you can also write: `id`, `title`, `text` // articles is the name of our table // "%$query%" is what we"re looking for, % means anything, for example if $query is Hello // it will match "hello", "Hello man", "gogohello", if you want exact match use `title`="$query" // or if you want to match just full word so "gogohello" is out use "% $query %" ...OR ... "$query %" ... OR ... "% $query" if(mysql_num_rows($raw_results) >

".$results["title"]."

Done!

Now it works. Try different words, variations, editing code, experiment. I"m adding full code of both files in case you think you"ve missed something. Feel free to ask questions or ask for tutorials.

index.php

search.php

= $min_length){ // if query length is more or equal minimum length then $query = htmlspecialchars($query); // changes characters used in html to their equivalents, for example: < to > $query = mysql_real_escape_string($query); // makes sure nobody uses SQL injection $raw_results = mysql_query("SELECT * FROM articles WHERE (`title` LIKE "%".$query."%") OR (`text` LIKE "%".$query."%")") or die(mysql_error()); // * means that it selects all fields, you can also write: `id`, `title`, `text` // articles is the name of our table // "%$query%" is what we"re looking for, % means anything, for example if $query is Hello // it will match "hello", "Hello man", "gogohello", if you want exact match use `title`="$query" // or if you want to match just full word so "gogohello" is out use "% $query %" ...OR ... "$query %" ... OR ... "% $query" if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following while($results = mysql_fetch_array($raw_results)){ // $results = mysql_fetch_array($raw_results) puts data from database into array, while it"s valid it does the loop echo "

".$results["title"]."

SSY stands for "Siddha Samadhi Yoga" (often expanded as ‘Science of Silence Yoga’) where Siddha means ‘something (i.e., knowledge) that is proven or accomplished’, Samadhi means ‘a state in which the intellect is equanimous’ and Yoga means ‘union with one’s higher self’.

SSY is the fundamental knowledge of life. Our ancient rishis had formulated a unique mode of training called Brahmopadesam, which is an instruction on the science of non-doing and experiencing stillness within and abundance without. It brings about a sea change in one’s outlook towards life and effects remarkable maturity in the individual. SSY is nothing but the present-day version of this ancient science of Brahmopadesam.

SSY as a training programme is offered by Life Yessence Academy (LiYA) , and it is the flagship programme of the institution. Being the flagship programme, it has become synonymous with the name of the organisation. Trainings of this great knowledge are taught in many places in the world by teachers of LiYA. The Indian chapter of LiYA is called Rishi Samskruti Vidya Kendra (RSVK ).

“I am the Body” is the first notion that kills the awakening. SSY is the process
To enter into Samadhi, the state of total detachment. " -Guruji

От автора: приветствую вас друзья. В этой статье мы с вами продолжим реализацию живого поиска для сайта. Что такое живой поиск? Вы с ним сталкиваетесь постоянно, когда что-то ищете в Гугле или Яндексе. Как только вы начинаете набирать поисковый запрос, поисковик тут же начинает предлагать вам варианты, из которых вам остается лишь выбрать наиболее подходящий. Удобная штука, не так ли? Попробуем и мы реализовать нечто подобное.

Исходные файлы текущей статьи вы можете скачать по . Первую часть статьи вы можете найти по .

Итак, в первой части мы подготовили базу данных, которая будет использована для живого поиска, а также прикрутили виджет Autocomplete библиотеки jQuery UI к полю поиска на нашей странице. Пока что виджет работает с тестовыми данными, но сейчас мы с вами это исправим.

Прежде всего укажем для виджета другой источник данных, это будет, скажем, файл search.php, который нам необходимо также создать.

$(function(){ $("#search").autocomplete({ source: "search.php", }); });

Теперь наберу любой символ в поле поиска и посмотрю, что происходит при этом в консоли браузера.

Как видим, отправляется GET запрос с параметром term, значением которого является строка, набранная в поле поиска. При этом все происходит асинхронно, без перезагрузки страницы, т.е. используется AJAX.

Отлично, теперь остается лишь принять поступивший поисковый запрос и выдать ответ на него. Для этого потребуется организовать подключение к серверу БД и написать несложный код, получающий данные из БД по запросу. Код файл search.php будет примерно таким:

$db = mysqli_connect("localhost", "root", "", "world") or die("Нет соединения с БД"); mysqli_set_charset($db, "utf8") or die("Не установлена кодировка соединения"); /** * поиск автокомплит **/ function search_autocomplete(){ global $db; $search = trim(mysqli_real_escape_string($db, $_GET["term"])); $query = "SELECT Name FROM city WHERE Name LIKE "%{$search}%" LIMIT 10"; $res = mysqli_query($db, $query); $result_search = array(); while($row = mysqli_fetch_assoc($res)){ $result_search = array("label" => $row["Name"]); } return $result_search; } if(!empty($_GET["term"])){ $search = search_autocomplete(); exit(json_encode($search)); }

Обратите внимание, функция search_autocomplete, которая и получает данные по запросу, должна возвращать эти данные в определенном формате, это должен быть массив с ключами label и значениями найденных городов. После вызова функции данные необходимо преобразовать в формат JSON.

Остается проверить работу нашего живого поиска. Для этого, как и в прошлый раз, наберем всего одну букву — a:

Отлично! В ответ мы получили десяток городов, в названии которых встречается введенная буква. Если мы продолжим набирать название, список вариантов будет изменяться, т.е. с каждой буквой будет отправляться новый запрос AJAX.

By Ibrahim Diallo

Search is an important feature on a website. When my few readers want to look for a particular passage on my blog, they use the search box. It used to be powered by Google Search, but I have since then changed it to my own home-brewed version not because I can do better but because it was an interesting challenge.

If you are in a hurry and just want your site to be searchable, well do what I did before, use Google.

What it does is pretty simple. Get the term passed by the user, and forward it to Google search page. Limit the search result to our current domain using the site: keyword in the search query. All your pages that are indexed by Google will be available through search now. If you do want to handle your search in house however, then keep reading.

Homemade Search Solution

Before we go any further, try using the search box on this blog. It uses the same process that I will describe below. If you feel that this is what you want then please continue reading.

This solution is catered to small websites. I make use of LIKE with wild cards on both ends, meaning your search cannot be indexed. This means the solution will work fine for your blog or personal website that doesn"t contain tons of data. Port it to a bigger website and it might become very slow. MySQL offers Full Text Search which is not what we are doing here.

Note: If you have 5000 blog posts you are still fine. .

We will take the structure of this blog as a reference. Each blog post has:

• A title p_title
• A url p_url
• A summary p_summary
• A post content p_content
• And catergories category.tagname

For every field that matches with our search term, we will give it a score. The score will be based on the importance of the match:

// the exact term matches is found in the title $scoreFullTitle = 6; // match the title in part $scoreTitleKeyword = 5; // the exact term matches is found in the summary $scoreFullSummary = 5; // match the summary in part $scoreSummaryKeyword = 4; // the exact term matches is found in the content $scoreFullDocument = 4; // match the document in part $scoreDocumentKeyword = 3; // matches a category $scoreCategoryKeyword = 2; // matches the url $scoreUrlKeyword = 1;

Before we get started, there are a few words that do not contribute much to a search that should be removed. Example "in","it","a","the","of" ... . We will filter those out and feel free to add any word you think is irrelevant. Another thing is, we want to limit the length of our query. We don"t want a user to write a novel in the search field and crash our MySQL server.

// Remove unnecessary words from the search term and return them as an array function filterSearchKeys($query){ $query = trim(preg_replace("/(\s+)+/", " ", $query)); $words = array(); // expand this list with your words. $list = array("in","it","a","the","of","or","I","you","he","me","us","they","she","to","but","that","this","those","then"); $c = 0; foreach(explode(" ", $query) as $key){ if (in_array($key, $list)){ continue; } $words = $key; if ($c >= 15){ break; } $c++; } return $words; } // limit words number of characters function limitChars($query, $limit = 200){ return substr($query, 0,$limit); }

Our helper functions can now limit character count and filter useless words. The way we will implement our algorithm is by giving a score every time we find a match. We will match words using the if statement and accumulate points as we match more words. At the end we can use that score to sort our results

Note: I will not be showing how to connect to MySQL database. If you are having problems to efficiently connect to the database I recommend reading this .

Let"s give our function a structure first. Note I left placeholders so we can implement sections separately.

Function search($query){ $query = trim($query); if (mb_strlen($query)===0){ // no need for empty search right? return false; } $query = limitChars($query); // Weighing scores $scoreFullTitle = 6; $scoreTitleKeyword = 5; $scoreFullSummary = 5; $scoreSummaryKeyword = 4; $scoreFullDocument = 4; $scoreDocumentKeyword = 3; $scoreCategoryKeyword = 2; $scoreUrlKeyword = 1; $keywords = filterSearchKeys($query); $escQuery = DB::escape($query); // see note above to get db object $titleSQL = array(); $sumSQL = array(); $docSQL = array(); $categorySQL = array(); $urlSQL = array(); /** Matching full occurrences PLACE HOLDER **/ /** Matching Keywords PLACE HOLDER **/ $sql = "SELECT p.p_id,p.p_title,p.p_date_published,p.p_url, p.p_summary,p.p_content,p.thumbnail, ((-- Title score ".implode(" + ", $titleSQL).")+ (-- Summary ".implode(" + ", $sumSQL).")+ (-- document ".implode(" + ", $docSQL).")+ (-- tag/category ".implode(" + ", $categorySQL).")+ (-- url ".implode(" + ", $urlSQL).")) as relevance FROM post p WHERE p.status = "published" HAVING relevance >

In the query, all scores will be summed up as the relevance variable and we can use it to sort the results.

Matching full occurrences

We make sure we have some keywords first then add our query.

If (count($keywords) > 1){ $titleSQL = "if (p_title LIKE "%".$escQuery."%",{$scoreFullTitle},0)"; $sumSQL = "if (p_summary LIKE "%".$escQuery."%",{$scoreFullSummary},0)"; $docSQL = "if (p_content LIKE "%".$escQuery."%",{$scoreFullDocument},0)"; }

Those are the matches with higher score. If the search term matches an article that contains these, they will have higher chances of appearing on top.

Matching keywords occurrences

We loop through all keywords and check if they match any of the fields. For the category match, I used a sub-query since a post can have multiple categories.

Foreach($keywords as $key){ $titleSQL = "if (p_title LIKE "%".DB::escape($key)."%",{$scoreTitleKeyword},0)"; $sumSQL = "if (p_summary LIKE "%".DB::escape($key)."%",{$scoreSummaryKeyword},0)"; $docSQL = "if (p_content LIKE "%".DB::escape($key)."%",{$scoreDocumentKeyword},0)"; $urlSQL = "if (p_url LIKE "%".DB::escape($key)."%",{$scoreUrlKeyword},0)"; $categorySQL = "if ((SELECT count(category.tag_id) FROM category JOIN post_category ON post_category.tag_id = category.tag_id WHERE post_category.post_id = p.post_id AND category.name = "".DB::escape($key)."") > 0,{$scoreCategoryKeyword},0)"; }

Also as pointed by a commenter below, we have to make sure that the these variables are not empty arrays or the query will fail.

// Just incase it"s empty, add 0 if (empty($titleSQL)){ $titleSQL = 0; } if (empty($sumSQL)){ $sumSQL = 0; } if (empty($docSQL)){ $docSQL = 0; } if (empty($urlSQL)){ $urlSQL = 0; } if (empty($tagSQL)){ $tagSQL = 0; }

At the end the queries are all concatenated and added together to determine the relevance of the post to the search term.

// Remove unnecessary words from the search term and return them as an array function filterSearchKeys($query){ $query = trim(preg_replace("/(\s+)+/", " ", $query)); $words = array(); // expand this list with your words. $list = array("in","it","a","the","of","or","I","you","he","me","us","they","she","to","but","that","this","those","then"); $c = 0; foreach(explode(" ", $query) as $key){ if (in_array($key, $list)){ continue; } $words = $key; if ($c >= 15){ break; } $c++; } return $words; } // limit words number of characters function limitChars($query, $limit = 200){ return substr($query, 0,$limit); } function search($query){ $query = trim($query); if (mb_strlen($query)===0){ // no need for empty search right? return false; } $query = limitChars($query); // Weighing scores $scoreFullTitle = 6; $scoreTitleKeyword = 5; $scoreFullSummary = 5; $scoreSummaryKeyword = 4; $scoreFullDocument = 4; $scoreDocumentKeyword = 3; $scoreCategoryKeyword = 2; $scoreUrlKeyword = 1; $keywords = filterSearchKeys($query); $escQuery = DB::escape($query); // see note above to get db object $titleSQL = array(); $sumSQL = array(); $docSQL = array(); $categorySQL = array(); $urlSQL = array(); /** Matching full occurences **/ if (count($keywords) > 1){ $titleSQL = "if (p_title LIKE "%".$escQuery."%",{$scoreFullTitle},0)"; $sumSQL = "if (p_summary LIKE "%".$escQuery."%",{$scoreFullSummary},0)"; $docSQL = "if (p_content LIKE "%".$escQuery."%",{$scoreFullDocument},0)"; } /** Matching Keywords **/ foreach($keywords as $key){ $titleSQL = "if (p_title LIKE "%".DB::escape($key)."%",{$scoreTitleKeyword},0)"; $sumSQL = "if (p_summary LIKE "%".DB::escape($key)."%",{$scoreSummaryKeyword},0)"; $docSQL = "if (p_content LIKE "%".DB::escape($key)."%",{$scoreDocumentKeyword},0)"; $urlSQL = "if (p_url LIKE "%".DB::escape($key)."%",{$scoreUrlKeyword},0)"; $categorySQL = "if ((SELECT count(category.tag_id) FROM category JOIN post_category ON post_category.tag_id = category.tag_id WHERE post_category.post_id = p.post_id AND category.name = "".DB::escape($key)."") > 0,{$scoreCategoryKeyword},0)"; } // Just incase it"s empty, add 0 if (empty($titleSQL)){ $titleSQL = 0; } if (empty($sumSQL)){ $sumSQL = 0; } if (empty($docSQL)){ $docSQL = 0; } if (empty($urlSQL)){ $urlSQL = 0; } if (empty($tagSQL)){ $tagSQL = 0; } $sql = "SELECT p.p_id,p.p_title,p.p_date_published,p.p_url, p.p_summary,p.p_content,p.thumbnail, ((-- Title score ".implode(" + ", $titleSQL).")+ (-- Summary ".implode(" + ", $sumSQL).")+ (-- document ".implode(" + ", $docSQL).")+ (-- tag/category ".implode(" + ", $categorySQL).")+ (-- url ".implode(" + ", $urlSQL).")) as relevance FROM post p WHERE p.status = "published" HAVING relevance > 0 ORDER BY relevance DESC,p.page_views DESC LIMIT 25"; $results = DB::query($sql); if (!$results){ return false; } return $results; }

Now your search.php file can look like this:

$term = isset($_GET["query"])?$_GET["query"]: ""; $search_results = search($term); if (!$search_results) { echo "No results"; exit; } // Print page with results here.

We created a simple search algorithm that can handle a fair amount of content. I arbitrarily chose the score for each match, feel free to tweak it to something that works best for you. And there is always room for improvement.

It is a good idea to track the search term coming from your users, this way you can see if most users search for the same thing. If there is a pattern, then you can save them a trip and just cache the results using Memcached .

If you want to see this search algorithm in action, go ahead and try looking for an article on the search box on top of the page. I have added extra features like returning the part where the match was found in the text. Feel free to add features to yours.

Did you like this article? You can subscribe to read more awesome ones. .

On a related note, here are some interesting articles.

If there is one thing a web server does everyday it iss connecting to the database. I have been using PHP for many years now, but if you ask me to write a script to fetch data from the database I couldn"t do it without going back to the Ultimate PHP manual to find a few examples first.

A few months ago I updated my PHP version. PHP 5.4 to 5.5 . I never had any problems updating PHP before. I follow its development closely and try to remove my deprecated functions long before they are officially removed. But this time I was caught off guard. It silently broke part of my website for the silliest reason.

Comments(45)

Zaryel Aug 12 2015:

Ian Mustafa Sep 26 2015:

Rob Sep 29 2015:

adeem Feb 11 2016:

Ivan Venediktov Apr 9 2016.