Database Searching Techniques with MySQL

Database Searching Techniques with MySQL

In this article, we will learn about basic Database Searching Techniques with MySQL. Search box most important element on web pages specially content management sites. When it comes to implementation search box, you want that people should get correct results as per their searching terms or keywords. In this post I will explain very basic Database Searching Techniques with MySQL and Unicode data searching using MySQL LIKE statement. So lets start searching in MySQL.

Step 1: Replace spaces
Before executing SQL query searching word replace spaces with %(symbol) using PHP str_replace function.

Step 2: Use LIKE Operator with MySQL Wildcard Characters
Here searching word php mysql spaces replaced with %(symbol)- php%mysql

Sample Database
For Database Searching Techniques with MySQL, We have created a database table called posts.
This table Contains two columns id and title.

CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255),
PRIMARY KEY (`id`)
)

MySQL Query Code

SELECT * FROM messages WHERE msg LIKE '%php%mysql%' ;

config.php

PHP database configuration file. This file used to connect to database by passing required details like hostname, username, password and database name.


After successful connection to database. You can write your search query and follow the steps to replace spaces with %(symbole) and use LIKE Operator.

Search.php

Now we will write php code to connect to database and query to table for search term. This file contains PHP and HTML code.Basically we posting our search term in form using post method and process the search term with mysql_query to get the matching result.

<--?php 
include("config.php"); 
if($_SERVER["REQUEST_METHOD"] == "POST") { 
$search=$_POST['search']; 
$search=mysql_escape_string($search); 
$search_fix=str_replace(" ","%",$search); // Space replacing with % 
$sql=mysql_query("SELECT title FROM articles WHERE title LIKE N'%$search_fix%'"); 
} 
?>

 

How to search unicode or UTF-8 data.

The Unicode Standard includes characters from the Basic Multilingual Plane. If data stored in Unicode format. Then you can search data using ‘LIKE N’, where N stands for National language character set. Which means that you are passing an NCHAR, NVARCHAR or NTEXT value.

MySQL ‘ LIKE N ‘

SELECT * FROM articles WHERE title LIKE N'%w3తరగతులు%' ;

Database Table Structure for Unicode type

CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) character set utf8 collate utf8_general_ci default NULL,
PRIMARY KEY (`id`)
)

Here we are creating table with posts name with fields id and title and set utf8 as utf8_general_ci. Now from below script written in config.php file, we will connect to database by providing correct data connection credentials.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.