HOWTO: Simple Search Engine with PHP and MySQL | Hackosis

Hackosis is an Open Blog. You Can Participate.

06
Nov

HOWTO: Simple Search Engine with PHP and MySQL
Filed under Featured, Tutorials, Web development.

PHP-MySQL Logo

There might be many reasons why you may be storing data in a database. As Google has showed us, searching data is an invaluable tool that can help minimize the time that it takes to find the information you need.

I would like to show you how you can build your own PHP search engine to find the information you seek in a MySQL database.

First you will need a LAMP to work from. For Windows users a quick and dirty way is to download and install XAMPP. For Linux users try searching Google for a tutorial.

OK, now we will skip to the part where you have a working LAMP install and a database that needs searching from a web interface. I will assume that you have basic knowledge of and where to put your PHP files and also some basic SQL query syntax.

NOTE: I am not including “<?php” at the beginning of documents, nor “?>” at the end I will assume that you will know that these are the operators to signify php code within a file. You may also download the zip file that contains all of the code examples listed below at the end of this document.

Step 1: We are going to create a config.php file with our database information. Please fill in the correct information for your MySQL database and save the file:

PHP [Show Styled Code]:

$dbhost='127.0.0.1';
$dbusername='db_username';
$dbuserpass='db_password';
$dbname = 'db_name';

PHP [Show Plain Code]:

$dbhost=‘127.0.0.1′;
$dbusername=‘db_username’;
$dbuserpass=‘db_password’;
$dbname = ‘db_name’; 

Step 2: Construct the search.php file.

It is always a good practice to make verbose comments in your code. If someone else comes along they might have some questions and may need to contact you.

PHP [Show Styled Code]:

/**************************************************************************************
 * Main Search Page - search.php
 * Author: Your Name 
 * This file searches the database
 **************************************************************************************/

PHP [Show Plain Code]:

/**************************************************************************************
 * Main Search Page - search.php
 * Author: Your Name <email@something.com>
 * This file searches the database
 **************************************************************************************/ 

Next, we are going to call the config.php file that we created in Step 1. We could just use ‘include’ instead of ‘require’, but this file is critical to our application, so we want the script to fail if it cannot find the ‘config.php’ file. You would also want to store this in a different directory if our PHP application had many files, but since there are only two, we will put it in the same place as search.php.

PHP [Show Styled Code]:

//Get variables from config.php to connect to mysql server
require 'config.php';

PHP [Show Plain Code]:

//Get variables from config.php to connect to mysql server
require ‘config.php’; 

After that, we are going to initiate a connection to our database. This pulls the variables from the config.php that we specified. If it cannot connect, it will print ‘Cannot select database’.

PHP [Show Styled Code]:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

PHP [Show Plain Code]:

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die(‘Cannot select database’); 

Now lets insert some code to pull a variable from the search form that we will create later in the file. This basically says, if the search variable is set through the form, retrieve it and store it. There is no sense in storing it if it is not set. Also, the curly brackets are the enclosure for the if statement.

PHP [Show Styled Code]:

//search variable = data in search box or url
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

PHP [Show Plain Code]:

//search variable = data in search box or url
if(isset($_GET[’search’]))
{
$search = $_GET[’search’];
} 

We don’t want extra whitespace in the search variable as it will make a pure wild card and return all results; so we trim it like this:

PHP [Show Styled Code]:

//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);

PHP [Show Plain Code]:

//trim whitespace from variable
$search = trim($search);
$search = preg_replace(‘/\s+/’, ‘ ‘, $search); 

Here comes the fun part. What we are doing here is seperating the words of the search variable, delimited by spaces and storing them in an array inside the keywords variable. I told you it was fun.

PHP [Show Styled Code]:

//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);

PHP [Show Plain Code]:

//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search); 

Now, just to double check ourselves we are going to make sure that no arrays are empty, as said before, if this is the case, it will return every row because of the syntax of our SQL query. If there is any empty arrays, we are ridding of them.

PHP [Show Styled Code]:

//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));

PHP [Show Plain Code]:

//Clean empty arrays so they don’t get every row as result
$keywords = array_diff($keywords, array("")); 

OK, we are ready to set the MySQL query. This is saying if the search variable is ‘null’ or is ‘%’ (MySQL wildcard) do nothing, else the MySQL query is whatever we set it to. Depending on the number of keywords, it will loop until it has competed the query for all words entered. You might have a preference for how your query is ordered, so replace the “ORDER BY column1″ with the column that you would like to sort by. You will have to replace table_name and column[1-4] with the table that you are searching in your database. If you have more columns that you are searching you will have to enter more OR statements. Some tweaking may be required here.

PHP [Show Styled Code]:

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i

PHP [Show Plain Code]:

//Set the MySQL query
if ($search == NULL or $search == ‘%’){
} else {
for ($i=0; $i<count($keywords); $i++) {
$query = "SELECT * FROM table_name " .
"WHERE column1 LIKE ‘%".$keywords[$i]."%’".
" OR column2 LIKE ‘%".$keywords[$i]."%’" .
" OR column3 LIKE ‘%".$keywords[$i]."%’" .
" OR column4 LIKE ‘%".$keywords[$i]."%’" .
" ORDER BY column1";
} 

Now we will run the query and store it in a variable named result. If the query fails it will print the MySQL error message. The curly bracket at the end will close our if statement from the previous code snippet.

PHP [Show Styled Code]:

//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
}

PHP [Show Plain Code]:

//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
} 

In the following code we are going to count the number of rows retrieved from the MySQL query.

PHP [Show Styled Code]:

if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}

PHP [Show Plain Code]:

if ($search == NULL or $search == ‘%’){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
} 

Now is when we start printing the HTML code. I have included the HTML inside of echo statements to show you how it works. Notice how you MUST provide “\” before any double quotes. Notice how the method of our form is “GET”? That must match how we retrieved the variable earlier. The body onload statement is a little javascript that will automatically place the cursor in the search box to avoid repetitive mouse gestures. Also, we will print the keywords that the user searched for below the search box. Customize the form to your liking.

PHP [Show Styled Code]:

echo "";
echo "";
echo "";
echo "
";
echo "";
echo "";
echo "";
echo "
";
echo "
";
echo "
";
echo "

";
//If search variable is null do nothing, else print it.
if ($search == NULL) {
} else {
echo "You searched for ";
foreach($keywords as $value) {
   print "$value ";
}
echo "";
}
echo "


";
echo "";

PHP [Show Plain Code]:

echo "<html>";
echo "<head>";
echo "<title>Your Title Here</title>";
echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />";
echo "</head>";
echo "<body onLoad=\"self.focus();document.searchform.search.focus()\">";
echo "<center>";
echo "<br /><form name=\"searchform\" method=\"GET\" action=\"search.php\">";
echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />";
echo " <input type=\"submit\" value=\"Search\" />";
echo "</form>";
//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>"; 

Next, we can print some error messages if need be telling the user if there is nothing in the search box or a wildcard, and if the search returned no rows from the database.

PHP [Show Styled Code]:

//If users doesn't enter anything into search box tell them to.
if ($search == NULL){
echo "Please enter a search parameter to continue.";
} elseif ($search == '%'){
echo "Please enter a search parameter to continue.";
//If no results are returned print it
} elseif ($count <= 0){
echo "Your query returned no results from the database.";
//ELSE print the data in a table
} else {

PHP [Show Plain Code]:

//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 { 

What we are going to print next, after the else above, is a table header. This is optional, especially if you choose not to display the results in a table.

PHP [Show Styled Code]:

//Table header
echo "
";
echo "
";
echo "


";
echo "


";
echo "


";
echo "


";
echo "


";
echo "


";
echo "
";
echo "COLUMN 1: COLUMN 2: COLUMN 3: COLUMN 4: COLUMN 5: COLUMN 6:


";

PHP [Show Plain Code]:

//Table header
echo "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";
echo "<tr>";
echo "<td><b>COLUMN 1:</b></td>";
echo "<td><b>COLUMN 2:</b></td>";
echo "<td><b>COLUMN 3:</b></td>";
echo "<td><b>COLUMN 4:</b></td>";
echo "<td><b>COLUMN 5:</b></td>";
echo "<td><b>COLUMN 6:</b></td>";
echo "<tr>";
echo "</table></center>"; 

Now we can start printing the data that was returned by the SQL query in a table. We are including some code to alternate colors in the table for readability. The code following is saying while there are rows in the result, store them in a variable named row and print the data. Also, the end if is from the error messages two code snippets above. The names column[1-6] should match the names of the columns in your MySQL database that you are wishing to display in the HTML table. If you are wishing to not display your results in a HTML table, feel free to change the HTML to something more fitting.

PHP [Show Styled Code]:

//Colors for alternation of row color on results table
$color1 = "#d5d5d5";
$color2 = "#e5e5e5";
//While there are rows, print it.
while($row = mysql_fetch_array($result))
{
//Row color alternates for each row
$row_color = ($row_count % 2) ? $color1 : $color2;
//table background color = row_color variable
echo "
";
echo "
";
echo "


";
echo "


";
echo "


";
echo "


";
echo "


";
echo "


";
echo "

";
echo "".$row['column1']." ".$row['column2']." ".$row['column3']." ".$row['colomn4']." ".$row['column5']." ".$row['column6']."

";
$row_count++;
//end while
}
//end if
}

PHP [Show Plain Code]:

//Colors for alternation of row color on results table
$color1 = "#d5d5d5";
$color2 = "#e5e5e5";
//While there are rows, print it.
while($row = mysql_fetch_array($result))
{
//Row color alternates for each row
$row_color = ($row_count % 2) ? $color1 : $color2;
//table background color = row_color variable
echo "<center><table bgcolor=".$row_color.">";
echo "<tr>";
echo "<td>".$row[‘column1′]."</td>";
echo "<td>".$row[‘column2′]."</td>";
echo "<td>".$row[‘column3′]."</td>";
echo "<td>".$row[‘colomn4′]."</td>";
echo "<td>".$row[‘column5′]."</td>";
echo "<td >".$row[‘column6′]."</td>";
echo "</tr>";
echo "</table></center>";
$row_count++;
//end while
}
//end if
} 

The last thing we are going to do is clear the memory of the result and close the body and html tags.

PHP [Show Styled Code]:

echo "";
echo "";
if ($search == NULL or $search == '%') {
} else {
//clear memory
mysql_free_result($result);
}

PHP [Show Plain Code]:

echo "</body>";
echo "</html>";
if ($search == NULL or $search == ‘%’) {
} else {
//clear memory
mysql_free_result($result);
} 

There are better ways to handle some of the operations in the PHP code in this example. The main point here was to split it up so it is more easily understandable. I hope this was a good exercise for you. Please be aware that the input IS NOT PROPERLY SANITIZED, so if you set the user in your config.php file as a user that can write to the database, there is a probability that SQL injection could occur.

Download the config.php and search.php files here.

Related Posts

Tags: MySQL, PHP, Search Engine, Tutorial, Web development

Like this post? Subscibe to the RSS feed.

93 Comments

hadi Says:
December 11th, 2007 at 4:36 am
How would you put the search engine in a template for the search results so it won’t go into a blank page but your own template
Shane Says:
December 11th, 2007 at 9:16 am
I would recommend looking up on some CSS coding.

http://www.w3.org/MarkUp/Guide/Style
Paul Filmer Says:
December 12th, 2007 at 5:41 am
nice code - been looking for something that can use - and . in the search.

I’m having real difficulty in setting a result as a link….

I have a field called [url] and I’m trying to code the result as a direct link - I’ve tried all combinations of and can’t get the code to work - any suggestions?

echo “”.$row['url'].”";
Shane Says:
December 12th, 2007 at 8:29 am
Give this a whirl. I am assuming that your field actually contains a URL:

echo “<a href=”.$row['url'].”>Link</a>”;
Shawn Says:
December 18th, 2007 at 2:23 pm
Greetings. Thank you so much for putting together this tutorial. I used it on my website and it’s flawless! (:

Question: I want to add a drop-down menu next to the search box to add the functionality of limiting results to specific database columns. In other words, my database table has 3 columns (or “fields”), and so my drop-down menu would also have 3 options, each corresponding with a unique column/field name.

Any help would be most appreciated.
Shane Says:
December 18th, 2007 at 3:38 pm
You would have to query the database for the column names, store it in an array, then pass the drop down menu value back into your main query for the search terms entered into the search box.
Olie Says:
December 18th, 2007 at 8:02 pm
Nice Code! Just what I was looking for.

Do you know how I can spread the columns further apart?

here is what mine looks like.

just type in a name like mike

http://www.nutritiondirect.org/point_system/search.php
Shane Says:
December 18th, 2007 at 8:46 pm
You should look into some CSS classes:

http://www.tizag.com/cssT/class.php

You can set the width easily this way.
Frank Says:
December 19th, 2007 at 8:58 am
great tutorial,

but i have a question:

it is possible to get two results that are the same with this script. Do you know how to “compare” the results and delete one of the two result that are the same?

thanks for your time,
Frank
Paul Filmer Says:
December 21st, 2007 at 1:48 am
thanks Shane - the link code works good!
Dan Says:
December 29th, 2007 at 6:27 pm
Excellent script! For unique results try this:

$searchResult = array();
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
if(!in_array($row, $searchResult){
array_push($searchResult, $row); }
}

:)
Steve Says:
January 15th, 2008 at 7:15 pm
This tutorial is really the best one I have encountered on the net and has furnished me with a good understanding of php - mysql search engine. I must confess that I am having a little trouble getting back the array results from the DB. The search I am submitting is returning results in the form of column headers with a very small centered coloured band where the results should be??… I am not entirly sure why this is… maybe it is my table? although a regular select pulls results… Please can you help ;-)
Shane Says:
January 15th, 2008 at 8:47 pm
Thanks Steve, I am glad you got good use from it!

What do you mean by ‘regular’ SELECT? Are you doing something obscure?

If you are still in a bind, upload the script (without usernames and passwords of course) and email me the URL.

Upload site:
http://rapidshare.com/

Contact Me
Steve Says:
January 15th, 2008 at 11:04 pm
Shane I must confess that I “was” doing something obscure I was running a query within a query within the search like old mainframe applies to DB2 piggybacking rex on the back of jcl against very large databases. I really had some difficulty but your “Are you doing something obscure?” reply just got me thinking and I have to say that I now have the code working in my obscure way LOL;-D I am very happy ;-D. Your tutorial code is of a very high and clearly thought out quality which I am still very grateful for. Your skill and the tutorial which again I am very happy to have come accross after looking for several days. ;-) again thank you Shane
Allen Says:
January 21st, 2008 at 11:16 am
Great work Shane.

How would I limit the number of results displayed as well as a generating “next page” until there are no more results?
Shane Says:
January 21st, 2008 at 12:13 pm
Hey Allen, thanks for stopping by.

Mabye this PHP paging tutorial would be helpful.
Owen Says:
January 23rd, 2008 at 10:16 am
Hi, great tut however I have one small question which is puzzling me.

To my tiny mind this is only ever going to pull the results of the last keyword in the array ($keyword[$i]) as the loop seems to be renewing the variable $query and then when finished looping runs the mysql_query…

Surely you need to store the created queries in an array and then loop thru the array storing records that way?

Feel free to shoot me down in flames (please!)

Oo
Shane Says:
January 23rd, 2008 at 6:16 pm
Owen,

You would think this would be the case, but the script works fine with multiple keywords.

Honestly, I don’t have the answer to why. Anyone else have some insight?
Kostas Says:
January 23rd, 2008 at 6:31 pm
Shane
Thanks for the excellent tutorial - brilliantly set out and easy to understand.
I too would like to add ‘pagination’ and you pointed Alan to an excellent site too.
I’ve just spent 3 hours trying to insert it into you search page with no luck!
Are you planning to write a ‘part 2′ in the near future including pagination?!
Thanks.
Kostas
Shane Says:
January 23rd, 2008 at 8:47 pm
Kostas,

I will post a pagination tutorial as soon as I get a chance. It is kind of tricky.
Lucian Says:
January 25th, 2008 at 4:49 am
After I have read your script I have 2 questions in mind:
1. what about chars like “%”,”/” or “‘” in search text
2. if column1 matches 4 of my $keywords, it will show 4 times
Lucian Says:
January 25th, 2008 at 5:24 am
Sorry about the second question, I’d read the code again :)
Shane Says:
January 25th, 2008 at 8:57 am
Lucian,

@ Question 1:

You need to be more specific. Are you trying to filter characters like that? If so, you might look into a sanitizing tutorial.
aaron cicali Says:
January 27th, 2008 at 12:18 pm
I’m confused. Looking at the first section of code where the query is constructed, it appears that the for loop would result in the query only containing the last of an array of keywords (multiple columns only searched by the last keyword). Am I missing something?

I have a search script I’m cleaning up, and I’m trying to find out if there’s a more efficient way to query besides:

SELECT * FROM table WHERE
column1 LIKE keyword1 OR column2 LIKE keyword1 AND
column1 LIKE keyword2 OR column2 LIKE keyword2
AND
column1 LIKE keyword3 OR column2 LIKE keyword3

The query requires all keywords to match for a positive result to be returned.

Great tutorial. Thanks for sharing.
Steve Says:
January 29th, 2008 at 3:54 am
Hi Shane - I was wondering if there was a way in PHP of restricting the number of times a user could pull from the DB i.e. 3 searches and then get a message sort of idea. The java onclick idea.. I have been playing with sessions but as of yet no joy. Any help would be great… thanks in advance, Steve
Shane Says:
January 29th, 2008 at 8:13 am
I would think it would entail a session where a variable gets incremented each time the script is run under that session ID. If the variable is less than three, do nothing, else print message.

To be honest, I have not played with session a whole lot, so I am in a similar boat with you Steve.
Allen Says:
January 29th, 2008 at 4:03 pm
Also, this maybe a dumb question, but I can’t figure out how to display results without a keyword search. For example, I simply want to list all the entries in a table where a field RESTAURANT matches keyword CHINESE FOOD.

I think there is a simple solution to this. Any help?
Allen Says:
January 29th, 2008 at 4:29 pm
Here is the pagination scrip that is working for me. I used the link Shane gave us and adapted it to Shanes code:

It doesnt seem to want to post the links. Here’s the code:

http://precisionautosport.com/code.txt
Shane Says:
January 30th, 2008 at 8:09 am
Allen,

Thanks for enlightening us with your pagination code. Mind if I include it in the post?

As far as your query question, I am a bit unclear at what you are trying to accomplish..
Mikko Says:
January 30th, 2008 at 1:45 pm
Hi!

I made a little tweak to the code:

$query = “SELECT * FROM table_name WHERE “;

for ($i=1; $i<=count($keywords); $i++)
{

$query = $query .”column1 LIKE ‘%”. $keywords[$i-1]. “%’ “;

if ($i<count($keywords))
{ $query = $query .”OR “;}
}

$query = $query .”ORDER BY column1″;

Now you don’t have to worry about how many words user enters into the search field.

The changes I made:

1)
I cut the $query down into smaller pieces.

2)
i=1; $i<=count($keywords);

i now starts from one instead of zero but as I added the ‘=’ to the condition the loop keeps on iterating until it reaches the count($keywords)but the number of iterations stays still the same as in the original version.

3)
$query = $query .”column1 LIKE ‘%” .$keywords[$i-1].”%’ “;

To get the correct entry from the key I deducted 1 from $keywords[$i]

4)
if ($i<count($keywords))
{ $query = $query .”OR “;}

This will prevent extra “OR ” from appearing into the end of the query (like: …column1 LIKE word3 OR column1 LIKE word4 OR ORDER BY column1) since it is printed once less than count($keywords) is.

Hope this explanation made some sense :)
Mikko Says:
January 31st, 2008 at 12:00 am
[edit]

Oh and add the semicollon at the end of query: $query = $query .”ORDER BY column1;″;

If you copy and paste the text straight from the site be sure to change those “tilted” single quotes to the “straight” ones (’ instead of ´)
karthi Says:
February 1st, 2008 at 2:39 am
i want a particular text serarch coding for validation in user registration page while i insert but i want tos search the particul name in my database
Lucas Says:
February 11th, 2008 at 2:39 pm
Hi,

really great tutorial !! Helped me out a lot with my MYSQL DB. Well, i got 2 questions regarding your script. I have 4 columns in my DB, ..let’s call them A,B,C and D. Well and i would like to have a search form where a user can choose at the beginning in which column he will be searching. Kinda “Scroll Out Space”, where he can choose between one of the 4 columns. After he has chosen one he can enter a search word next to a free textfield and the Search script will “only” search in that particular chosen column. And my last question is…how can i manage it that my results will be shown in a table which you can see??? One of my columns has longer results in it, so the whole “results” page doesn’t look even after search was finished. Just a table so that every column will have it’s own space and the whole results page will look ok and not in chaos. Thanks in advance. Regards, Lukas.
Steve Says:
February 26th, 2008 at 9:25 pm
Hi Shane - I was wondering if there was a way in which multiple tables could be searched?

Many thanks in advance ;-)
Mikko Says:
February 27th, 2008 at 1:56 am
Hi Steve!

What you’re looking after is using JOIN. More info on that can be found from http://www.informit.com/articles/article.aspx?p=30875&seqNum=5
Robert Newton Says:
February 27th, 2008 at 7:59 am
Thank you for making this tutorial. Between your search code (and explanation) and the example for pagination as posted by (Allen of precisionautosport) I have a fairly professional looking (and working) search for my sister’s website. If you’d like a link back to this site (seo), I’d be more than happy to oblige. Again, Shane and Hackosis, thank you. =)
Aaron Cicali Says:
February 27th, 2008 at 3:00 pm
Hi Shane,

I think there’s a bug in your example, please correct me if I’m wrong.

When you use a FOR loop to assign the variable $query, the query would only contain what was assigned to it during the last iteration of the for loop. This renders all but the final iteration useless.

This might work better:

//Set the MySQL query
if($search == NULL or $search == ‘%’)
{
//do nothing
}
else
{
$query = “SELECT * FROM table_name WHERE “;

for ($i=0; $i < count($keywords); $i++)
{
$query .= ” column1 LIKE ‘%”.$keywords[$i].”%’”.
” OR column2 LIKE ‘%”.$keywords[$i].”%’”.
” OR column3 LIKE ‘%”.$keywords[$i].”%’”.
” OR column4 LIKE ‘%”.$keywords[$i].”%’”;

if($i < (count($keywords) - 1))
$query .= ” AND”;
}

$query .= ” ORDER BY column1″;
}
Shane Says:
February 28th, 2008 at 8:24 am
Thanks for the suggestion Aaron. I will test this and post back the results. I am always up for improvements!
BT Says:
March 5th, 2008 at 8:00 pm
Cool site search.

One question

how can we add options to search a specific column ei

Keyword: europe
choose option(dropdown): London (selected)

and it searches only london columns and displays those results in london.

Many Many Thanks
Steve Says:
March 8th, 2008 at 7:46 pm
Hi Again guys, I firstly would like to thank Shane and Mikko for the help they have given me. I have one final question. How can I pull the data from the array in the form of either formatted XML,HTML or suchlike page. The reason I ask these questions here is simple…. I have needed help with php given the time limits that I am working to and have found that there is no BS or Ego here, the content is from genuine clear thinking people. BTW,If somebody would find the php session eq to the java onclick code I created useful, please let shane know and I will be happy to provide.
thanksToYou Says:
March 11th, 2008 at 8:10 pm
nice code, but how can i make the column much further?
please help me!

thanks!
Mikko Says:
March 12th, 2008 at 4:03 pm
Hi guys!

BT:

You’ll have to first assign values to your dropdown menu :

London
Helsinki

Then when processign the values from GET:
$city =$_GET['city']

And adjust your query:

$query = “SELECT * FROM table_name WHERE column1 LIKE ‘%”.$city”;

Steve:
The results should show up as a formatted HTML-page if you followed through the whole example (or downloaded the files attached to the end of tutorial.

thanksToYou:
What do you mean by making columns further?
Mikko Says:
March 12th, 2008 at 4:07 pm
Damn. The site apparently doesn’t want to print out pure html code :p I’ll try replacing signs with { and } respectevly in BT’s example. See if this works…

{form action=”searchform.php” method=”GET”}
{select name=”city”}
{option value=”london”}London{/option}
{option value=”Helsinki”}Helsinki{/option}
{/select}
{/form}
Alen Says:
March 18th, 2008 at 8:03 am
Great job!

I found out that the query is key sensitive.
What should i do for the script to get all the results regardless the KeyCase

thanks
Shane Says:
March 18th, 2008 at 8:58 am
Alen,

By default the query should NOT be case sensitive. It may have something to do with collation.

See here:

MySQL Case sensitivity
Alen Says:
March 18th, 2008 at 10:13 am
Thanks for your prompt reply

i just did copy paste your script and changed the columns and db name etc.

if i search for Hill or hill i get different results..

the Db is utf8_unicode_ci type

thanks
Alen Says:
March 18th, 2008 at 1:42 pm
[append]

It was my fault the table city was utf8_bin
I change it to utf8_general_ci and it works like charm

thanks again!
Alen Says:
March 20th, 2008 at 6:32 am
hi,

I’m trying to do a select query without success.

My problem is:
I have a table “users” with fields: city,country
and a table “Country” with fields: id,name

in the table “users” the field country is the filled with “id” (1,2,3..) of the country.

What i want to archive is to form the select statement that it would get from the table “country” the name field so i could use it in the search.

Usually I’m doing it like the following but i don’t know how to implement it in the search form:
SELECT country.name, users.city, FROM country, users WHERE country.id = users.country

any help?
thanks
Allen Says:
March 20th, 2008 at 9:01 am
Hi again,

Can we take the code one step further? Let’s say I have a database with listings. How would I be able to store pictures in the database along with its info such as address, name, etc. Would I store the actual image in mySQL? or just store the path to the image file on my server then call it using img src=”"
Alen Says:
March 20th, 2008 at 9:12 am
Allen

The best is to store the path of the image in the sql. but if you plan to have A LOT of images i guess the mysql choice will be faster than the filesystem.
Alen Says:
March 20th, 2008 at 9:48 am
Reply to post: By Alen on Mar 20, 2008

I did my homework :) Im posting the solution in case someone has the same Q

$query = “select * FROM users LEFT JOIN country on users.country=country.id ” .
Shane Says:
March 20th, 2008 at 10:01 am
Great work Alen. ;)
Alen Says:
March 20th, 2008 at 10:23 am
Shane can i ask you a Q?

now im getting two ID fields (one form users and the one from country)

How can i escape define which fields from the joined table to be joined? :)
Shane Says:
March 20th, 2008 at 12:17 pm
Is something like you are looking for?

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
Al Says:
April 3rd, 2008 at 8:41 am
Hello,
Great article! I am extremely new to the web developing community, and trying to teach myself how to do all of this =) This article has helped me quite a bit with developing a search engine for my site.

Question though.
When performing the search, I am able to retrieve information from my database just fine. The problem is, my echo’ed keyword is not exact to my database.

Example
I type in “Help” for the search word. My echo’ed result is “Help” BUT, in my database, the “Help” is actually “HELP”.

How do I go about making the echo’ed search result exactly case sensative to the database information?

Thanks for the help =)
Albert Says:
April 24th, 2008 at 7:19 am
nice code they have done exactly what i was in need of. thanks
Robert Says:
April 25th, 2008 at 1:44 pm
Shane,

How can I “AND” the keywords so that if more than one keyword is entered, all keywords are required to be in the results?
Shane Says:
April 25th, 2008 at 2:18 pm
//Set the MySQL query
if ($search == NULL or $search == ‘%’){
} else {
for ($i=0; $i $query = “SELECT * FROM table_name ” .
“WHERE column1 LIKE ‘%”.$keywords[$i].”%’”.
” AND column2 LIKE ‘%”.$keywords[$i].”%’” .
” AND column3 LIKE ‘%”.$keywords[$i].”%’” .
” AND column4 LIKE ‘%”.$keywords[$i].”%’” .
” ORDER BY column1″;
}
Robert Says:
April 25th, 2008 at 4:53 pm
Shane,

I meant I wanted all keywords to be matched in the results. If the keywords entered are “blue” and “rug”, I want all the results to have blue and rug in them (so blue rug, blue rugs, long blue rug(s), wide blue rug(s), etc.

Something else I’ve noticed (now that I’m playing with this again) is the last keyword is the limiting factor in the results. If for instance you enter “blue” and “rug” again, and blue by itself has 40 results and rug 12, your results will total 12, because rug is the last keyword. If you enter “rug” and “blue,” you will get 40 results, because blue is the last keyword.

I think someone else has alluded to this in the comments. I test some of the posted suggestions, and will get back to you if you’d like.

Still, if you have any ideas how I can get the keyword results combined, I’d greatly appreciate it.
Robert Says:
April 26th, 2008 at 10:51 am
Shane,

Ok, modified my queries to work with the script by Aaron Cicali, and the results are more consistent. Although, reversing the order of my keywords results in a consistent 2 results difference (when 2 keywords are entered).

Still not getting the keywords being “AND” together.
Robert