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
Live DemoReplace 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
Sample Database
Contains two columns id and title.
CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255),
PRIMARY KEY (`id`)
)
`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>
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.
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`)
)
`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");
?>
$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");
?>
Postgre SQL - Relational Database Management System Professional - Version 7.2
hi, its nice tutorial i have another one tutorial on this topic (www.99Points.info) which is really very awesome just try it
ReplyDeletePL make sure that Demo link is correct
ReplyDeleteKumar, demo is correct, and i've tested, everything was fine :D
ReplyDeletethanks for admin :)
Hey good post, but you forget to remove b tag from search results while loading.. for e.g. if load results with facebook , than because of b in url it's giving wrong results.
ReplyDeleteSweet. Thanks dude!
ReplyDeleteThank you
ReplyDeleteGood topic
Very nice POST.
ReplyDeleteThanks a lot.
Please use codeigniter for your all jquery tutorial.
ReplyDeletei want to know jquery with codeigniter.
waiting for that..thanksssssssssss
I got something for you all to try!!
ReplyDeleteIn the demo, search for the following, exactly as written:
'; DELETE FROM articles; --
OMG WHAT JUST HAPPENED?! It's called SQL injection. You should not use this code, it's very dangerous.
@hopeseekr
ReplyDeleteThank of tips but here mysql_escape_string() not allow this.
$q="'; DELETE FROM articles; -- ";
$q=mysql_escape_string($q);
$q_fix=str_replace(" ","%",$q);
$q_fix="\';%DELETE%FROM%articles;%--%";
thank you
ReplyDeletehow is if i wanna download the ajaxed version ?
ReplyDeletethank you
ReplyDeleteGood tut.I think it's simple and quite helpful!!Nice work!! :)
ReplyDeleteUsing "like" is one of the most horrible ways to "search", and will kill your performance on anything significant. Most DBs have a specific text searching/full text searching mechanism making better use of specific indexes, etc.
ReplyDeleteMySql : match (col_1, col_2 ) against ( {text} )
Oracle: http://www.oracle.com/technology/products/text/index.html
thank's for this post, is very important have a style for search botton and textbox
ReplyDeleteThis code doesn't work ;). and there's no security, none even "htmlentities" or "mysql_escape_real_string"
ReplyDeletethx to u for sharing us these resources!
ReplyDeletecool article.. thanks a lot :D
ReplyDeletegood
ReplyDeletevery very simple .. thanx
ReplyDeletehow to search and print out in 1 row include the table format.
ReplyDeleteits just only an example no question for sql injection or something else...
ReplyDeletebtw, thanks for this is could really help!
~aleks
i found this blog so interesting. keep it up...jquery mouse hover effects on image. try it here: http://goo.gl/ByWQ
ReplyDeletehaii good topic its very useful for my website thanks a lot
ReplyDeletehey wheres the code? how can i download this..??
ReplyDeleteplease send me in my account. [email protected] tnx tnx =)
can some one give a example in mysql how to add an article?
ReplyDeletehey really nice article.
ReplyDeletebut tell me is there any way to search inside another website database.
just like you are doing in yahoo, bing, google, youtube instant search
i mean i want to make a local search enigne ( just dial ). so is there any possible way to search inside the data of just dial with the help of ajax, php.
please review my request and try to make a local search engine instant search .
thanks in advance
hey really..... is so good for program php
ReplyDeleteit doesn't found string with when 'ajax' occure befor 'jquery'
ReplyDeleteit is slow, mysql does't use index on LIKE with % at start ('%LIKE') . use derrick solution^^.
$q=$_POST['q'];
ReplyDelete$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%'");
I am using the same code as you posted.
but the problem with your code is that it does not search if searching input is giving in reverse order. for example if I have something like this in DB 'php, mysql, ajax, wordpress'. If I search for ajax, php then your code will not return any results.
can you suggest how to solve it.?
Do you really know how helpful your site has been to some of us?
ReplyDeleteWould you please put an autocomplete with jquery, Ajax, PHP and MySQL please?
this is nice but it has no empty data error or no result not found error message
ReplyDeletethanks
plz provide testing related topics
ReplyDeleteI've been searching for 5 days now for the simplest search box with no luck until I found this...Thank you so so so much! I can finally proceed with my uni project..
ReplyDeletei have proble two text box (ex text1 city search text2 name search ) in code for php plz fast responce
ReplyDelete