Shantanu's Blog

Database Consultant

December 29, 2005


MySQL SQL Syntax and Use

To write a multiple-table DELETE, name all the tables in a FROM clause and specify the conditions used to match up records in the tables in the WHERE clause. The following statement deletes records from table t1 where there is a matching id value in table t2:

DELETE t1 FROM t1, t2 WHERE =;

To delete rows from both tables where there are matching id values, name them both after the DELETE keyword:

DELETE t1, t2 FROM t1, t2 WHERE =;

December 28, 2005


Free and Open Source

An interesting blog post is published here...

>> how can it be free of charge, and be of good quality too?
>> What's the catch?

Free and Open Source software is unbelievable and revolutionary.
Don't get surprised.

>> I thought I knew what the catch was: MySQL 4.0 was not that good.

Open Source software is "evolved software". It improves itself over a period of time.
If he had tested MySQL 4.1 or 5.0 his opinion would be different. :)

Open mind:
>> Of course, I just consumed the products, briefly glancing over the features.

You have to use Open Source software like firefox for a few weeks to understand it's true strength.

>> Gradually, my attitude changed.
>> A product that definitely made a difference in this respect was PHP.

I was introduced to PHP by my web hosting company since they had installed PHP.
When you use PHP, you don't look back.

Installation time and expertise:
>> I set up in both Apache and Microsoft Internet Information Server -
>> both up and running within 10 minutes.

This is something awful about open source software. The easy way to download, the time it takes to download and install is an important aspect of any software.
Install Open Office or firefox to check what I am trying to say.

A "real" software:
>> a product that I legally did not pay for, it had clear documentation
>> AND it worked exactly as described

You got it! :)

Invisible open source:
>> I didn't know Apache was open source!

Most of the web sites are powered by open source software.
And most of us don't know that!

customer to developer:
>> my confidence in open source software has got another boost
>> by getting involved (in my own modest way) in the MySQL forums,
>> and in writing the odd blog or article.

With open source, you just don't use it but report bugs, suggest features,
help new users and finally write code!

>> Linux for human beings that do like to configure,
>> make the OS see my hardware and solving IRQ conflicts.

>> and how fast it works!

>> No helpdesk can beat this kind of support - it's just amazing.

December 27, 2005


From Rajesh Jain's Weblog

India's politicians have, for the most part, failed us. India's entrepreneurs cannot afford to.


Dynamic Images

Header ("Content-type: image/png");
$img_handle = imageCreateFromPNG("");
$color = ImageColorAllocate ($img_handle, 100, 100, 100);
ImageString ($img_handle, 3, 10, 9, "Your IP: $ip", $color);
ImagePng ($img_handle);
ImageDestroy ($img_handle);

Surprisingly, the code is quite short. It's also extremely straightforward.

December 26, 2005


MySQL Case Study - 99

I have columns foo, bar, qux and so on...

Select * from db where "%" = "1969"

So I wanna search EVERYTHING in the db for ANYTHING that says 1969. Whats the query to do this?


MySQL Case Study - 98

How to do a select statement that when the user search the ProteinName YNL031C, all the other proteins that have the same Complexes Id will be shown too?

ProteinName ComplexesID
YNL031C 123
YWN078C 123
YNL031C 412
YNL031C 745
YWC234C 745
YNL009W 745,60579,60579#msg-60579


MySQL Case Study - 97

I have a Mysql database of 3 tables
1)CUSTOMER (cust#, name, address, tel#, etc)
2)PRODUCT (prod#, desc, price, etc)
3)BOOKINGS (cust#, prod#, order_date)

I also have an admin page with options to display:
1)all bookings
2)bookings for next 7 days
3)bookings for next 14 days

How do I query the database to show the cust#, prod# and order_date from the BOOKINGS table, and the name, address, price from the CUSTOMER table for the three date options above?
I think I need to join two tables within a SELECT statement but am stuck on how to use todays date in the query.,60539,60539#msg-60539


MySQL Case Study - 96

I have 3 tables (many to many relation)

company -< cclink >- contact.

Now I want to create one query wich will get all the information about:
- companies and their contacts
(SELECT * FROM company, cclink, contact WHERE = cclink.company_id AND = cclink.contact_id)
- companies which don't have any contacts
(SELECT * FROM company WHERE NOT IN (SELECT cclink.company_id FROM cclink)
- contacts which don't have any companies
(SELECT * FROM contact WHERE NOT IN (SELECT cclink.contact_id FROM cclink)

Is it possible to conect those 3 queries in one query?,60349,60349#msg-60349

December 23, 2005


Determine the current user in MySQL 5.0

ON test.* TO testuser2@'%';

Now login with this testuser2 account from your localhost and execute the two functions directly inside a SELECT statement:

| testuser2@localhost | testuser2@% |
1 row in set (0.01 sec)

SESSION_USER() returns exactly the credentials used for login, including the hostname (which would be different if you logged in from a remote host), but CURRENT_USER() reflects always the underlying grant that determines the access rights of the user, which is testuser2 from any host (%).

December 22, 2005


Knowledge@Wharton - Wikis, Weblogs and RSS: What Does the New Internet Mean for Business? - Higher Learning - CIO

Knowledge@Wharton - Wikis, Weblogs and RSS: What Does the New Internet Mean for Business? - Higher Learning - CIO:

"One of the simplest arguments I've used to get people out of a traditional mindset is to point out a statistic—the cost of transactions in the U.S. More than 50% of the non-government GDP in the U.S. is based on transaction costs. Now, what's interesting is that the way most people think about economics is that execution costs are on the periphery. If you start from the premise that transaction costs are central to the productivity of any system, and if you then recognize that most of our time is spent negotiating, securing, monitoring, making sure people did what we expected them to do, dealing with the fact that motivations aren't entirely aligned, and so on, you realize that we have to find a way of working together amid this asymmetry of information. About half of our time is spent doing those things."


shantanuo private pastebin - collaborative debugging tool

shantanuo private pastebin - collaborative debugging tool: "pastebin is here to help you collaborate on debugging code snippets. If you're not familiar with the idea, most people use it like this:

* submit a code fragment to pastebin, getting a url like
* paste the url into an IRC or IM conversation
* someone responds by reading and perhaps submitting a modification of your code
* you then view the modification, maybe using the built in diff tool to help locate the changes"


MySQL Case Study - 95

Finding Unique dates in a month:
I have table which contains the columns:
the_year, the_month, the_day

Sample data:

From these 3 columns I need to determine the "days of activity per month".
So October has 2 days of activity because it has 30 and 29.
And November has 3 days of activity because it has 01, 02 and 11
How can I do this?

December 21, 2005


Google Operators

If you want to do *any* calculation, just type it in google search box. for e.g.

1 a.u./c
1.21 GW / 88 mph
e^(i pi)+1
sine(30 degrees)
G*(6e24 kg)/(4000 miles)^2

There is an "in" keyword for conversions.

5 kilometers in miles
4 in roman
0x7d3 in roman
45% of 300 US dollars in Euro
123 in binary
0×20 in decimal

You can restrict the search by using one of the google operators followed by :

The site: operator can be used at any domain level including .gov ( and .edu (
You can also have it restrict the search to a directory on that server:

You can use lyrics: and movie: operator to find music info.
movie:[zipcode or city]

And you can also use intitle: operator to find mp3 files on the net.
intitle:index.of + mp3 + "grandaddy" -html -htm -php -asp apache 2005

hard-to-reach customer support phone numbers - company name and the standard toll-free prefixes.
[company name] 800 OR 877 OR 888 OR 866

December 20, 2005


3 reasons to upgrade to firefox 1.5

1) You can rearrange tabs by dragging and dropping them left and right.
2) You can force links that would open in new windows to open in new tabs instead. (Tools - options - Tabs - Force links.
3) A "Clear Private Data" command will wipe out all the records Firefox keeps of your browsing — cookies, saved passwords, cached pages and so on. Tools - Clear Private Data (Ctrl + Shift + Del) Private data can be automatically deleted when you close your browser. (Tools - Options - Privacy - Settings... - Clear private data when closing Firefox


Yahoo + yubnub

Yahoo! adds today a few handy shortcuts to its search, and here are a few examples:



How to use?
!audio madonna
!note this is a test note to be saved.

If you want to use your favorite yubnub commands, create a shortcut for and save it in your account.
First login to your yahoo account and then type this command in Yahoo Search box.
!set yb

Click this link
and choose "ok" on the next screen.

Once set, type the following command in yahoo search box to use yubnub command,
!yb wp india
(Wikipedia - India page)

To delete this command from your yahoo account you will be using mm... yes, you guessed it.
!unset yb


Why does bloglines matter?

I think Bloglines is as important as Yahoo mail or gmail for me. Why?

1) Scrolling: As you can see from the screenshots, I have to scroll left to right to read the full line!

Bloglines allows me to read the post without scrolling horizontally, no matter what resolution!

2) Design: I love CSS and small fonts and other Java Script tactics used by smart programmers just like everybody else. But what really matters is "Content". Every website is like an application. I have to learn how to use if efficiently, how to get to the real page as soon as possible. Bloglines (and I must mention wikis) are very good at delivering contents within no time!
I like the same readable font size and color across all the sites. Making the site contents colorless doesn't make it less important!

3) Search: I can search within the feeds I have subscribed to and most probably I will find what I am looking for since I have 1000 feeds!
(Just kidding)

4) Speed: Bloglines knows which feed I am going to read next. They show the entries from cache within no time!

5) Share: Bloglines makes it very easy to check feeds a user is watching by going to public/user (for e.g. public/shantanuo)

6) Shortcuts: Bloglines has recently introduced keyboard shortcuts, but I am still not used to it. (not used to gmail shortcuts as well)

7) Email This: select text from an entry and click on "Email This" link. Most blogging sites (like provides an email address where you can send your posts to be published. You can use this email address to "republish" the contents from one post to your own.

8) Clip/Blog This: Bloglines has an inbuilt blog that is easy to update. Just select the text and clip it. Selected text should appear on your personal clip book. (Firefox 1.5 doesn't like the selected text and posts only the header! Is bloglines listening?)

9) Archiving: Bloglines displays only the last 200 entries. But I can select entries from archives updated in the last week or last month.

10) Viruses: There is no way a virus can ever enter into my computer if I read RSS feed instead of visiting the website.

That is why I don't mind Bloglines Plumber visiting me once in a while :)

Thank you bloglines!

December 19, 2005


Neopets! Who?

Neopets has a staggering 25 million members worldwide. It has been translated into 10 languages and gets more than 2.2 billion pageviews per month. These dedicated Neopians spend an average of 6 hours and 15 minutes per month on the site. That makes Neopets the second-stickiest site on the Internet - ahead of Yahoo!, MSN, AOL, and eBay, according to Media Metrix. What's more, its demographics are the stuff of marketers' dreams: Four out of five Neopians are under age 18, and two out of five are under 13.

The roster of clients who have set up shop inside the land of Neopia runs from Atari and DreamWorks to Frito-Lay and Lego. While passive product placement has become standard in TV and film, the Neopets approach emphasizes interaction and integration. Neopians can even win virtual trophies featuring Bubble Yum and SweeTarts, and display them for other players.
This seamless interweaving of marketing and entertainment is an advertiser's dream come true. "There's nothing on the Net delivering an experience like Neopets," says David Card, a senior analyst for Jupiter Research. "Kids aren't being harangued, and parents think it's safe."


December 18, 2005


MySQL Case Study - 94

Group by and return percent of total group

I need to construct a query where I GROUP BY a field, but also return the percent in group.
For example, my initial query might be, 'SELECT field_1,count(field_1) FROM table_a WHERE field_1>'0' GROUP BY field_1'.

If field 'field_1' containes values 1 thru 5, then I might get a result (comma delimited) such as...

1 200
1 300
1 400
1 400
1 500
2 100
2 700
2 300
2 900
3 320

Is there any way to modify the query so that the count for each value is divided by the total count (i.e., for value 1 1800/4120 returning a value of 43.69, the percent of total count)?

I'm using a custom query client which requires the entire query to be 1 statement; therefore I cannot run several queries where the first returns a variable (such as the total count).

I currently run mysql 4.1, but can upgrade to 5x if the solution is only available in that version.,52997,52997#msg-52997


MySQL Case Study - 93

These are how my tables are:

Style Table
StyleID, StyleName
12345, Apple
12346, Orange
12347, Lemon
12348, Lime

StyleCategory Table
StyleID, CategoryID
12345, 33
12345, 34
12345, 35
12346, 33

I need to return the StyleID where more than one certain CategoryID's show up. Such as, return the StyleID that has CategoryIDs 33, and 34.

Could anyone help me I have tried a query such as this but I get no results:
SELECT DISTINCT StyleID FROM Style, StyleCategory WHERE Style.StyleID = StyleCategory.StyleID AND StyleCat.CategoryID = '33' AND StyleCat.CategoryID = '34';,52734,52734#msg-52734


MySQL Case Study - 92

I am creating a soccer website, and am trying to produce a table that shows how many goals the players have scored. I have a players table and a goals table, but when I use the count( function, it only shows the players that have scored? How can I show a full list of players including those that have not scored, therefore do not have any entries in the goal table?

Fields are:
players: id, name
goals: id, match_id, player_id,52632,52632#msg-52632


MySQL Case Study - 91

My current site requires calculated rating values. These ratings are submitted by users and they are either 1, 2, or 3. A simple average will not work because if one person votes 3, that will have as much weight as if 100 people vote 3. Also, simply using the sum feature is not ideal because 3 1's will equal 1 3. Ones should mean that the rating should stay at one, but 3's could essentially increase. In other words, if a lot of people rate a question 1, it should stay at one, or even go below 1. But if a lot of people vote 3's, then it should raise above 3 by a little bit each time. It is sort of like a parabola (remotely). It requires more mathematics than just adding. Does anyone have suggestions. I am currently using this and have tried avg in the past:



MySQL Case Study - 90

I have a list of items. The user will be able to sort these by various methods. The most difficult, but but highly desired of which will be by a rating system. The ratings are stored in a second table. Ratings are either 1, 2, or 3 and I need these to be averaged, but then the first table needs to be sorted by these. In other words, the 2nd table has a huge list of 1s, 2s, and 3s...some relate to each question. Each question needs to have these values averaged together, and then be sorted by these. Can I do this in one query? Otherwise, do I have to bring the list of items into perl and then calculate the averages, only to combine them into @questions. Doing it all in one query would be nice but I don't know if you can do

SELECT * from questions SORT BY avg(2ndtable.questionid),53031,53031#msg-53031


MySQL Case Study - 89

find a upperclass character in a string

I want to Take a table of jpg names like JackDanials.jpg, JimSmith.jpg and return (using SQL) into a data set that looks like

Firstname Lastname
----------- -----------

December 17, 2005



If you use MySQL on a large data warehousing application or a popular web site, odds are that your server is running many more read queries (SELECT) than write queries (INSERT, UPDATE, and DELETE). If that's the case, replication is an excellent way to support basic load balancing. By having one or more slave servers, you can spread most of the work among several servers.


Desktop Linux For Small Business

ZDNet has published a comprehensive round-up of reviews of several major Linux distributions, with emphasis on small business needs. The winner? Ubuntu Linux 5.10: "We emerged from our Linux experience with a strong preference for Ubuntu Linux 5.10, with SUSE Linux 10 a close second. If you prefer to deal with a consultancy-like support service, then Novell Linux Desktop 9, may be a good move, while the very corporate Red Hat Desktop 4, could prove a sensible option for companies with large numbers of desktops.",39024180,39237495,00.htm

December 16, 2005


Online Image Editor

PXN8 is a free online image-editor.
Along with a slew of nice editing features like crop, rotate, resize etc, PXN8 also integrates with Flickr, allowing you to edit your Flickr photos with the click of a bookmarklet, then save the edits back in Flickr.

December 15, 2005


Gmail File Space Firefox Extension (gspace)

This extension allows you to use your Gmail Space (2 GB) for file storage. It acts as a remote machine. You can transfer files between your hard drive and gmail. Your gmail account looks like a FTP host and you can upload and download your files. After you install, you get an option called "GSpace" in your "tools" menu clicking on which opens the window for transfer of folders/files. You need latest firefox version 1.5 to install the extension.

What is Talkr? Talkr allows you to listen to text-only blogs on your iPod. Talkr can podcast the blogs you like!

The another one is called Feed2Podcast
and it features a male robot voice instead of a female one like Talkr uses.
Again, it's pretty decent, and if you want to, you can subscribe to any podcast feed for e.g.

December 12, 2005


Guaranteeing Data Integrity with MySQL 5.0

What does MySQL do when you try to insert invalid data?
It 'silently' converts the incoming bad data into the default values for each of the table's column datatypes.
Experts advised to use PHP to first check the data before trying to add it to the database. It added one more layer of programming and complexity. (and thus actually increased chances of invalid data landing up into your database!)

The good news is that MySQL 5.0 now offers server-enforced data integrity.

set sql_mode=strict_all_tables;

First, notice that the server-enforced data integrity capability is dynamic - it can be changed without stopping/starting the MySQL server. Next, take note that the above example changed server-enforced data integrity at the client level only. Thus
show global variables like 'sql_mode';
will not return any value where as...
show variables like 'sql_mode';
will return "STRICT_ALL_TABLES"
A DBA can globally set such data integrity enforcement by changing the sql_mode configuration parameter in the my.cnf file or they can start the MySQL daemon/service and specifically specify it via command line startup if they wish. A client (given the privileges) can also override the global setting if they desire.

However, the main thing to observe in the above test is that MySQL is now rejecting invalid data and is not converting it into a column's default value.


powerpoint to OPML

OPML Workstation is a preliminary version of PowerPoint® to OPML site. It provide conversion of PowerPoint® files to OPML files. OPML files created by the converter include an OPML rendering of your PowerPoint outline as well as .gif graphics files that reflect the look and feel and embedded graphics of your presentation.
OPML produced by the converter is compatible with Dave Winer’s OPML editor, and validates using the OPML validator.
They host your OPML files and provide you with personal URL links to your OPML files. Files in your private folder are password protected, and files in your public folder are open to others.

December 11, 2005


Respondus Product: StudyMate 1.5

Respondus Inc. develops testing, survey, and game applications for eLearning. Most products are highly integrated with the leading course management systems, making it easier for instructors to create and manage online materials.

StudyMate is an authoring tool that lets you create 10 Flash-based activities and games using three simple templates. The Flash activities are usable with any web server.


Merge all data from the csv files into a text file

1) Windows Start Button | Run
2) Type cmd and hit enter ("command" in Win 98)
3) Go to the folder with the CSV files (for help enter "help cd")
4) Type copy *.csv all.txt and hit enter to copy all data in the files into all.txt.
5) Type exit and hit enter to close the DOS window

When you use File Open to open the file all.txt the Text Import Wizard will help you import the file into Excel. Choose Delimited - Comma to format the data.


Online Excel spreadsheet

With Tracker you simply copy and paste from Excel to host your spreadsheet securely on the net. Editing the data is as easy and as quick as opening and editing file from your local hard drive. You can use the Calendar and / or Map tool if dates and addresses are used in the data.

1) You can sort the records by right clicking on the column name.
2) You can also allow your friends either to view or edit the data.
3) "Export table data back to excel" is just one click away.

Bugs / Limitations:
1) You can currently host only 2 spreadsheets.
2) When you share the tracker with other users, they can't search or sort!

December 10, 2005


Portable Mobile charger

Charge 2 Go is compact, light, reusable and easy to operate. One AA battery (included) and one connector (pick from offered phone types) allows you to charge your mobile phone anywhere. Talk whenever, wherever and for as long as you want.

December 09, 2005


Yahoo Answers

Yahoo Inc. unveiled a service that allows users to get their questions answered online for free by volunteers. The feature, called Yahoo Answers, gives a human touch to what has typically been the exclusive role of the Web portal's search engine.


sheet references!

There is a sheet called "shantanu" that contains the total amount of bill in the cell h5. There is another sheet called "Client X" that contains the total amount in the cell h5. You want to consolidate the amounts in the last sheet called "report".

Column A Column B
Shantanu 100
Client X 200
(100 and 200 are the values of the cell h5 from the respective sheets.)

If the value of cell A2 is shantanu that is same as the sheet name, then following formula will do the trick.

But what if we don't know if the bill amount is in cell h5 or h6 or h8?
We can assume that the value will be in either of the cells and the other cells will be always blank then we can assume that the total of the range h5 to h8 will be the correct amount of bill.
So in this case the formula will look like this...

December 07, 2005


Learn Ruby

Ruby is a programming language from Japan (available at which is revolutionizing the web. The beauty of Ruby is found in its balance between simplicity and power.

This is an incredibly cool tutorial, and a way of playing with Ruby without downloading any software. Start using it by typing the word help at the command prompt and then follow the instructions on the screen. (For e.g. type 2+6 to get the total as instructed)


Yahoo RSS to SMS / IM

Yahoo Alerts have been updated with a new feature that lets you get RSS results on your phone via SMS.


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?