Shantanu's Blog

You can call the shell script from PHP page and also check if it was run successfully.

?php
passthru('xfer_rsync.sh',$returnvalue);

if ($returnvalue != 0){
//we have a problem!
//add error code here
}else{
//we are okay
//redirect to some other page
}
?


Read More

Labels: php, shell script

Curl is a command line tool for doing all sorts of interesting and essential URL manipulations and data transfers.

// Read a plain URL
curl http://www.google.com

// Get a web page and store it in a file
curl -o savedpage.html http://www.example.com/index.php

// download all the pages from page no 1 to page no 12 and save it to a corresponding file
curl -o pages#1.html http://example.com/pages.php?pageNo=[1-12]

// Show headers only
curl --head http://www.google.com/logo_plain.jpg

// Get a FTP directory listing
curl ftp://username:password@example.com/directory/

// Upload a file to a remote directory using FTP
curl -T uploadfilename -u username:password ftp://sitename.com/directory/myfile

Labels: linux tips

Normalize table by creating a child table with the uniquefield and each value of the comma separated column.

Name skills
shan PHP, MySQL, HTML, JAVA
Paul MySQL, Perl
Ahmed JavaScript, C++

You can use comma or space as delimiter to split the values as shown below.

CALL normalize_table('Original_table', 'Name', 'skills', ',');

A new table will be created called SplitValues that will look like this...

cid value
Shan PHP
Shan MySQL
Shan HTML
Shan JAVA
Paul MySQL
Paul Perl
Ahmed JavaScript
Ahmed C++

alter ignore table SplitValues add primary key(cid, value), add key(value, cid);

You will like to add keys on this SplitValues table so that you can join it back to the original table. As the column "Name" is unique in the original table, the following query should work.

select a.* from SplitValues as a inner join original_table as b on a.cid = b.Name where a.cid = "Paul"
will return 2 rows instead of 1

cid value
Paul MySQL
Paul Perl

This has various uses, as it can further be joined with some other table. The join will be very fast as both the columns in these two tables are indexed.

http://forge.mysql.com/tools/tool.php?id=204

Labels: mysql tips

Suppose we have a web application, users post their names and phone numbers. Phone numbers can have very different format:

mysql> select * from phonebook order by phone;
+-------+--------------------+
| name | phone |
+-------+--------------------+
| Sanja | +380 (912) 8008005 |
| Bar | +7-912-800-80-01 |
| Svoj | +7 912 800 80 02 |
| Ramil | (7912) 800 80 03 |
| Hf | +7 (912) 800 80 04 |
+-------+--------------------+
5 rows in set (0.00 sec)

Searching for a phone number is difficult. For e.g. the following query does not return "Svoj".
select * from t1 where phone='+7(912)800-80-02';

mysql> create table phonebook (name varchar(64), phone varchar(64) character set utf8 collate utf8_phone_ci);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into phonebook values ('Svoj','+7 912 800 80 02');
Query OK, 1 row affected (0.05 sec)
mysql> insert into phonebook values ('Hf','+7 (912) 800 80 04');
Query OK, 1 row affected (0.00 sec)
mysql> insert into phonebook values ('Bar','+7-912-800-80-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into phonebook values ('Ramil','(7912) 800 80 03');
Query OK, 1 row affected (0.01 sec)
mysql> insert into phonebook values ('Sanja','+380 (912) 8008005');
Query OK, 1 row affected (0.02 sec)

_____

Solution: reorder punctuation characters, making them ignorable

Solution: reorder punctuation characters, making them ignorable

/usr/share/mysql/charsets/Index.xml

Edit the above file and add the code mentioned below.

....



\u0000
\u0020   
\u0028 
\u0029 
\u002B 
\u002D 

_____

mysql> show collation like '%phone%';
+---------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+---------------+---------+-----+---------+----------+---------+
| utf8_phone_ci | ucs2 | 252 | | | 8 |
+---------------+---------+-----+---------+----------+---------+
1 row in set (0.00 sec)

mysql> select * from phonebook where phone='+7(912)800-80-02';
+------+------------------+
| name | phone |
+------+------------------+
| Svoj | +7 912 800 80 02 |
+------+------------------+
1 row in set (0.00 sec)


http://forge.mysql.com/w/images/b/b7/HowToAddACollation.pdf

Labels: mysql tips

For years, (yes, years!) I did not know how to find the list of all the bugs those I have submitted and to watch the bugs those I have subscribed to. Why? The small check box "only your bugs" and "include subscribed bugs" is hidden deep down. I wish it was at the top somewhere and a mention of the e-mail address that I used to log-in.

Labels: mysql bug

Stemming words using mysql user defined function looks very promising.
For e.g. the user might search for "testing", "tested" or "tests". All these words are related to each other because the base word "test" is common in all of them. the stem_word function will find out what the base word is for each word saved in the database and match it against the base word of the user input.

mysql> select stem_word('en', 'testing');
+----------------------------+
| stem_word('en', 'testing') |
+----------------------------+
| test |
+----------------------------+
1 row in set (0.00 sec)

mysql> select stem_word('en', 'tested');
+---------------------------+
| stem_word('en', 'tested') |
+---------------------------+
| test |
+---------------------------+
1 row in set (0.00 sec)

mysql> select stem_word('en', 'tests');
+--------------------------+
| stem_word('en', 'tests') |
+--------------------------+
| test |
+--------------------------+
1 row in set (0.00 sec)

The stem_word function is not there by default and needs to be installed from:
http://mysqludf.com/lib_mysqludf_stem/index.php

This is different than the soundex logic. As you can see from the following screen shot, all the words have different soundex value and hence are considered different. These three words doesn't has any relation to each other if we use the soundex logic, but using the stem_word logic (as explained above) we can easily find the word families.

mysql> select soundex('testing');
+--------------------+
| soundex('testing') |
+--------------------+
| T2352 |
+--------------------+
1 row in set (0.02 sec)

mysql> select soundex('tested');
+-------------------+
| soundex('tested') |
+-------------------+
| T230 |
+-------------------+
1 row in set (0.00 sec)

mysql> select soundex('tests');
+------------------+
| soundex('tests') |
+------------------+
| T232 |
+------------------+
1 row in set (0.00 sec)

Labels: mysql tips