Shantanu's Blog

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 t1.id = t2.id;

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 t1.id = t2.id;

http://www.informit.com/articles/article.asp?p=30875&seqNum=6

An interesting blog post is published here...
http://rpbouman.blogspot.com/

>> 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.

Evolving:
>> 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.

PHP:
>> 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!

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

Fast:
>> and how fast it works!

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

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

http://tinyurl.com/8hb5q

Header ("Content-type: image/png");
$img_handle = imageCreateFromPNG("http://www.icemelon.com/images/tutorials/bannerboy.png");
$color = ImageColorAllocate ($img_handle, 100, 100, 100);
$ip = $_SERVER['REMOTE_ADDR'];
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.

http://www.icemelon.com/tutorials/9/Dynamic_Images.htm

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?

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?

Protein_Complexes
ProteinName ComplexesID
YNL031C 123
YWN078C 123
YNL031C 412
YNL031C 745
YWC234C 745
YNL009W 745

http://forums.mysql.com/read.php?10,60579,60579#msg-60579

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.

http://forums.mysql.com/read.php?10,60539,60539#msg-60539

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 company.id = cclink.company_id AND contact.id = cclink.contact_id)
- companies which don't have any contacts
(SELECT * FROM company WHERE company.id NOT IN (SELECT cclink.company_id FROM cclink)
- contacts which don't have any companies
(SELECT * FROM contact WHERE contact.id NOT IN (SELECT cclink.contact_id FROM cclink)

Is it possible to conect those 3 queries in one query?

http://forums.mysql.com/read.php?10,60349,60349#msg-60349

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON test.* TO testuser2@'%';

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

SELECT SESSION_USER(), CURRENT_USER();
+---------------------+----------------+
| SESSION_USER() | CURRENT_USER() |
+---------------------+----------------+
| 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 (%).

http://www.futhark.ch/mysql/116.html

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."

http://www2.cio.com/higher/report3687.html

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 http://pastebin.com/1234
* 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"

http://pastebin.com/

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

Sample data:
eventDate
2005-11-01
2005-11-01
2005-11-02
2005-11-02
2005-11-11
2005-10-30
2005-10-29

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?
http://forums.devshed.com/t304675/s.html

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

1 a.u./c
56*78
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 (site:.gov) and .edu (site:.edu)
site:www.somewebsite.com
site:somewebsite.com
site:.com
You can also have it restrict the search to a directory on that server: site:somewebsite.com/foo/

You can use lyrics: and movie: operator to find music info.
lyrics:
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

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

http://shortcuts.search.yahoo.com

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

Search
!audio http://audio.search.yahoo.com/search/audio

Application
!note http://notepad.yahoo.com/?v=161&DESC=%s
!mail http://compose.mail.yahoo.com/ym/Compose?login=1&To=%s

How to use?
!audio madonna
!note this is a test note to be saved.
!mail shantanu@yahoo.com

If you want to use your favorite yubnub commands, create a shortcut for yubnub.org and save it in your account.
First login to your yahoo account and then type this command in Yahoo Search box.
!set yb http://yubnub.org/parser/parse?command=%s

OR
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

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 blogger.com) 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!

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."

--http://www.wired.com/wired/archive/13.12/neopets.html

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.

http://forums.mysql.com/read.php?10,52997,52997#msg-52997

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';

http://forums.mysql.com/read.php?10,52734,52734#msg-52734

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(goals.id) 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

http://forums.mysql.com/read.php?10,52632,52632#msg-52632

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:

SUM(rating.ratingvalue)

http://forums.mysql.com/read.php?10,53037,53037#msg-53037

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)

http://forums.mysql.com/read.php?10,53031,53031#msg-53031

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
----------- -----------
Jack.........Danials
Jim..........Smith

http://forums.mysql.com/read.php?10,53199,53246#msg-53246

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.

http://dev.mysql.com/books/hpmysql-excerpts/ch07.html

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."

http://reviews.zdnet.co.uk/software/os/0,39024180,39237495,00.htm

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.

http://www.pxn8.com/index.pl

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.

http://tinyurl.com/cotwc

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

http://www.talkr.com/index.html

The another one is called Feed2Podcast
http://feed2podcast.com
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.
http://feeds.feedburner.com/TheDistantLibrarianBroughtToYouByFeed2podcastcom

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.

http://dev.mysql.com/tech-resources/articles/mysql-data-integrity.html

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

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.

http://opmlworkstation.com/

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.

http://www.respondus.com/products/index.shtml

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.

http://www.rondebruin.nl/csv.htm

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!

http://tracker.jot.com/login/

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.

http://www.chargetogo.com/product.htm

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.
http://answers.yahoo.com/

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.
=INDIRECT((CONCATENATE(A2,"!h5")),TRUE)

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...
=SUM(INDIRECT((CONCATENATE(A3,"!h5:h8")),TRUE))

Ruby is a programming language from Japan (available at ruby-lang.org) which is revolutionizing the web. The beauty of Ruby is found in its balance between simplicity and power.
http://tryruby.hobix.com/

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 Alerts have been updated with a new feature that lets you get RSS results on your phone via SMS.

http://alerts.yahoo.com/