So this is big. Most company want a very indepth search on there website, so what do you do. Well most people just do a simple SQL query with the like operator in there. However search is much much more than that. Several things to take in account are stop words, relevancy, uppercase/lowercase, trimming, multiple words, and so much more. Hopefully this will help you a little.

Things to remember

• Never run the user supplied information straight to the database
• Trim off the extra spaces
• Convert to lower case
• Clean out stop words like: ‘and’, ‘if’, ‘or’……
• Strip out punctuation

To start:

1
2
3
4
5
6
7
8
9

//Make this bigger for what you need
$stop_words = array(" if", " and ", " other ", " dont ", " never ");
$punctuation = array('/','\\','\'','<','>','(','*','&','?',';',':','[',']','{','}','|'
                             ,'=','+','"',',','.','-','_',')','^','%','$','#','@','!','~','`');
 
$string = strtolower($_GET['search']);
$string = str_replace($stop_words, ' ',$string);
$string = str_replace($punctuation, ' ',$string);
$string = trim($string);

Porter Stemming Algorithm

Check this out, Porter Stemming Alorithm. You should become acquainted with this algorithm if u want to build a great search. Basically every word has multiple variations, and what you want to do it dumb down all words to there basic.

For example: The word ‘Visual’ can also be Visuals, Visualization. If you just do a basic search and type Visualization, things like visual wont show up. What do you do? Well download a class that applies this algorith to your string.
This article is still being written

What Now?!

Now is time to think. Go figure. So on to the actual query. You can write it with a simple query like this:

1

$sql = "select * from table where column like '%$string%';"

The only problem with that is the search is gonna have to be near exact, so what you need to do is search with an or and split the string by words.

1

$sql = "select * from table where (column like '%$word1%' or column like '%$word2%');"

So now you have all these results.. Now is the hard part and the part i leave you at. Your gonna have to write an algorithim or a means of reordering the list. Some things to remember. More exact results should show up higher on the list.

Author: Jason Rogers

Well to start i thought i was a genius for setting up the MYSQL replication server. Well im not….. Anyways i found it really helped but it wasnt a complete solution the load is still intense. After much research i found that what i did would be completly awesome in a read only enviroment, but there is writing involved. Because of this the replication still bottlenecks at certain places. What do i do?!?!?! Well after a weekend of research i have another solution im going to try. Caching. I noticed that we get roughly 60k page views a day. Thats a crap load of queries given that every page has some sort of database connection. Ive decided that for this im going to use Memcached. “Memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.”–Taken right off there site. Well so we have database connections cached, what about static html and images? For that im going to implement Squid and set it up as a reverse proxy server.

The Rundown of Memcached

Memchached can cache database queries, flat out bad ass. However all these queries are being put into memory, and whats cool about memcached is that you can have it hosted on another server. Basically you run a query on a page, the same query gets hit by users say every 4 seconds. Well cache the results of the query and keep them cached for 5 seconds. This will add about a 5 second delay to when items get updated, but will alleviate all those queries to huge monolithic poorly designed databases!

Sharding the database

Sharding, try searching it. Took me forever to find some descent sources to learn what the heck this is. As far as i knew before this, it was when you died in EQ2 and needed to find your shard so you don’t have an exp penalty :). Any who. Sharding. What it means is to break your database/database server into many. Only storing a certain region of the data on each shard. Now each shard can be replicated and clustered all you want, but the overall concept of separating the data across several servers has to remain. Whats the point you ask? Why would you waste time and effort? But managing it would suck, right? Ill tell you the point. When you do a write to a table in a database it locks it temporarily to change the data. Say you have a very intensive back end that needs updating a lot…well thats a lot of locking and will cause the reading to get backed up. So what you do is split the data across several servers, so that the weight of writing to one server is now spread across many causing less backups. The downfall however is you’ll have to teach your program to know what server to connect to for its information.

Author: Jason Rogers

Well i had this problem at my work. I developed an intensive piece of software that during work hours would cause certain tables to lock. Being that we have one monolithic database server this is a huge problem. Normally i could care less with only a couple people working on the software they’d only notice a few seconds longer to submit there entry. Well the problems is that the live sites would be working in the same database, and so being affected and in some situations would cause the sites to lose connection all together. The solution finally was to set up a second MYSQL server as a slave and turn on the replication aspect of MYSQL. This solved all our problems and is actually very easy to maintain.

So what im going do for you is give you a basic run down on what you need to do to set this up.

What you need:

• 2 Servers
• MYSQL installed on both of them
• Some SQL/linux knowledge

Step 1:
The first step that i would take is make sure that all the data in the two servers is the same. This can be done usually with a mysqldump, however you do it make sure the information you want replicated is on both mysql servers.
Step 2:
Choose which server will the master and which will be the slave. So the first thing is to locate the my.cnf file on the master server. This file is usually located in the /etc/ directory. Once located edit and add the entry under the [mysqld] section.

1
2
3

[mysqld]
server-id=1
log-bin

The server id can be whatever you want, but no server should have the same id. Next do the same thing with the slave except dont add the log-bin entry. like so:

1
2

[mysqld]
server-id=1

Step 3:
Your gonna need an account for the slave to access the master. I would advise greatly against using root, or any other day to account. Instead create an account for this use only. You can create a basic user like this from inside mysql like so:

1

mysql>GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'somepassword';

Step 4:
At this point in the game i would probably go through and restart both your mysql servers. Super simple, the basic way to do this if your server i set up standardly is.. go to /etc/rc.d/init.d/ then type ./mysqld restart

Now that your to this point what we need to do is find out your Binary Log Position. This is vital so that the slave knows where to start pulling sql statements from. So what you need to do is log in to the master mysql server and excute this statement:

1
2
3
4
5
6
7

mysql> show master status;
+--------------------------------+----------+-----------------+--------------------+
| File                                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------------+----------+-----------------+--------------------+
| thename-bin.000001           | 6376597 |                     |                          |
+--------------------------------+----------+-----------------+--------------------+
1 row in set (0.00 sec)

Remember all of this as you’ll need it in the next and final step.
Step 5:
YAY final step. Go to your slave server and login. Then type this in:

1
2
3
4
5
6
7
8

mysql> change master to
master_host='<ip-address or hostname>',
master_user='replication_user',
master_password='somepassword',
master_log_file='thename-bin.000001',
master_log_pos=6376597;
 
mysql> start slave;

and that was all she wrote. There are now a bunch of commands and other things you can do but ill write down a few vital commands you should know for maintaining these:

1
2
3
4

stop slave;  //stops the slave from pulling from the master
start slave; //starts slave to pull from master
show slave status; //this command is run on the slave server to check its status
show master status; //this command is run on the master server to check its status

i like the last two alot so that when i check the binary log position i can make sure that there both sync’d at the right record.

Author: Jason Rogers

Well about an hour ago i lost my thumb drives which hold all my basic functions i use on a day to day basis. As any Geek will tell you this sort of thing almost certianly means the end of the world, but i will go on. Without them im at a huge loss, so as i try to rebuild my library of functions ill post some of them. Note: Most of the functions are all php.

Ever try to create a back-end for a website, or simple just building a form, and can’t seem to get those damn quotes to show in the textbox? Well my co-worker just asked me about this and is pretty frustrated. The simple solution is to create a basic function that uses a string replace to put in the 7-bit ASCII value instead of the character.

Example 1 - Quotes in a text box:

1
2
3
4
5
6
7
8
9
10

<?PHP
function code_for_ascii($data) {
     $temp = str_replace("'", '&#39;', $data); //Single Quote
     $temp = str_replace('"', '&quot;', $temp); //Double Quote
     return $temp;
}
 
$test_variable = '"jason"\'';
?>
<input type="text" name="test" value="<?PHP echo code_for_ascii($test_variable); ?>">

This functions cool and you may have tried it just now. If you did you’ll see that when you submit the form the returned response is

\”jason\”\’

kinda wierd but it will escape the characters for you. Now say you want to put that request back into a textbox you’ll need to do it like so.

1
2
3
4

<?PHP
$variable_1 = code_for_ascii(stripslashes($_REQUEST['test']));
?>
     <input type="text" name="test" value="<?PHP echo $variable_1; ?>">

Author: Jason Rogers