Thursday 9 May 2013
Facebook StumbleUpon Twitter Google+ Pin It

Database Searching Techniques with SQL

Search box most important element on web pages specially contented management sites. In this post I want to explain very basic searching techniques and Unicode data searching using SQL LIKE statement. I hope you like this. Thanks

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

Here searching word jquery ajax spaces replaced with %(symbol)- jquery%ajax
SQL Code
SELECT * FROM messages WHERE msg LIKE '%jquery%ajax%' ;

Results
Search results

Sample Database
Contains two columns id and title.
CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255),
PRIMARY KEY (`id`)
)

Search.php
PHP and HTML code. Seaching data from the articles table.
<?php
include("config.php");
if($_SERVER["REQUEST_METHOD"] == "POST")
{
$q=$_POST['q'];
$q=mysql_escape_string($q);
$q_fix=str_replace(" ","%",$q); // Space replacing with %
$sql=mysql_query("SELECT title FROM articles WHERE title LIKE N'%$q_fix%'");
}
?>
<html>
<body>
<form method="post" action="">
<input type="text" name="q" /> <input type="submit" value=" Search " />
</form>
<?php
while($row=mysql_fetch_array($sql))
{
$title=$row['title'];
echo '<div>'.$title.'</div>';
}
?>
</body>
</html>

How to search unicode or UTF-8 data.
If data stored in Unicode format. Take a look at following image.
Unicode Date

SQL ' LIKE N '
SELECT * FROM articles WHERE title LIKE N'%చిరంజీవి%' ;

Why prefix 'N'?
Unicode - The N stands for National language character set. Which means that you are passing an NCHAR, NVARCHAR or NTEXT value Read more.

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

config.php
PHP database configuration file
<?php
$mysql_hostname = "Host name";
$mysql_user = "UserName";
$mysql_password = "Password";
$mysql_database = "Database Name";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $bd) or die("Could not select database");
?>
-By Parthiv Patel
Parthiv Patel
Bhaishri Info Solution
Sr. PHP Developer
Limdi Chowk, AT PO. Nar, Di. Anand
Nar, Gujarat
388150
India
pparthiv2412@gmail.com
7383343029
DOB: 12/24/1986

No comments: