php and mySQL

MySQL FULLTEXT Searching

2011-04-07T22:03:00.000-07:00

Have you ever wanted to search text stored in your database, but couldn't figure out how to do it efficiently? Are you lazy like me and don't enjoy maintaining reverse indexes, dictionaries, and word scores? You're in luck. The release of MySQL 4.0 has made searching text stored in databases available to the masses.
MySQL has had FULLTEXT searching in one form or another since version 3.23.23.FULLTEXT indices in MySQL allow database administrators and programmers to designate any character-based field (CHAR, VARCHAR, or TEXT) as a FULLTEXT index, which allows for complex text searching against data stored in those fields.
This feature is not to be confused with the LIKE function in MySQL. LIKE works more along the lines of a regular expression. On the other hand, FULLTEXT indices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.
This article assumes you have a working installation of MySQL, a good understanding of how MySQL works, and a basic understanding of web programming (with PHP, Perl, or something similar). Further, this article may not be of any interest to those who are already using FULLTEXT indices in a production environment.

How it Works

The MySQL team has made it extremely easy to add FULLTEXT searching to your tables. They are created much like regular KEY and PRIMARY KEY indices. For the purpose of this article we are going to make a basic blog table, put some data into it, and start searching. Before we get ahead of ourselves we need to create some tables.

Creating the tables

-- The main blog table with our FULLTEXT index
-- Nothing extreme here, but you get the idea
CREATE TABLE blog_entries
(
entryID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
posted INT(11) UNSIGNED NOT NULL DEFAULT '0',
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
title CHAR(255) NOT NULL DEFAULT '',
entry TEXT NOT NULL DEFAULT '',
PRIMARY KEY (entryID),
KEY (posted),
KEY (categoryID),
FULLTEXT(title,entry)
);

-- A category table so you can organize your posts and
-- later do some fun searching based on such data.
CREATE TABLE blog_categories
(
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
name CHAR(75) NOT NULL DEFAULT '',
PRIMARY KEY (categoryID)
);

-- Add some data into your blog
INSERT INTO blog_categories VALUES (1,'Personal');
INSERT INTO blog_categories VALUES (2,'Work');
INSERT INTO blog_categories VALUES (3,'Editorials');

INSERT INTO blog_entries
VALUES (1,
1050942000,
1,
'About miester',
'I was born in michigan in 1980 in a small town called Adrian.
My mother is named Sue, while my father is named Mike.
They currently live in a small town called East Jordan. On April
27th, 2003 I will graduate from Eastern Michigan University with a
degree in Computer Information Systems.');

INSERT INTO blog_entries
VALUES (2,
1050942000,
2,
'Today at work',
'While I was at work today I was having some problems
with the RAID array. It seems that we have a rogue cron script that
is causing problems. When I find out more info I will post it here.');

INSERT INTO blog_entries
VALUES (3,
1050942000,
1,
'Vacation!',
'After I graduate I am taking a 2 week vacation. On my
agenda is a trip to Washington DC to see my girlfriend\'s sister
as well as throwing a few discs at the local disc golf course.');

INSERT INTO blog_entries
VALUES (4,
1050942000,
1,
'Vacation!',
'I have had a horrible cold for the last few days. Today I drank a
revive vitamin water with 150% of my daily dose of vitamin C. That
should help things out.');

Querying the Data

Now that we have data in our tables we can begin to query it. There are some restrictions to FULLTEXT searching, which are covered below. You will want to read over the restrictions before you use FULLTEXT indices in a production environment. For now we are going to do a simple query for the word mother.

mysql> SELECT entryID,title
-> FROM blog_entries
-> WHERE MATCH (title,entry) AGAINST('mother');
+---------+---------------+
| entryID | title         |
+---------+---------------+
|       1 | About miester |
+---------+---------------+
1 row in set (0.00 sec)

There are a few things to note when querying FULLTEXT indices. First, MySQL automatically orders the results by their relevancy rating. Second, queries that are longer than 20 characters will not be sorted. Third, and most importantly, the fields in the MATCH() should be identical to the fields listed in the table's FULLTEXTdefinition.
All other MySQL syntax works as you'd expect with a FULLTEXT search, meaning you can further limit your search terms. We could search blog entries based on posting date or category. If you let your imagination wander you can think of all sorts of ways to filter your data. Let's look for blog entries that only appear in the Personalcategory and match the term michigan.

mysql> SELECT E.entryID, E.title, C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
->       MATCH (E.title, E.entry) AGAINST ('michigan') AND
->       E.categoryID=1;
+---------+---------------+----------+
| entryID | title         | name     |
+---------+---------------+----------+
|       1 | About miester | Personal |
+---------+---------------+----------+
1 row in set (0.00 sec)

Note that we not only did a join but also filtered the results based on the category. Another thing to note is that FULLLTEXT indices are not case sensitive. If you would like to use MySQL's relevancy rating in your code you can add the MATCH() ... AGAINST() clause to your SELECT statement as well.

mysql> SELECT E.entryID, E.title, C.name,
->        MATCH (E.title, E.entry) AGAINST ('michigan') AS score
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
->       MATCH (E.title, E.entry) AGAINST ('michigan') AND
->       E.categoryID=1;
+---------+---------------+----------+-----------------+
| entryID | title         | name     | score           |
+---------+---------------+----------+-----------------+
|       1 | About miester | Personal | 1.2635315656662 |
+---------+---------------+----------+-----------------+
1 row in set (0.00 sec)

Boolean Searches

Probably the most anticipated feature in MySQL 4.0's FULLTEXT is the ability to do boolean searches without having to process the query strings. This means you can add +s and -s to your queries, along with a host of other commands, and MySQL will interpret them for you.

mysql> SELECT E.entryID,E.title,C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
->       MATCH (E.title,E.entry) AGAINST ('+vacation -washington' IN BOOLEAN MODE) AND
->       E.categoryID=1;
+---------+-----------+----------+
| entryID | title     | name     |
+---------+-----------+----------+
|       4 | Vacation! | Personal |
+---------+-----------+----------+
1 row in set (0.00 sec)

We have two entries with the word vacation in the title, but since we removedwashington, entryID 4 does not show up in the result. You can read all about IN BOOLEAN MODE on MySQL's FULLTEXT manual page.

Restrictions

A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.

FULLTEXT indices are NOT supported in InnoDB tables.
MySQL requires that you have at least three rows of data in your result set before it will return any results.
By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.
By default, your search query must be at least four characters long and may not exceed 254 characters.
MySQL has a default stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will return zero rows.
According to MySQL's manual, the argument to AGAINST() must be a constant string. In other words, you cannot search for values returned within the query.

Altering `FULLTEXT`'s Default Behavior

There are several ways to alter the default behavior of FULLTEXT. MySQL has sometips for fine tuning the FULLTEXT search, but the details are a little sparse. The most common problem is the four character minimum word length on queries. Before we go over that, let's review the variables associated with the FULLTEXT searching.

mysql> SHOW VARIABLES LIKE 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_min_word_len          | 2              |
| ft_max_word_len          | 254            |
| ft_max_word_len_for_sort | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

The variable we wish to change is ft_min_word_len. According to the manual we should be able to change this via the SET VARIABLE command, but, in reality, this does not work. After asking the mailing list about this problem I was told this had to be specified as a startup option. To change the minimum query string to three characters, start the MySQL server as follows.

$ cd /path/to/mysql
$ ./bin/safe_mysqld --user=mysql -O ft_min_word_len=3 &

After you have restarted your MySQL server (don't forget to change your startup scripts) you have to rebuild the FULLTEXT indices. The manual suggests the following command:

-- Replace tbl_name with the name of your table
mysql> REPAIR TABLE tbl_name QUICK;

After you have rebuilt your indices, you should be able to search with query strings of three or more characters instead of the default four character limit.

Having fun with MySQL `FULLTEXT` Searching

I'll save the implementation details for a later article, but here are some interesting ways in which you could use MySQL FULLTEXT searching to finding data on your website more interesting.

PHP

A great way to add cross referencing to articles would be to store a query (i.e.,linux for a post about Debian GNU/Linux) with each article. If an article had a query, PHP could then perform a FULLTEXT search, returning those results as "Related Articles". Furthermore, you could use PHP to create an advanced search script that allowed users to search the database based on category, criteria, pricing, etc.

`ispell`

ispell is a great tool for open source developers to use to make their applications more user friendly. By adding ispell to your search engine, you can check the spelling of each query, offering alternate queries if the query was spelled wrong. Everyone's favorite search engine does something similar.

Looking towards the Future

According to the manual, the MySQL team has a lot they still want to implement intoFULLTEXT searching. Here is a brief overview of those enhancements:

Proximity operators
Making the stopword lists depend on the language of the data
Stemming

The proximity operators will really make FULLTEXT searching impressive. This will allow you to do searches on words based on how close together they are. For example, if you currently searched for 'mysql search' you would get results even where mysql and search appear at opposite ends of the document. With proximity operators, the scoring algorithm gauges how close together the words are. Documents where mysql and search appeared directly next to one another would score higher than documents where they were not close together.
Stemming is a great way to make search engines smarter. This would allow MySQL to search for words that share the same lexical root. For example, queries forrunning would return documents with ran and run as well as running.

Conclusion

As you can see, the possibilities of FULLTEXT are almost endless. For those of us looking for an easy and powerful solution to our searching woes, MySQL has come up with an answer.
Joe Stump is the Lead Architect for Digg where he spends his time partitioning data, creating internal services, and ensuring the code frameworks are in working order.

MySQL Commands List

2011-03-30T15:54:00.000-07:00

MySQL Commands List

Here you will find a collection of basic MySQL statements that should prove useful for basic CRUDS operations (create, replace, update, delete, select).

There may be some issues with the kind of quotes you use around your data. If you are having difficulties, try using singe quotes ( ' ) or tricky quotes ( ` -- on keyboard key next to number 1) around your data. One or the other is bound to work.

The following statements are covered in this page:

CREATE DATABASE
CREATE TABLE
INSERT
REPLACE
UPDATE
SELECT
DELETE

VALUES
SET
WHERE
IN
AND
OR
RLIKE
DISTINCT
MAX

CREATE DATABASE

CREATE DATABASE database_name ;
Will create a MySQL database.
You may have to ensure that the user you used to log in has enough privileges to create databases.
Some web hosting companies do not allow their customers to create databases -- as they have already created a couple databases for you. In this case you should already have enough privy's to create tables on the already created databases. Check with your hosting provider to see what the proper database names are and what the usernames and passwords are for the database(s).
Example:
CREATE DATABASE myMediaLibrary;

CREATE TABLE

Create a table on the database you are currently logged into.
Example:
CREATE TABLE IF NOT EXISTS `music` (
`id` int(8) unsigned NOT NULL,
`artist` text NOT NULL,
`album` text NOT NULL,
`title` text NOT NULL,
`track` text NOT NULL,
`year` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `artist` (`artist`),
FULLTEXT KEY `album` (`album`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `track` (`track`),
FULLTEXT KEY `year` (`year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
The table set up will be similar to:

id artist album title track year

NOTE: You may want to use AUTO INCREMENT to have MySQL automatically maintain the id field when you add a new item to the table. This is up to you on how you would like to maintain the id., which in this case is the "PRIMARY KEY" which needs to be unique and is what MySQL needs to keep things orderly:
CREATE TABLE IF NOT EXISTS `music` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`artist` text NOT NULL,
`album` text NOT NULL,
`title` text NOT NULL,
`track` text NOT NULL,
`year` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `artist` (`artist`),
FULLTEXT KEY `album` (`album`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `track` (`track`),
FULLTEXT KEY `year` (`year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Refer to the MySQL documentation for more info on defining column data types.

INSERT STATEMENTS

INSERT INTO table_name ( `col_A`, `col_B`, `col_C`) VALUES ( `col_A_data`, `col_B_data`, `col_C_data`) ;
Inserts a row into the table using the data defined in the VALUES section.
As you can see, the column names are established within the first set of parenthesis, the order of the data in the second set of parenthesis must match the order of the column names defined int he first set of parentheses.
Example:
INSERT INTO music ( 'id', `artist`, `album`) VALUES ( '1', `the beatles`, `Abbey Road`);
Puts a row into the database tables as:

id artist album title track year

1 the beatles Abbey Road

I've color coded each "pair" so you can see that the data "col_B_data' that is going to go into column name "col_B" must be second. if there is no data available for col_B, use empty single quotes as:
INSERT INTO table_name ( `col_A`, `col_B`, `col_C`) VALUES ( `col A data `, ``, `col C data`) ;
Example:
INSERT INTO music ( 'id', `artist`, `album`) VALUES ( '1', ``, `Abbey Road`);
Puts a row into the database tables as:

id artist album title track year

1 Abbey Road

REPLACE STATEMENTS

REPLACE INTO table_name ( `col_A`, `col_B`) VALUES ( `col A data`, `col B data`) ;
Replaces items in a row. This action is dependant on the "id" because all tables need to have at least one "unique" column. Meaning that one of the columns must be used to provide row data that is unique to each row. In this example, "id" is the unique row.
Example:
REPLACE INTO music ( 'id', `artist`, `album`) VALUES ( '1', `the beatles`, `abbey road`);
Puts a row into the database tables as:

id artist album title track year

1 the beatles Abbey Road

As you can see, we've just replaced the empty "artist" with the new data of "the beatles"
REPLACE behaves much like INSERT except that if the "unique column" is supplied, the existing row will be updated, if the "unique column" is not provided, a new row will be added.
Example:
REPLACE INTO music ( `artist`, `album`) VALUES ( `the beatles`, `abbey road`);
Puts a new row into the database tables as:

id artist album title track year

1 the beatles Abbey Road

the beatles Abbey Road

NOTE: You may get errors when trying this kind of REPLACE, because usually the "unique column" can not be null (or empty). Some people like to set up the database where the "unique column" is automatically incremented by MySQL. This is kind of confusing and can lead to a headache. So just remember to include the "unique column" when using the REPLACE statement, or you'll get duplicate rows... OR use the UPDATE statement.

UPDATE STATEMENTS

UPDATE table_name SET col_B='new_data' WHERE col_A='reference_data' ;
Will update a row's columns with the new values specified in the SET section and the row to update is specified by the WHERE section
Example:
UPDATE music SET title='Come Together' WHERE id=1;
Notice that quotes are not around the id-1. This is because you shouldn't have to use quotes when you are entering an integer.

id artist album title track year

1 the beatles Abbey Road Come Together

To update two or more columns, separate value pairs in the SET section with a comma
Example:
UPDATE music SET track='1', year='1969' WHERE id=1;
Notice that quotes are not around the id-1. This is because you shouldn't have to use quotes when you are entering an integer.

id artist album title track year

1 the beatles Abbey Road Come Together 1 1969

It may seem frustrating to see that quotes were used around the "integers' in the SET section, but not in the WHERE section. Sometimes you may need to use them, but sometimes you do not. A llot of it depends on how the database was originally set up. If we initially set up the database where "track" and "year" did not use TEXT, but rather INT(11) we could probably get away with not using quotes around the data in the SET section. This issue also has to do with how MySQL is set up on your server.
Another thing to consider is that because we're using single quotes around the data that we are entering... what if your data contains a single quote? Such as:
UPDATE music SET title='Mike's Song' WHERE id=1;
As you can see, now there are three single quotes, and chances are MySQL will return an error. There are two things you can try.
1. Escape the single quote in the data as:
UPDATE music SET title='Mike\'s Song' WHERE id=1;
1. Use tricky quotes:
UPDATE music SET title=`Mike's Song` WHERE id=1;
Tricky quotes are just above the "tab" button on your keyboard.

SELECT STATEMENTS

SELECT * FROM table_name WHERE 1 ;
Returns all rows and all columns from table_name.. All rows is specified by the * (asterisk) symbol between SELECT and FROM.
Example:
SELECT * FROM music WHERE 1;
Returns:

id artist album title track year

1 the beatles Abbey Road Come Together 1 1969

2 the beatles Abbey Road Something 2 1969

3 the beatles Abbey Road Maxwell's Silver Hammer 3 1969

4 the beatles Abbey Road Oh! Darling 4 1969

5 the beatles Abbey Road Octopus's Garden 5 1969

6 the beatles Abbey Road I Want You (She's So Heavy) 6 1969

7 the beatles Abbey Road Here Comes the Sun 7 1969

8 the beatles Abbey Road Because 8 1969

9 the beatles Abbey Road You Never Give Me Your Money 9 1969

10 the beatles Abbey Road Sun King 10 1969

11 the beatles Abbey Road Mean Mr. Mustard 11 1969

12 the beatles Abbey Road Polythene Pam 12 1969

13 the beatles Abbey Road She Came in Through the Bathroom Window 13 1969

14 the beatles Abbey Road Golden Slumbers 14 1969

15 the beatles Abbey Road Carry That Weight 15 1969

16 the beatles Abbey Road The End 16 1969

17 the beatles Abbey Road Her Majesty 17 1969

18 beatles Rubber Soul Drive My Car 1 1969

19 beatles Rubber Soul Norwegian Wood (This Bird Has Flown) 2 1969

20 beatles Rubber Soul You Won't See Me 3 1969

21 beatles Rubber Soul Nowhere Man 4 1969

22 beatles Rubber Soul Think for Yourself 5 1969

23 beatles Rubber Soul The Word 6 1969

24 beatles Rubber Soul Michelle 7 1969

25 beatles Rubber Soul What Goes On 8 1969

26 beatles Rubber Soul Girl 9 1969

27 beatles Rubber Soul I'm Looking Through You 10 1969

28 beatles Rubber Soul In My Life 11 1969

29 beatles Rubber Soul Wait 12 1969

30 beatles Rubber Soul If I Needed Someone 13 1969

31 beatles Rubber Soul Run for Your Life 14 1969

SELECT * FROM table_name WHERE column_name IN ('data_1','data_2') ;
Returns all rows* and all columns that have matching data specified in the IN section. The data defined in the IN section must match exactly.
NOTE: All rows is specified by the * symbol between SELECT and FROM sections.
Example:
SELECT * FROM music WHERE track IN ('1','2');
Returns:

id artist album title track year

1 the beatles Abbey Road Come Together 1 1969

2 the beatles Abbey Road Something 2 1969

18 beatles Rubber Soul Drive My Car 1 1969

19 beatles Rubber Soul Norwegian Wood (This Bird Has Flown) 2 1969

SELECT col_A_name, col_B_name FROM table_name WHERE col_B_name='search_term' ;
Returns only two columns from the matching row. The data defined int eh WHERE section must match exactly.
Example:
SELECT artist, album FROM music WHERE title='You Never Give Me Your Money';
Returns:

artist album

the beatles Abbey Road

SELECT col_A_name, col_B_name FROM table_name WHERE col_B_name IN ('search_term_1','search_term_2') ;
Returns only two columns that match the data defined in the IN section.
Example:
SELECT title, album FROM music WHERE title='You Won\'t See Me', 'I\'m Looking Through You';
NOTE: Notice that the single quotes are "escaped" by using a back slash within the data. You may want to try using tricky quotes instead as:
SELECT album, date FROM music WHERE title=`You Won't See Me`, `I'm Looking Through You`;
Returns:

album date

Rubber Soul 1969

Rubber Soul 1969

SELECT * FROM table_name WHERE col_A_name RLIKE ('search_termA') OR col_B_name RLIKE ('search_termB') ;
This example will search through your table for matching words.
Will return all rows that contain either search_termA OR search_term_B -- the search term can be a single word or a phrase.
NOTE: This options is based on having MySQL set up to take advantage of FULLTEXT, which was established when we set up the table. Also note that some MySQL configurations will not return matches on words that are shorter than 4 characters. So for this example, MySQL may not return anything. So you may want to experiment with this kind of search with a larger word such as "Yourself."
NOTE 2: This is actually the old way to do things, but more reliable than the new method. The new method uses the MATCH statement, but requires some additional configuration on your MySQL installation / server. The method offered here is probably a little more reliable as of this writing.
NOTE TO SELF: If the year is 2013, you may have better luck using the MATCH statement. I'm sure I'll be dead by then, so your on your own. Check with the MySQL documentation.
Example:
SELECT * FROM music WHERE title RLIKE ('you') OR title RLIKE ('mustard') ;
Returns:

id artist album title track year

6 the beatles Abbey Road I Want You (She's So Heavy) 6 1969

9 the beatles Abbey Road You Never Give Me Your Money 9 1969

20 beatles Rubber Soul You Won't See Me 3 1969

27 beatles Rubber Soul I'm Looking Through You 10 1969

11 the beatles Abbey Road Mean Mr. Mustard 11 1969

SELECT * FROM table_name WHERE col_A_name RLIKE ('search_termA') AND col_B_name RLIKE ('search_termB') ;
This example will search through your table for matching words.
Will return all rows that contain either search_termA OR search_term_B -- the search terms can be a single word or a phrase.

Example:
SELECT * FROM music WHERE title RLIKE ('you') AND title RLIKE ('me') ;
Returns:

id artist album title track year

9 the beatles Abbey Road You Never Give Me Your Money 9 1969

20 beatles Rubber Soul You Won't See Me 3 1969

NOTE: In this example, we are only searching within the same column, you can search different columns if you want, and you can also add more OR or AND statements, or you can mix and match OR and AND statements to refine the search..
Example:
SELECT * FROM music WHERE title RLIKE ('you') OR artist RLIKE ('the') AND track RLIKE ('1969');
SELECT * FROM table_name WHERE 1 ORDER BY RAND() LIMIT number_of_rows_to_return ;
Will select random rows and limit the number of rows returned to the number specified in the LIMIT section.
Example:
SELECT * FROM music WHERE 1 ORDER BY RAND() LIMIT 3;
Returns something similar to :
(Can't be too sure here, since MySQL is picking rows randomly, but you can see that only three rows are returned :)

id artist album title track year

25 beatles Rubber Soul What Goes On 8 1969

13 the beatles Abbey Road She Came in Through the Bathroom Window 13 1969

5 the beatles Abbey Road Octopus's Garden 5 1969

SELECT DISTINCT col_A FROM table_name WHERE 1;
Will only return rows that have unique data in the column specified.
Perhaps the best way to explain this is through example.
If we do something like this:
SELECT DISTINCT year FROM music WHERE 1;
... we will only get one row and one column because all the rows in our table have "1969" set as the year. MySQL will pick the first unique row, all subsequent rows that have the same data in the specified column will not be returned, because the data in the other rows is not unique.

year

1969

If we do something like:
SELECT DISTINCT artist FROM music WHERE 1;
We'll only get two rows:

artist

the beatles

beatles

... because the for the first album, we used "the beatles" whereas the second album, we just used "beatles" -- so only the first row that MySQL encounters with unique data in the "artist" column will be returned... because the rest of the rows are not unique.

SELECT DISTINCT col_A,col_B FROM table_name WHERE 1 ORDER BY RAND() LIMIT number_of_rows_to_return ;
You can add the DISTINCT statement to any statement. So that only unique items are returned. Or another way to think about it is that no duplicate rows are returned. For example, if you used the RLIKE statement to look for a couple key words, MySQL may return the dame row two times, you can use the DISTINCT statement to filter only unique rows.
Example:
SELECT DISTINCT artist,album FROM music WHERE 1 ORDER BY RAND() LIMIT 5;
This example is deviating slightly from out standard "All beatles" table, in this example, we'll have to imaging that our tables contains a whole bunch of artists and albums.
So, with your imagination flying, the example above would return 5 rows, where both the artist and album are unique.

artist album

beatles Rubber Soul

the beatles Abbey Road

Ray Lamontagne Trouble

The Tragically Hip Wheat kings

The Police Outlandos d'Amour

NOTE: Yes, both "the beatles" and "beatles" albums would be returned because the entire data contained in the "artist" column is unique for both of these albums.
SELECT MAX(col_A) FROM table_name ;
Will return the highest value for that column. Usually you want to sue this on a column that references a number. For example, let's say you wanted to find the highest ID on your table so that you could determine the next ID to use fro a new item that you are inserting into your tables. You could use the MAX statement to find the largest ID number, then when you INSERT your new item, you can simply bump up the number by one.
Example:
SELECT MAX(id) FROM music;
Returns:

id

31

... because the highest ID in our table is 31. e.g. there are 31 items in our table as defined by the ID column.
If we ran something like:
SELECT MAX(track) FROM music ;
... we'd get:

track

17

... because 17 is the highest number in the "track" column

DELETE STATEMENTS

DELETE FROM table_name WHERE column_name='search_data';
Will remove rows that match the search data defined in the WHERE section
Example:
DELETE FROM music WHERE artist='the beatles';
Will remove all rows that have "the beatles" defined in the "artist" column.
DELETE FROM table_name WHERE column_name IN ('search_data_A', 'search_data_B');
Will remove all rows that match the search data defined in the IN section.
Example:
DELETE FROM music WHERE artist IN ('the beatles', 'beatles');
... will remove all the items from our example table

How to copy export mySQL user priviledges

2011-03-28T17:19:00.000-07:00

Copy/Export MySQL User Priviledges

I’m often asked how to copy or export MySQL Users from one machine to another. The following SQL query will show your users:

SELECT DISTINCT CONCAT (‘show grants for `’, user, ‘`@`’, host, ‘`;’) AS query FROM mysql.user;

In my case on my test server, this shows:

SHOW GRANTS FOR ‘root’@’127.0.0.1′;
SHOW GRANTS FOR ‘debian-sys-maint’@'localhost’;
SHOW GRANTS FOR ‘root’@'localhost’;

Now, I’ll need to execute each one of these as separate statements. The output of SHOW GRANTS FOR ‘root’@'localhost’; is:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@'localhost’ IDENTIFIED BY PASSWORD ‘*XXX…XXX’ WITH GRANT OPTION;

Copy and paste each ‘GRANT’ statement to your new SQL server, with the hashed password intact and you should be ready to go.

Lowercase and Uppercase in Perl

2011-03-20T18:16:00.000-07:00

Lowercase and Uppercase in Perl

All the programming languages has functions/methods to convert case to upper or lowercase.
Perl has lc for lowercase, uc for uppercase, ucfirst for first character to be converted to uppercase. etc...
Let see some examples :
#!/usr/bin/perl -w
$name = "firat serkan atagun";
$ucname= uc ($name);
$lcname = lc ($name);
$ucfname = ucfirst ($name);
print $name."\n".$ucname."\n".$lcname."\n".$ucfname;

The output will be :
firat serkan atagun
firat serkan atagun
FIRAT SERKAN ATAGUN
Firat serkan atagun

uc function is used for converting strings in to uppercase.
lc function is used for converting strings in to lowercase
ucfirst function is used for converting the first chracter of the string to uppercase.

Using the Perl rand() function

2011-03-15T18:01:00.000-07:00

Using the Perl rand() function

Introduction

The rand() function is used to generate random numbers. By default it generates a number between 0 and 1, however you can pass it a maximum and it will generate numbers between 0 and that number.

Example 1. Between 0 and 1

To generate a random decimal number between 0 and 1, use rand() without any parameters.

#!/usr/bin/perl
  use strict;
  use warnings;

  my $random_number = rand();

  print $random_number . "\n";

This will give you a random number, like:

0.521563085335405

Example 2. A bigger range of numbers

Quite often you don't want a number between 0 and 1, but you want a bigger range of numbers. If you pass rand() a maximum, it will return a decimal number between 0 and that number. Our example below generates a random number between 0 and 100.

#!/usr/bin/perl
  use strict;
  use warnings;

  my $range = 100;

  my $random_number = rand($range);

  print $random_number . "\n";

The program will produce something like:

34.0500569277541

Example 3. A random integer

To generate a random integer, convert the output from rand to an integer, as follows:

#!/usr/bin/perl
  use strict;
  use warnings;

  my $range = 100;

  my $random_number = int(rand($range));

  print $random_number . "\n";

This program gives you an integer from 0 to 99 inclusive:

Example 4. With an offset

To generate a random number between, for example, 100 and 150, simply work out the range and add the minimum value to your random number.

#!/usr/bin/perl
  use strict;
  use warnings;

  my $range = 50;
  my $minimum = 100;

  my $random_number = int(rand($range)) + $minimum;

  print $random_number . "\n";

How to connect in Perl to MySQL database

2011-03-15T17:54:00.000-07:00

How to connect in Perl to MySQL database

#!/usr/bin/perl

# PERL MODULE
use Mysql;

# MYSQL CONFIG VARIABLES
# MYSQL CONFIG VARIABLES
$host = "localhost";
$database = "store";
$tablename = "inventory";
$user = "username";
$pw = "password";

# PERL CONNECT()
$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB
$connect->selectdb($database);

# LISTTABLES()
@tables = $db->listtables;

# PRINT EACH TABLE NAME
@tables = $connect->listtables;
foreach $table (@tables) {
print "$table
";
}

# DEFINE A MySQL QUERY
$sqlstr = "SELECT * FROM $tablename";
$sqlstr="select id, captcha_question from captcha where id='$captch_id' LIMIT 1";

# EXECUTE THE QUERY
$execute = $connect->query($sqlstr);

$rownumber = $execute->numrows();
$fieldnumber = $execute->numfields();

# PRINT THE RESULTS
print $rownumber."
";
print $fieldnumber."
";

# HTML TABLE
print "
"; # FETCHROW ARRAY while (@results = $execute->fetchrow()) { print " "; } print "

id	product	quantity
" .$results[0]."	" .$results[1]."	" .$results[2]."

Advanced email in PHP

2011-02-21T21:32:00.001-08:00

Advanced email in PHP

About the Author

Kevin Yank

Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode ofMacGyver.

View all articles by Kevin Yank...

By: Kevin Yank

February 13th, 2002

Reader Rating: 9

I wish I could remember the very first email message I ever sent. Unfortunately, the truth is that I've come to take email for granted; for instance, I easily send more email messages than I make phone calls. Because the novelty has long since worn off, the memory of that first email is as lost to me as my first phone call; but I doubt my sense of wonder was any less complete at the time. To someone who has never seen email in action before, that first message can be magical.

In this article, I'll try to recapture some of that magic for those of you who have never created a Web site that sends email messages. We'll see how the PHP server-side scripting language may be set up to send email, and explore how to send complex message types such as HTML email or emails with file attachments.

Note: This article was written back when things like HTML email and file attachments were a lot more difficult to do in PHP than they are today. Before you dive into the from-scratch solutions presented in this article, you might consider investigating PHPMailer [1], a free library for PHP that provides all of these features with minimal hair-pulling and gnashing of teeth.

PHP Email Setup

Before we can send email with PHP, we need to set it up to do so, just as you need to set up your email program before it can send messages. Configuration for sending email in PHP is done with the php.ini file, so open up your Web server's php.ini in whichever editor you normally use.

If you don't run your own server, but instead have a PHP-equipped Web host, you can safely assume that everything in this section has been done for you, and skip ahead.

In the section entitled [mail function] in the php.ini file, you'll find three settings: SMTP, sendmail_from, and sendmail_path. If your server runs on a Windows machine, you'll want to set the SMTP option to point to your SMTP server (or your ISP's SMTP server, if you're setting up PHP on your home machine). If instead you're setting up PHP on a Linux (or other Unix-based OS) server, you'll want to set the sendmail_path option to point to the sendmail program on your server, passing it the -t option. You can use the SMTP option in Linux instead if you don't have sendmail set up.

In either case, you'll want to set the sendmail_from option to your email address, or whichever address you'd like to appear as the default 'from' address for emails sent from PHP scripts.

Here's how the section might look on a typical Windows server, or on a Linux server without sendmail:

[mail function] 

; Setup for Windows systems 

SMTP = smtp.my.isp.net 

sendmail_from = me@myserver.com

And here's how it might look on a Linux server with sendmail:

[mail function] 

; Setup for Linux systems 

sendmail_path = /usr/sbin/sendmail -t 

sendmail_from = me@myserver.com

With those settings in place, restart your Web server and you're ready to go!

Sending Plain Email

It doesn't come much easier than the procedure to send plain text email in PHP. In fact, you can do it in just one line in a PHP script:

mail('recipient@some.net', 'Subject', 'Your message here.'); 

?>

The above line would send an email message to recipient@some.net with 'Subject' as the subject line and 'Your message here.' as the message body. As you can see, PHP's mail function makes sending email exceedingly simple, but there are a few advanced tricks we can use to get more out of this simple function.

First of all, if the mail system you configured in your php.ini file rejects a message you try to send (for example, if the 'to' address is not a valid email address), this function will display an error message in the user's browser. Like most PHP functions, however, error messages may be suppressed by preceding the function name with an @ symbol. Combine this with the fact that the mail function returns either true or false depending on whether the email was accepted by the mail sending system, and you have a formula to send email with appropriate error checking:

if (@mail($to, $subject, $message)) { 

 echo('

Mail sent successfully.
');
} else {
echo('Mail could not be sent.
');
}
?>

Note that just because an email message could be sent doesn't guarantee it will arrive at its destination. An email address can be valid (i.e. correctly formed) but not actually exist. For instance, you can successfully send a message to nonexistant.user@hotmail.com -- that is, the mail function will return true -- but the message will bounce because no such user exists. PHP provides no built-in means of detecting when this happens.

When you want to send a message to multiple recipients, you can just list their email addresses one after another, separated by commas, in the first parameter. For example:

mail('recipient1@some.net, recipient2@some.net', 

    'An email to two people', 'Message goes here.'); 

?>

That about covers the basics of the mail function; now let's really get fancy and explore mail headers and what we can do with them!

HTML Email and Other Headers

So now you can send email from your PHP scripts. How exciting! Although I'm sure you're already drunk with power, would you like to know how to send HTML email too? Of course you would!

To understand HTML email, first you need to understand mail headers. Any given email message is made up of two parts: the headers and the message body. Here's how a simple message looks when your email program fetches it from your ISP:

Return-Path:    

Delivered-To: you@some.net   

Received: ...several lines like this...   

From: Sender    

To: You    

Subject: A Simple Message   

Date: Mon, 11 Feb 2002 16:08:19 -0500   

Organization: Sender's Company   

X-Mailer: Microsoft Outlook, Build 10.0.2616   

  

Hi there!   Is this thing on?

Everything up to the blank line makes up the headers for this message. In actual fact, most email messages will have many more header lines than this; however, to keep our focus I trimmed this example down to the bare essentials.

As you can see, every line of the headers begins with the name of the header (From:, To:, Subject:, Date:, etc.), followed by some value. Most headers are standardized, and have a specific meaning, either to your mail program or to the mail servers that were responsible for getting the message to you. Non-standard headers exist as well, and they all begin with X- (e.g. X-Mailer:, a non-standard header, often appears to indicate the program that was used to send the message).

NOTE: If a header's value needs more than one line, additional lines should begin with a space. We'll see an example of this in the next section.

As soon as your mail program gets to a blank line, it knows the headers are over and the rest of the email is the message body, which it should display. In this case, the message body is the last line above.

PHP's mail function lets you specify your own headers, which it adds to those it generates automatically to get your message where it needs to go. For example, the following call to the mail function will add an X-Mailer: header to the outgoing message, identifying PHP 4.x as the sending program:



mail('recipient@some.net', 'Subject', 'Your message here.',   

    'X-Mailer: PHP 4.x');   

?>

This optional fourth parameter is most often used to specify a 'from' address other than the default specified in php.ini. Let's add a From: header to do just that:



mail('recipient@some.net', 'Subject', 'Your message here.',   

    "From: sender@some.net\nX-Mailer: PHP 4.x");   

?>

Note that since headers each appear on a single line, we must separate our two headers with a new line character (\n), which means I need to use double quotes around the header string (single quoted strings don't recognize special character codes like \n).

Additional headers also let you assign names to email addresses by specifying them in the format name . Here's our example again, but this time with names "The Sender" and "The Receiver" attached to the relevant addresses:



mail('recipient@some.net', 'Subject', 'Your message here.',   

    "To: The Receiver \n" .   

    "From: The Sender \n" .   

    "X-Mailer: PHP 4.x");   

?>

Notice that to do this, we've had to specify the To: header manually, since the first parameter of PHP's mail function doesn't support this more advanced address format. We must still list all the recipients of the message as bare email addresses in the first parameter, however.

The cc: and Bcc: headers provide the ability to send carbon copies and blind carbon copies of a message as well:



mail('recipient@some.net, someone@some.net, metoo@some.net',   

    'Subject', 'Your message here.',   

    "To: The Receiver \n" .   

    "From: The Sender \n" .   

    "cc: Interested \n" .   

    "Bcc: Me Too \n" .   

    "X-Mailer: PHP 4.x");   

?>

See how the email addresses of all recipients, be they actual addressees (To), carbon copies (Cc), or blind carbon copies (Bcc) are listed in the first parameter? This isn't documented anywhere, but in my testing I've found that it's absolutely vital if you want the message to get to everyone it's supposed to, especially on Windows servers where PHP's mail function is especially sensitive.

BUG ALERT: There are two apparent bugs in the PHP mail function, which I've observed as recently as PHP 4.1.0, that you must be aware of. First, the Cc:header must be typed 'cc:' (as above) or 'CC:', that is, in either all-caps or low-caps. The mixed case version (Cc:) should work, but it doesn't. Secondly, on Windows servers, the Bcc: header does not work as it should. Bcc: recipients do get a copy of the message, but the Bcc: headers are not stripped out of the message during sending, so recipients can see the Bcc: receivers by looking at those lines in the headers.

What does all this have to do with HTML email, you ask? Well, a few special headers will cause the message body to be interpreted as HTML by email clients that support it:



mail('recipient@some.net', 'Subject',   

    '

Your message here.
',
"To: The Receiver \n" .
"From: The Sender \n" .
"MIME-Version: 1.0\n" .
"Content-type: text/html; charset=iso-8859-1");
?>

Note both the message in HTML format as well as the two new headers: Mime-Version: and Content-type:.

The MIME-Version: header indicates that the standard extended mail format will be used (MIME stands for Multipurpose Internet Mail Extensions), which allows for messages to be sent content types other than plain text. The Content-type: header then specifies that the message with contain HTML (and sets the character set to the standard for that format).

Mixed Messages

In life, it's rarely good to send mixed messages. At least, that's what my last girlfriend told me! When it comes to email, however, mixed messages offer a whole lot of power. A single email message can contain both text and HTML versions of your message. That makes it viewable in most any email program, and you don't sacrifice the power of HTML formatting for readers that are appropriately equipped.

Be aware that mixed messages have their weaknesses. First of all, since you're sending two versions of your message, the message will typically be a lot larger than it would be if you sent just one format or the other. Also note that old email programs that don't recognize the mixed message format may display both versions of the message as file attachments (one text, the other HTML).

Let's look at a simple example of a mixed email message, and then look at the PHP code to send it:

Date: Mon, 11 Feb 2002 16:08:19 -0500   

To: The Receiver    

From: The Sender    

Subject: A simple mixed message   

MIME-Version: 1.0   

Content-Type: multipart/alternative;   

             boundary="==Multipart_Boundary_xc75j85x"   

  

This is a multi-part message in MIME format.   

  

--==Multipart_Boundary_xc75j85x   

Content-Type: text/plain; charset="iso-8859-1"   

Content-Transfer-Encoding: 7bit   

  

This is the text portion of the mixed message.   

  

--==Multipart_Boundary_xc75j85x   

Content-Type: text/html; charset="iso-8859-1"   

Content-Transfer-Encoding: 7bit

This is the HTML portion of the mixed message.

--==Multipart_Boundary_xc75j85x--

After the initial, standard headers at the top of the message, we have the MIME-Version: 1.0 header that enables the advanced email features we need. The Content-Type: header is where things start to get funky:

Content-Type: multipart/alternative;   

             boundary="==Multipart_Boundary_xxc75885"

We specify a content type of multipart/alternative, which is the special type that allows us to send a message with two or more alternative versions of the message (from which the recipient's email program will pick the most suitable for display). In addition, we use the Content-Type header to set a boundary string.

To keep the header lines short, this part of the header appears on a second line (as mentioned in a note above, the second line must begin with one or more spaces to indicate that it's a continuation of the previous line).

In this case, I chose "==Multipart_Boundary_xc75j85x" as the boundary string. There is no special significance to this string, other than it is unlikely to appear as part of the message itself. I used characters like equals signs and underscores, and semi-random strings of letters and numbers to help ensure this. We then use this string to divide up our message into parts.

The text "This is a multi-part message in MIME format." is included for the benefit of older mail programs, so that the user has some idea of why the email may not appear quite as expected. With that disclaimer out of the way, we use our boundary to mark the start of the first part of our message:

--==Multipart_Boundary_xc75j85x   

Content-Type: text/plain; charset="iso-8859-1"   

Content-Transfer-Encoding: 7bit   

  

This is the text portion of the mixed message.

Notice that we add two dashes (--) to the beginning of the boundary string when we actually use it.

After the first boundary, we begin the text version of the message. Each part of the message begins with a couple of headers to indicate its content type and encoding. In the case of the text part, the content type is text/plain (with the standard character set, iso-8859-1), and the encoding is 7bit (plain ASCII text). A blank line marks the end of the headers, which are followed by the message body.

The HTML version then follows:

--==Multipart_Boundary_xc75j85x   

Content-Type: text/html; charset="iso-8859-1"   

Content-Transfer-Encoding: 7bit

This is the HTML portion of the mixed message.

The headers are almost identical to the text part, but this time we specify text/html as the content type. After the body of the HTML document, all that remains is the closing boundary, to which we add an extra two dashes on the end to mark the end of the message:

--==Multipart_Boundary_xc75j85x--

As you can see, mixed messages may look complicated, but they're actually pretty simple when you take a closer look. The only tricky part from the standpoint of a PHP developer who wants to send a message like this is the task of generating a boundary string.

Here's how I like to do it:

$semi_rand = md5(time());   

$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";   

?>

We take the current Unix timestamp (the number of seconds since January 1, 1970), as given by time(), and use the MD5 algorithm to convert it to a semi-random string. This string is then used to make up part of the boundary string. Feel free to use whatever method you like to generate your boundary strings.

With all this in mind, you should be well-equipped to generate mixed messages in PHP. Since mixed messages are relatively uncommon, I'll leave the actual code for you to write as an exercise. This business of splitting up messages into parts with a boundary string is important, however, when it comes to file attachments -- the subject of our final section.

File Attachments

File attachments work just like mixed email, except that a different content type is used for the message as a whole (multipart/mixed instead ofmultipart/alternative), and there's a new Content-Disposition header that tells the email client how to handle each part of the message.

Let's write a PHP script that processes a form submission that contains an email message to be sent, possibly with a file attachment, and sends it out. I'll talk you through it line by line so that by the end you'll not only have a useful snippet of PHP code, but also an understanding of how file attachments work. You can download the script [2](and the form for it) if you want to try it out for yourself.

First, we grab the submitted values and place them in PHP variables. Most people have their servers set up to create global variables for submitted values automatically, but as of PHP 4.1 this is no longer the default, so we do it by hand just in case. Since we want to accept file attachments, it's safe to assume that the form will be submitted with a POST request:



// Read POST request params into global vars     

$to      = $_POST['to'];     

$from    = $_POST['from'];     

$subject = $_POST['subject'];     

$message = $_POST['message'];

File uploads in PHP 4.1 are placed in a special $_FILES array, so we fetch the values we need out of it:

// Obtain file upload vars     

$fileatt      = $_FILES['fileatt']['tmp_name'];     

$fileatt_type = $_FILES['fileatt']['type'];     

$fileatt_name = $_FILES['fileatt']['name'];

For the sake of brevity, we'll assume that the required parameters ($to and $from) now have valid values (email addresses) in them. Normally we would check their format with regular expressions.

Next, we use the $from value to begin building the extra headers for the email:

$headers = "From: $from";

Next we check the $fileatt variable, which may or may not contain the path and filename to an uploaded file attachment. We use PHP's is_uploaded_file function to find out:

if (is_uploaded_file($fileatt)) {     

 // Read the file to be attached ('rb' = read binary)     

 $file = fopen($fileatt,'rb');     

 $data = fread($file,filesize($fileatt));     

 fclose($file);

Having read in the data for the file attachment, we need to set up the message headers to send a multipart/mixed message:

 // Generate a boundary string     

 $semi_rand = md5(time());     

 $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";     

      

 // Add the headers for a file attachment     

 $headers .= "\nMIME-Version: 1.0\n" .     

             "Content-Type: multipart/mixed;\n" .     

             " boundary=\"{$mime_boundary}\"";

Now for the message body itself. This works just as we saw for the text part of a mixed message in the previous section:

 // Add a multipart boundary above the plain message     

 $message = "This is a multi-part message in MIME format.\n\n" .     

            "--{$mime_boundary}\n" .     

            "Content-Type: text/plain; charset=\"iso-8859-1\"\n" .     

            "Content-Transfer-Encoding: 7bit\n\n" .     

            $message . "\n\n";

Now, to allow for binary file types, we need to use Base64 encoding to convert the (possibly binary) file attachment data to a text-only format suitable for sending by email. All email programs in popular use support Base64 encoding of file attachments, so this is the best way to go. Fortunately, PHP provides a function for Base64 encoding:

 // Base64 encode the file data     

 $data = chunk_split(base64_encode($data));

We now have everything we need to write the portion of the message that contains the file attachment. Here's the code:

 // Add file attachment to the message     

 $message .= "--{$mime_boundary}\n" .     

             "Content-Type: {$fileatt_type};\n" .     

             " name=\"{$fileatt_name}\"\n" .     

             "Content-Disposition: attachment;\n" .     

             " filename=\"{$fileatt_name}\"\n" .     

             "Content-Transfer-Encoding: base64\n\n" .     

             $data . "\n\n" .     

             "--{$mime_boundary}--\n";     

}

That completes the modifications necessary to accommodate a file attachment. We can now send the message with a quick call to mail:

// Send the message     

$ok = @mail($to, $subject, $message, $headers);     

if ($ok) {     

 echo "

Mail sent! Yay PHP!
";
} else {
echo "Mail could not be sent. Sorry!
";
}
?>

And that's how we send emails with file attachments in PHP!

Summary

In this article, you probably learned a lot more about email than you ever wanted to know. That intimate knowledge about what makes email tick allowed us to do some pretty special things with PHP's deceptively simple mail function.

we learned how to manipulate email headers to play with the recipients of an email
we saw the headers that permit you to send HTML email
we combined the HTML and text versions of an email into a single, mixed message

and finally...

we brought it all together with a couple of new tricks to send email messages with file attachments.

If you're in the mood for a challenge, try expanding the file attachment script in this article to allow for multiple file attachments, or to allow for an HTML message. If you're an object oriented PHP type, try developing a class that encapsulates the functionality we explored in this article (there are a number of such scripts available on the Internet if you're after something ready-made).

If you're really interested, check out the RFC for MIME extensions (RFC 2045 [3]) to discover everything that email is capable of, then go wild!

[1] http://phpmailer.sourceforge.net/
[2] http://www.webmasterbase.com/examples/phpemail/phpemail.zip
[3] http://www.ietf.org/rfc/rfc2045.txt

MySQL Security Checklist

2011-02-20T15:48:00.000-08:00

MySQL Security Checklist

Background
   1.Read the MySQL security guidelines at http://dev.mysql.com/doc/mysql/en/Security.html.
   2.Visit http://www.mysql.com/products/mysql/ often, and check for updates.
   3.Know
      your bugs! Check vulnerability databases such as SecurityFocus and ICAT
      regularly for MySQL bugs, and (if you can stand the noise levels)
      subscribe to security mailing lists such as VulnWatch, BugTraq, and the
      MySQL mailing lists.
Operating System
   1.Deploy IPTables (Linux), an IPSec filtering ruleset (Windows), or some other host-based firewall software on your MySQL servers.
   2.Use a low-privileged mysql account to run the MySQL daemon. This is the default on some platforms, but not others.
   3.Run mysqld with the --chroot option.
   4.Ensure that the MySQL user cannot access files
      outside of a limited set of directories. Specifically, the MySQL user
      should be prohibited from reading operating system configuration files.
      In some cases you might want to prevent the MySQL user from being able
      to modify the MySQL configuration files.
   5.Ensure that MySQL data files (normally residing
      beneath the MySQL data directory) cannot be read by users other than
      the root or Administrator account, and the account that MySQL is
      running under.
   6.Plaintext credentials. Ensure that no user other
      than the MySQL user can read any MySQL configuration and log files. The
      files my.cnf, my.ini, and master.info commonly have plaintext usernames
      and passwords in them, and the query log file (if present) is likely to
      contain passwords.
   7.Turn off unnecessary services or daemons.
   8.Make sure you don't have anything in your .mysql_history file.
MySQL Users
   1.Set a "strong" password for the root@localhost account.
   2.Remove all non-root MySQL users.
   3.Rename the root MySQL user to something obscure.
   4.If remote connections are enabled, specify REQUIRE SSL in the GRANT statement used to set up the user.
   5.Create a MySQL user for each web application—or
      possibly for each role within each web application. For instance, you
      might have one MySQL user that you use to update tables, and another,
      lower-privileged user that you use to "select" from tables.
   6.Ensure that MySQL users are restricted by IP
      address as well as passwords. See section 5.4 of the MySQL manual, "The
      MySQL Access Privilege System," for more information.
   7.Don't give accounts privileges that they don't absolutely need, especially File_priv, Grant_priv, and Super_priv.
   8.Never give anyone (other than root or whatever you call your root account) access to the mysql.user table.
MySQL Configuration
   1.Enable logging via the --log option.
   2.Disable the LOAD DATA LOCAL INFILE command by adding set-variable=local-infile=0 to the my.cnf file.
   3.Remove any unused UDFs
   4.If you're using only local connections, disable TCP/IP connections via the --skip-networking option.
   5.Depending on your operating system, and how your
      data directory is configured, you might want to disallow the use of
      symbolic links via the skip-symbolic-links option.
   6.Remove the default test database.
   7.Ensure MySQL traffic is encrypted.
Routine Audit
   1.Check your logs.
   2.Enumerate users and use the "show grants" statement regularly to see what privileges are granted to which users.
   3.Periodically do a quick check on password hashes.

How to upload multiple files into mySLQ

2011-01-20T21:15:00.000-08:00

By using this script you will be able to upload as many files as you want.
The code will check if file existes, limited extensions, file size, file selected ..etc.
For Q. E-mail/MSN: m(at)maaking.com.

###########################################
#----------Upload Multiple Files----------#
#----------Multi-files Uploader-----------#
#-------------Multi-Uploader -------------#
###########################################
/*=========================================\
Author : Mohammed Ahmed(M@@king) \\
Version : 1.0 \\
Date Created: Aug 20 2005 \\
---------------------------- \\
Last Update: Aug 31 2005 \\
---------------------------- \\
Country : Palestine \\
City : Gaza \\
E-mail : m@maaking.com \\
MSN : m@maaking.com \\
AOL-IM : maa2pal \\
WWW : http://www.maaking.com \\
Mobile/SMS : 00972-599-622235 \\
\\
===========================================\
------------------------------------------*/
//upload directory.
//change to fit your need eg. files, upload .... etc. $upload_dir = "images/"; //number of files to upload. $num_files = 5; //the file size in bytes. $size_bytes =51200; //51200 bytes = 50KB.
//Extensions you want files uploaded limited to. $limitedext = array(".gif",".jpg",".jpeg",".png",".txt",".nfo",".doc",".rtf",".htm",".dmg",".zip",".rar",".gz",".exe");

//check if the directory exists or not.
if (!is_dir("$upload_dir")) {
die ("Error: The directory ($upload_dir) doesn't exist");
}
//check if the directory is writable.
if (!is_writeable("$upload_dir")){
die ("Error: The directory ($upload_dir) is NOT writable, Please CHMOD (777)");
}

//if the form has been submitted, then do the upload process
//infact, if you clicked on (Upload Now!) button. if (isset($_POST['upload_form'])){

echo "

Upload results:

";

//do a loop for uploading files based on ($num_files) number of files.
for ($i = 1; $i <= $num_files; $i++) {

//define variables to hold the values.
$new_file = $_FILES['file'.$i];
$file_name = $new_file['name'];
//to remove spaces from file name we have to replace it with "_".
$file_name = str_replace(' ', '_', $file_name);
$file_tmp = $new_file['tmp_name'];
$file_size = $new_file['size'];

#-----------------------------------------------------------#
# this code will check if the files was selected or not. #
#-----------------------------------------------------------#

if (!is_uploaded_file($file_tmp)) {
//print error message and file number.
echo "File $i: Not selected.
";
}else{
#-----------------------------------------------------------#
# this code will check file extension #
#-----------------------------------------------------------#

$ext = strrchr($file_name,'.');
if (!in_array(strtolower($ext),$limitedext)) {
echo "File $i: ($file_name) Wrong file extension.
";
}else{
#-----------------------------------------------------------#
# this code will check file size is correct #
#-----------------------------------------------------------#

if ($file_size > $size_bytes){
echo "File $i: ($file_name) Faild to upload. File must be ". $size_bytes / 1024 ." KB.
";
}else{
#-----------------------------------------------------------#
# this code check if file is Already EXISTS. #
#-----------------------------------------------------------#

if(file_exists($upload_dir.$file_name)){
echo "File $i: ($file_name) already exists.
";
}else{
#-----------------------------------------------------------#
# this function will upload the files. :) ;) cool #
#-----------------------------------------------------------#
if (move_uploaded_file($file_tmp,$upload_dir.$file_name)) {
echo "File $i: ($file_name) Uploaded.
";
}else{
echo "File $i: Faild to upload.
";
}#end of (move_uploaded_file).

}#end of (file_exists).

}#end of (file_size).

}#end of (limitedext).

}#end of (!is_uploaded_file).

}#end of (for loop).
# print back button.
echo "»back"; ////////////////////////////////////////////////////////////////////////////////
//else if the form didn't submitted then show it. }else{
echo "

Select files to upload!.

Max file size = ". $size_bytes / 1024 ." KB";
echo " ;
// show the file input field based on($num_files).
for ($i = 1; $i <= $num_files; $i++) {
echo "File $i: . $i ."\">
";
}
echo "

";
}
//print copyright ;-) echo"

Script by: maaking.com

"; ?>

How to validate the upload file is excel or not in the client side

2010-02-07T16:27:00.000-08:00

onBlur evernt use this code.

onblur="if(this.value.lastIndexOf(\'.xls\')==-1){ alert(\'Please upload only .xls extension file\'); return false;}"

for PDF file

onblur="if(this.value.lastIndexOf(\'.pdf\')==-1){ alert(\'Please upload only .xls extension file\'); return false;}"

How to prevent user from changing textfield value in html without disabling the text field.

2009-12-09T12:26:00.000-08:00

How to prevent user from changing textfield value in html without disabling the text field.

to do that, I used the onfocus="javascript:this.blur()"

<input type="text" name="focus" value="You can not change me. try it" onfocus="javascript:this.blur();" size="100"/&gt

How to replace a space with an underscore in php

2009-11-30T15:47:00.000-08:00

How to replace a space with an underscore in php

To do that, I used str_replace function in php.

Example.

$name = $_POST[name];

$pagelink = str_replace(" ","_",$name);

?>

Uploading Files with PHP

2009-11-26T16:49:00.001-08:00

Uploading Files with PHP

The HTML Form
This script will allow you to upload files from your browser to your hosting, using PHP. The first thing we need to do is create an HTML form that allows people to choose the file they want to upload.

This form sends data to the file "upload.php", which is what we will be creating next to actually upload the file.

Uploading the File

The actual file upload is very simple:

This very small piece of code will upload files sent to it by your HTML form.

1. The first line $target = "upload/"; is where we assign the folder that files will be uploaded to. As you can see in the second line, this folder is relative to the upload.php file. So for example, if your file was at www.yours.com/files/upload.php then it would upload files to www.yours.com/files/upload/yourfile.gif. Be sure you remember to create this folder!

2. We are not using $ok=1; at the moment but we will later in the tutorial.

3. We then move the uploaded file to where it belongs using move_uploaded_file (). This places it in the directory we specified at the beginning of our script. If this fails the user is given an error message, otherwise they are told that the file has been uploaded.

Limit the File Size
if ($uploaded_size > 350000)
{
echo "Your file is too large.
";
$ok=0;
}

Assuming that you didn't change the form field in our HTML form (so it is still named uploaded), this will check to see the size of the file. If the file is larger than 350k, they are given a file too large error, and we set $ok to equal 0.

You can change this line to be a larger or smaller size if you wish by changing 350000 to a different number. Or if you don't care about file size, just leave these lines out.

Limit Files by Type

if ($uploaded_type =="text/php")
{
echo "No PHP files
";
$ok=0;
}

The code above checks to be sure the user is not uploading a PHP file to your site. If they do upload a PHP file, they are given an error, and $ok is set to 0.

if (!($uploaded_type=="image/gif")) {
echo "You may only upload GIF files.
";
$ok=0;
}

In our second example we only allow users to upload .gif files, and all other types are given an error before setting $ok to 0. You can use these basic examples to allow or deny any specific file types.

Putting It Together

350000)
{
echo "Your file is too large.
";
$ok=0;
}

//This is our limit file type condition
if ($uploaded_type =="text/php")
{
echo "No PHP files
";
$ok=0;
}

//Here we check that $ok was not set to 0 by an error
if ($ok==0)
{
Echo "Sorry your file was not uploaded";
}

//If everything is ok we try to upload it
else
{
if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target))
{
echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded";
}
else
{
echo "Sorry, there was a problem uploading your file.";
}
}
?>

id	artist	album	title	track	year
1	the beatles	Abbey Road

artist	album
beatles	Rubber Soul
the beatles	Abbey Road
Ray Lamontagne	Trouble
The Tragically Hip	Wheat kings
The Police	Outlandos d'Amour

php and mySQL

MySQL FULLTEXT Searching

How it Works

Creating the tables

Querying the Data

Boolean Searches

Restrictions

Altering FULLTEXT's Default Behavior

Having fun with MySQL FULLTEXT Searching

PHP

ispell

Looking towards the Future

Conclusion

MySQL Commands List

MySQL Commands List

How to copy export mySQL user priviledges

Copy/Export MySQL User Priviledges

Lowercase and Uppercase in Perl

Using the Perl rand() function

Using the Perl rand() function

Introduction

Example 1. Between 0 and 1

Example 2. A bigger range of numbers

Example 3. A random integer

Example 4. With an offset

How to connect in Perl to MySQL database

Advanced email in PHP

Advanced email in PHP

About the Author

Kevin Yank

PHP Email Setup

Sending Plain Email

HTML Email and Other Headers

Mixed Messages

File Attachments

Summary

MySQL Security Checklist

How to upload multiple files into mySLQ

Upload results:

Select files to upload!.

How to validate the upload file is excel or not in the client side

How to prevent user from changing textfield value in html without disabling the text field.

How to replace a space with an underscore in php

Uploading Files with PHP

Altering `FULLTEXT`'s Default Behavior

Having fun with MySQL `FULLTEXT` Searching

`ispell`