Shantanu's Blog

Database Consultant

October 31, 2005


Quick Mail

Sending an email shouldn't be that difficult. Right?
Yes, introducing Quick Mail!

1) goto the site
2) If you want to send me an email, type the command
send Happy New Year! - From Uncle

That's it.

If you find that you are sending frequent email to the same address or you think that your uncle won't remember or will not type the email address correctly, create alias. Type the shortcut command.
shortcut shantanu

Now the shorter and easier to remember command will be...
send shantanu Happy New Year! - From Uncle

You can drag the “Quick” bookmarklet to your links toolbar so that you don’t even have to visit the saraswati website. Click on the “quick” link sitting on the links toolbar. A pop up window will be thrown out where you can type the command.
There are other useful commands too! (like gim for google images and wp for wikipedia). Check it out, you will find it interesting.

Did I mention that the "send" command can also be used to send SMS to any cell phone in Canada or USA? for e.g.
send 1234567890 call dad ASAP

October 28, 2005


RSS 2 PDF - Free Online RSS or Atom to PDF Generator

How about reading your favorite RSS feeds as PDF files instead of using the RSS reader?

is the answer.

You can directly link to the site for free using the following link.

For RSS & Atom (Add 'img=1' for Images)
Or use the bookmarklet available. fans - use the above tool to easily create PDF archives based on any Flickr newsfeed. Enter information for a particular user on Flickr (such as yourself) and/or Tags separated by commas for photos relating to a specific subject.

October 25, 2005


MySQL Case Study - 84

Order By a column that is not selected...

SELECT DISTINCT `topic` FROM `berichten` WHERE `forum` = '$forum' ORDER BY `id` DESC

returns the rows in a different order then:

SELECT DISTINCT `topic` FROM `berichten` WHERE `forum` = '$forum' ORDER BY `id` DESC LIMIT 0, 30;

The second query returns the right order, but I do not want a limit.
I read something about this in the documentation, but I did not fully understand that though.
When LIMIT is set to 0, 135 or higher the order of the returned rows is also wrong.


MySQL Case Study - 83

I have the following statement to remove entries from a number of tables matching a common key:

DELETE items, group_user_role,groups
FROM items AS i, group_user_role AS gur, groups AS g
WHERE g.group_id=gur.group_id AND g.group_id=i.group_id AND g.group_id='5';

My problem is, there is not always an i.group_id value and when there isn't the condition isn't currently met. How do I make the statement work whether or not i.group_id values exist?


MySQL Case Study - 82

sum( as "Weekly",
(select sum( from NetworkTraffic where date="2005-10-12") as "Daily"
FROM `NetworkTraffic`
LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId
WHERE date between "2005-10-10" AND "2005-10-16"
here is the result of the query

| name | Weekly | Daily |
| Default FTP Site | 566 | 591 |
| Default Web Site | 2 | 591 |
| net_tot | 714| 591 |
| _total | 78 | 591 |

The daily does not group by domain, it calculates the same result all the time.. how can I do this?


MySQL Case Study - 81

I'm collecting summary of daily record around midnight and building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0
BUT! my function call requires a constant-length list. So if any months in my selection range have no data, I need to fill the space with an empy value. How can I do this? Perhaps with an helper table, but how?


CREATE TABLE one2twelve (

(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);

IFNULL(SUM(mytable.value1), 0)
FROM one2twelve
LEFT JOIN mytable ON
MONTH(mytable.rec_date) = one2twelve.i AND
mytable.rec_time BETWEEN '00:00:00' AND '00:30:00' AND
YEAR(rec_date) = YEAR(NOW())
GROUP BY one2twelve.i;


MySQL Case Study - 80

If I had a table consisting of:

player l code l amount
playerA l 1 l 5
playerA l 2 l 6
playerA l 3 l 4
playerB l 5 l 10
playerC l 3 l 4
playerC l 4 l 3

SELECT player, code, SUM(amount) as amt FROM table GROUP BY player

How would I get the result below?

playerA 1,2,3 15
playerB 5 10
playerC 3,4 7

October 24, 2005


MySQL Case Study - 79

REPEAT syntax question
I need to run the following INSERT statement 125+ times:

INSERT INTO user_web_status (web_status) VALUES ('Active');

I have been trying to use a "REPEAT" or "WHILE-DO" statement to get the script to work... but just syntax errors so far. The other column in the table besides "web_status" is "user_ID", which is the primary key and auto-incremented.
I tried:

INSERT INTO user_web_status (web_status) VALUES ('Active');
user_ID > 125

...something is not quite right with that (getting syntax errors).

Any assistance is greatly appreciated... thanks.


MySQL Case Study - 78

I have created a table called t as:

name varchar(7) default NULL

and inserted following values into it:

insert into t values ('Chagh'),('Ach'),('Achchany');

now I want to report a numbered list according to alphabetical sort of values.


MySQL Case Study - 77

Question: I want to display all records that are cancelled and are dated between today and 7 days before.
I am using varchar column to store dates in dd/mm/yy format.

okay, we will have to use SUBSTRING three times, and CONCAT using '-' as the separator, in order to construct a string in YYYY-MM-DD format, and then just to be safe, we'll add 0 days to it, in order to ensure that it's a date value, and then we can use BETWEEN...

select foo
, bar
, ship_date
from orders
where status = 'cancelled'
and date_add(
, substring(ship_date,7,2)
, '-'
, substring(ship_date,4,2)
, '-'
, substring(ship_date,1,2) )
, interval 0 day)
date_add(current_date, interval -7 day)
and current_date
by order_id desc


There were 3 tables products, games and product_description. The name is in the product_description whereas the price in product. How do I show the products which don't have a product description?

SELECT * FROM products
WHERE products_id NOT IN (
SELECT products.product_id FROM games
INNER JOIN products_desc
ON products.product_id =products_desc.product_id


This brings up a error everytime. Why?

select e.client,, e.state
from table1 e, table2 n, table3 c
where e.client in c.client and n.client NOT IN e.client;


The correct syntax is:

e.client,, e.state
from table1 e, table2 n, table3 c
e.client in (c.client) and n.client NOT IN (e.client);

But I suspect that's not what you want.


e.client,, e.state
from table1 e
e.client in (select client from table3) and
e.client not in (select client from table2);

or the equivalent without subqueries:

e.client,, e.state
from table1 e
left join table2 n on e.client = n.client
left join table3 c on e.client = c.client
c.client is not null and n.client is null;


MySQL Case Study - 76

mobile_no field values are:


npa_nxx field values are:


I would want the query to return 646-555-1212 and 646-555 because they that phone number matches the NPA-NXX code


MySQL Case Study - 75

sorting in MySQL

OK, I`m new to PHP and MySQL and I`ve already read lots of stuff on the subjects (and going crazy), so my exuses in case I am asking something simple or already covered somewhere.

I have a Table, from which enties starting with numbers must be displayed. What I already have - if I press 'A', only names from some column in a table starting with A would be shown. For this I use "SELECT smth FROM smwhr WHERE smth LIKE 'A%';"

So, my question is, how to show all the names starting with a number from 1 to 10 (for example) using only one query. To be exact, from names like:

No More Suffering
I Have No Mouth and I Must Scream
7 Deadly Sins
9: The Last Resort

I would like to be displayed only:

7 Deadly Sins
9: The Last Resort


MySQL Case Study - 74

getting most recent entries from 1 table


I have a table with a list of articles from various topics all stored in one table. My columns are:

'id' 'articlename' 'category' 'article' 'timedatestamp'

Each article belongs to one of 5 categorys.

My question is though how can I get the latest article from each category in one query? So the query should return 5 results with the latest article added to each category.

I'm really stumped on this one so any help is very much appreciated.


MySQL Case Study - 73

Self Joins

my SQL join knowledge is not the best, and I am trying to work out the best way to have a three layer structure in one table. It needs to be like a Manager > Supervisor > Staff Join.

Here is some sections of my table, and I would like to know the SQL query to be able to select on the word "sissi" and be able to produce the output of Sissi > Crete > Greece.

Crete is Part of Greece and Sissi is part of Crete.

I dont seem to be able to get the self join correct

| greece_placeid | NAME | Parent_placeid | Child_placeid | Sibling_placeid |
| 1 | Greece | NULL | 2 | NULL |
| 2 | Crete | 1 | 3 | NULL |
| 3 | Sissi | 2 | NULL | NULL |


MySQL Case Study - 72

Who reviewed all the books written by X?

This is my database table structure

BookAuthor(book, author, earnings)
BookReview(book, reviewer, score)

The query that I am having trouble with is...

Who reviewed all the books written by Elmasri?

Now I get as far as the statement which gives me all reviewers who have reviewed at least one of Elmasri's books but I can't get any further...can some do this with EXISTS or NOT EXISTS

FROM BookAuthor JOIN BookReview ON
WHERE author='Elmasri';

Any help would be greatly appreciated...I am stumped.

October 23, 2005


Detecting IP range

I have a mysql table called "visitors" which lists all the hits to my website.

somedate user_name user_ip
2005-09-07 Blah-blah
2005-09-08 wah-wah
2005-09-09 Blah-blah
2005-09-09 nah-nah

In the table there is a column called "user_ip" which lists ip addresses in the usual format eg

I want to prevent a user from creating multiple identities so I need to find addresses which are coming from the same computer. If the user has a dynamic rather than static address then the last two figures will change, but the first two figures, eg 202.21 in the above example, should stay the same.

What query can I use to find out how many ip addresses in the list have the same first two figures?

I can use the wildcard %, say

SELECT * FROM visitors
WHERE user_ip LIKE '202.21.%'

somedate user_name user_ip
2005-09-07 Blah-blah
2005-09-08 wah-wah
2005-09-09 nah-nah

But I want to do this where I don't know what the first two figures are.

select substring_index(user_ip, '.', 2) pf, count(*)
from visitors group by pf

pf count(*)
202.21 3
202.221 1

I can just change the 2 to 3, or 4 if I want the whole (exact) ip.

select substring_index(user_ip, '.', 4) pf, count(*)
from visitors group by pf

pf count(*) 2 1 1

Once I have the user_ip, for e.g. I have found there are 2 instances of, how do I query the table to bring up user_id and user_name for that ip?

select i, substring_index(somefile, '.', i) pf, count(*), group_concat(distinct someblah)
(select (1) as i union select (2) union select (3) union select (4)) as n
join youneek
group by i, pf
order by i desc;

i pf count(*) group_concat(distinct user_name)
4 2 Blah-blah,nah-nah
4 1 wah-wah
4 1 Blah-blah
3 202.21.23 3 wah-wah,Blah-blah,nah-nah
3 202.221.213 1 Blah-blah
2 202.21 3 wah-wah,Blah-blah,nah-nah
2 202.221 1 Blah-blah
1 202 4 Blah-blah,nah-nah,wah-wah

Now adding the line after group by
having i > 2 and count(*) > 1
will pin point the person who is accessing the site using two (or three) different user names from the same IP or the IP range

select i, substring_index(user_ip, '.', i) pf, count(*), group_concat(distinct user_name)
(select (1) as i union select (2) union select (3) union select (4)) as n
join visitors
group by i, pf
having i > 2 and count(*) > 1
order by i desc

i pf count(*) group_concat(distinct user_name)
4 2 Blah-blah,nah-nah
3 202.21.23 3 nah-nah,wah-wah,Blah-blah

October 18, 2005



I just came across a very unusual mysql behavior. MySQL just like latest word and excel versions is very smart and does most of the things on it's own. But sometimes letting it do that way may prove fatal.

Yes, technically, It' not a bug. but I will call it 'a bug' because it looks and tastes like it.


`invoiceno` int(11), `amount` decimal(10,2));

CREATE TABLE `receipts` (
`invoiceno` int(11), `amount` decimal(10,2) );

insert into bill values (1,23), (2,43), (5,39);

insert into receipts values (10,23), (20,43), (5,9);

select invoiceno from bill
inner join receipts
where bill.invoiceno = receipts.invoiceno;

select invoiceno from bill
where invoiceno in (select invoiceno from receipts);

In the above example the inner join doesn't work (unless the table name is mentioned like this...)
select bill.invoiceno from bill
But the "DEPENDENT SUBQUERY" does work (without table reference). I wanted to note and mention the unexpected behavior. (unexpected, from general user’s point of view and not from an expert’s point of view)


The argument for why the "DEPENDENT SUBQUERY" works may be like this...
because the part in the brackets (select invoiceno from receipts) is calculated first and then the results are taken backwards.

Ok. Perfect.

But how will you explain when there is no invoiceno column in the receipts table, it actually selects the invoiceno column from the bill table?

drop table bill;
drop table receipts;

`invoiceno` int(11), `amount` decimal(10,2)

CREATE TABLE `receipts` (
`cheque` int(11), `amount` decimal(10,2)

insert into bill values (1,23), (2,43), (5,39);
insert into receipts values (10,23), (20,43), (5,9);

select * from bill;
select * from receipts;

SELECT invoiceno FROM bill
WHERE invoiceno IN ( SELECT invoiceno FROM receipts);

Now there is no inoviceno column in the receipts table. So it should show an error. right? It actually grabs the invoiceno column from the bill table!

October 16, 2005


Email alternative

You have a problem in e-mail called "occupational spam." It's when you carbon copy or blind carbon copy; you stretch your e-mail into a broadcast medium. Google says that's about 30% of e-mail. We found the same measure. The interesting thing, though, is that when you give people an alternative; a way in which they can communicate from one person to a whole group, or even in menu-to-menu fashion, and a space designed for menu-to-menu interaction, you get some simple efficiencies. E-mail is a point-to-point network, with this big mess. A lot of people are playing e-mail volleyball. Nobody knows which attachment is the latest version. You're not communicating very clearly, efficiently, or effectively. The difference with a wiki work space is that it's private for a group.

October 10, 2005


Yahoo blog search

Under Yahoo's new approach, a keyword search for online news will include a list of relevant Web logs, or "blogs," displayed in a box to the right of the results collected from mainstream journalism.

October 09, 2005


News: Google Announces Google Reader

News: Google Announces Google Reader: "
Google is announcing as I type a new feedreader - a first generation one, but full text, which I think is the best approach."

October 06, 2005


Web based spreadsheet

How about accessing your Word documents and excel spreadsheets from across the Internet and around the world? With ThinkFree Office Online, new from ThinkFree, Inc., you can do all this and more.

October 05, 2005


MySQL Case Study - 71

substring and substring index

I have a varchar field in my table that contains email addresses like this

Now I want to have a query that only picks out the bit after the @ part (they have already been vaidated to they all are correct) and then groups the results to get stats on each domain. Is this possible?

October 04, 2005


MySQL Case Study - 70

Order Order!!

My current query SELECT * FROM table ORDER BY name is coming with the following resultset:


I am looking for this:


I have tried the following:

ORDER BY ord(name)
ORDER BY soundex(name)
ORDER BY lpad(name,5, '0')
ORDER BY name + 0
ORDER BY 0 + name

None have helped.

Can anyone help me out?,34908,35959#msg-35959

1) The following query will work only if the number always appears at the fixed position from left. i.e. the number will start from the sixth character from left.

SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(name) -5) AS unsigned)

But how do I implement the query to take the length of name instead of 6 as the total length.
Instead of -5 i think its better to grab the length of name and subtract one

2) The following query solves the problem of measuring the length of the string.


But no body knows that the number will always start from the sixth character in the string.
The name can have more then 6 alphanumeric characters. How do I get around that?

3) Another option is to sort on the length of the entire string.

SELECT * FROM Kanwal ORDER BY LENGTH(name), name;

But that won't work if I have data like this...


The second row AIYC 10 will appear *after* AIYC 2 since it has more characters in it.
But actually it should be before it as shown above.
So this query won't work as well.

4) If you have a delimiter separating characters and numbers you can use substring_index.

ORDER BY substring_index(name, ' ', 1), substring_index(name, ' ', -1)+0;

But if you don't have a delimiter (ABC1, ABC2, ABC30, ABC10, AIYC1, AIYC2, AIYC10), then what?

5) So here's one way to sort alphanumeric values without a delimiter - I'm not sure if it's the best.

drop table tmp;
create table tmp (name char(10));
insert into tmp values ('AIYC1'), ('AIYC10'), ('AIYC2'), ('ABC1'), ('ABC2'), ('ABC10'), ('ABC30');
insert into tmp values ('tmp.10'), ('tmp.3'), ('emp-22'),('emp-7'),('emp-6a'),('emp-6b'),(45),(7);

--check the maximum length of the field

select max(length(name)) from tmp;

-- and then write a conditional up to that number

select name from tmp
ORDER BY left(name,
(CASE when substr(name,1) between '0' and '9' then 1
when substr(name,2) between '0' and '9' then 2
when substr(name,3) between '0' and '9' then 3
when substr(name,4) between '0' and '9' then 4
when substr(name,5) between '0' and '9' then 5 ELSE 6 end)-1),
CASE when substr(name,1) between '0' and '9' then 1
when substr(name,2) between '0' and '9' then 2
when substr(name,3) between '0' and '9' then 3
when substr(name,4) between '0' and '9' then 4
when substr(name,5) between '0' and '9' then 5 ELSE 6 end);

| name |
| 7 |
| 45 |
| ABC1 |
| ABC2 |
| ABC10 |
| ABC30 |
| AIYC1 |
| AIYC2 |
| AIYC10 |
| emp-6a |
| emp-6b |
| emp-7 |
| emp-22 |
| tmp.3 |
| tmp.10 |


MySQL Case Study - 69

I want to convert a field in which full christian names are stored into initials.
For instance:
James Henry William > J.H.W.
Is there anybody who can tell me how this can be done?

USE test;
name CHAR(50) NOT NULL

("James Henry William"), ("John Paul"), ("Gert");

INSERT INTO ints VALUES (1), (2), (3), (4), (5);,40430,47761#msg-47761


MySQL Case Study - 68

Finding a NULL date between dates

I'm trying to find the dates that have no records between September 1st and October 1st. Something along the lines of...

SELECT NULL(date_col) FROM table WHERE date_col >= '2005-09-01' AND date_col <= '2005-10-01'

Weird huh?


MySQL Case Study - 67

Querying Top n Search Phrases

I am logging search phrases entered by users to a database and want to be able to determine the Top n Search Phrases. E.g.:

| id | search_phrase |
| 1 | lorem |
| 2 | ipsum |
| 3 | dolor |
| 4 | lorem |
| 5 | ipsum |
| 6 | lorem |

If I wanted to find the top 3 search phrases from the above data, I should get:

1. lorem (3)
2. ipsum (2)
3. dolor (1)

Any ideas?


MySQL Case Study - 66


I need to be able to retrieve the latter part of my districts results stored in my table from the right of '-' the '-'appears in a different location at any time as the district is prefixed with either a 2 or 3 letter prefix..



i have copied an example from the documentation:

SELECT LEFT( district, LOCATE( '-', district ) -1 ) AS test
FROM `areas`

and i get


which is great but i need to return the latter part not the first bit, ive tried changing the sql to use RIGHT but this just gives me garbage and a headache..

any suggestions, i cant find anything on this..


MySQL Case Study - 65

Cross Tab query

This is my scenario:

I have two tables:
persons (id,age,roleid)
roles (roleid,description)

I want to build a sql query to produce the following rows (example):

range(age) role1 role2 role3 .... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19

I don't know if group by clause or having clause are helpful in this situation. Right now, I have no idea on how to do it.


MySQL Case Study - 64

How can I find the listing number of a row ?

I want to make a query which returns me the number of a row from total rows, I will make an exemple because I can't explain better

1245 | row dfsfsdf | categID |
1246 | rows cvxc | categID |
1247 | sc xvxcv xc| categID |
1248 | row dfsfsdf | categID |
1249 | rows cvxc | categID |
1250 | sc xvxcv xc| categID |

let's say that those are some rows inserted in a table and I have the ID of the row which I want to know when it occurs in my query result, for exemple ID 1247 is the 3rd, 1249 is the 5th .

Can I found that number using something else then a while($query...){
if($curentID != $id) $i++
else $the_number = $i


MySQL Case Study - 63

currently, my query returns:
Col1 col2
------ ------
A P123
A P124
B P125
C P126
A P127
B P128

How do I change my query to display:
Col1 col2
------ ------
A P123, P124, P127
B P125, P128
C P126


Create a Photo Album Presentation in PowerPoint

On the Insert menu, point to Picture, and then click New Photo Album.

In the Photo Album dialog box, you can choose to add pictures from your hard disk or a peripheral device, such as a scanner or digital camera.

You can capture all photos at once, hold down the CTRL key, click each picture file you need, and then click Insert. Next, specify the look of the album under Album Layout.

Note: If you want to change or update your photo album after you have created it, use the Format Photo Album dialog box from the Format menu.


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023   April 2024   May 2024   June 2024  

This page is powered by Blogger. Isn't yours?