Shantanu's Blog

Open Borders
Open Minds
Open Source
Open Office



GOT IT?

Did you know that you can create "protected" forms using Microsoft word?
Use this form for getting information from new job seekers, employees or customers.

1. Start Word
2. Insert a table with 2 columns and 5 rows.
3. Type Name in the first cell.
4. Click in the next cell and then select "Text form field" button on Forms toolbar. If the forms toolbar is not visible, right click on any toolbar and choose Forms from the context menu.


5. Right click on the newly added form field and choose Properties. Set the Maximum length to 56 or any number you prefer. Select Title Case as text format for the name field. Now click on "Add Help Text..." button to create a tooltip that will help the applicant. The help message will also appear on the status bar.
6. Type Address in the next cell.
7. Add a text form field as explained above in the next cell.
8. Type City in the next row and add Drop down form field instead of Text form field. Add the city choices for e.g. Mumbai, Pune, Delhi.
9. Type Gender and create two Check box fields. Male and female.
10. In the salary Expected field add the Text form field as you did in the first step, but select "Number" as type instead of text so that the applicant can not type any text in the salary field (only numbers). You can also choose the format of the number.
11. Finally select Tools - Protect document and add a password. (Make sure that the "Forms" check box is selected.

Save the document and send it to clients/ students.
Now you can expect to get the information in the right "FORM"!

viksoe.dk - GMail Drive shell extension:
GMail Drive enables you to save and retrieve files stored on your Gmail account directly from inside Windows Explorer. GMail Drive literally adds a new drive to your computer under the My Computer folder, where you can create new folders, copy and drag'n'drop files to.

Google Talk: "Google Talk enables you to call or send instant messages to your friends for free–anytime, anywhere in the world."

If you are looking for simple collaboration software for your organization, here is the easiest and very powerful tool available called "TiddlyWiki".

http://www.tiddlywiki.com/empty.html

Right click on the above link and choose 'Save link as...' or 'Save target as...'.
Create a new directory (for e.g. called 'todo') and the save the file. You can decide what to call it (but keep the .HTML extension). Do not use the File/Save command in your browser to save TiddlyWiki.

Now open the file from where you saved it.
Click on 'close all' link on the right.
Click on 'new tiddler' on the left.
Type the title and then the text. Type tags related to the topic. Click on 'done' to save.

You can also create a new journal by clicking on 'new journal' link.

Within the main story column you can click on bold links to read a linked tiddler. Click on italic links within tiddlers to create a new tiddler, or use the 'new tiddler' button in the main menu. You can edit the text of any tiddler by double-clicking on it (or selecting 'edit' from the toolbar),


_____

To delete a Tiddler, Select the entry from Timeline, choose Edit and then click on delete.
Do not delete the entries those you have not created since those may be necessary to run the software.
_____

WikiWOrd is nothing but two capital letters in a word. The word will be automatically hyperlinked. You can alternatively use the "New tiddler" link on the left navigation. To make a tiddler that doesn't have a WikiWord as its name, you can enclose the name in [[Shantanu Prabhakar Oak]]. You can disable a wikiword by preceding the WikiWord with a tilde (~). For example, JamesBond, JavaScript and TiddlyWiki.

To make quoted bits of text stand out, you can use BlockQuotes within your [[tiddler]]s, like this:

JeremyRuston said:
<<<
This is indented text.
<<<

Like BulletPoints and NumberedBulletPoints, you can have three different levels of BlockQuotes. Just [[edit]] this tiddler to see how it's done.

>level 1
>level 1
>>level 2
>>level 2
>>>level 3
>>>level 3
>>level 2
>level 1

Creating BulletPoints is simple.
* Just add an asterisk
** start the line with two asterisks
*** use three asterisks for three levels

It's easy to create NumberedBulletPoints.
# Use a single '#' at the start of each line
## within any bullets


Headers:

!Header 1
!!Header 2
!!!Header 3
!!!!Header 4
!!!!!Header 5


insert images:

[img[Fractal vegetable|fractalveg.jpg]]
(This curious vegetable is called 'Romanesque broccoli' and is one of [[my photos|http://www.flickr.com/photos/jermy/]])


Word Formatting:

''Bold''
==Strike==
__Underline__
//Italic//
@@highlight@@
@@color(green):green colored@@
@@bgcolor(#ff0000):color(#ffffff):red colored@@

You can divide a tiddler into
----
sections by typing four dashes on a line by themselves


You can also have monospaced blocks (useful for source code):
{{{
var winBot = winTop + winHeight;
if(posTop < winTop)
return(posTop);
else if(posBot > winBot)
{
if(e.offsetHeight < winHeight)
return(posTop - (winHeight - e.offsetHeight));
}}}

_____

TiddlyWiki has almost limitless possibilities:

* It works great as a documentation manager for products, software, etc.
* It can store little bits of information, reminders, and notes
* It makes a great FAQ page.
* Turn it into a todo list, with items as tiddlers.
* Some use it as a blog.
* Some use it as a website.
* Make it your own personal dictionary/encyclopedia.

selecting into with substrings:
I have data in a text field such as
rank1 type1 name1
rank1 type2 name2
rank2 type1 name3
rank3 type 3 name 4

What I would like to do is break it apart so that the rank and type data are in separate columns. For the ranks, it wasn't so hard to do, I just did an

UPDATE table SET rank=rank1 WHERE text=LEFT(rank1,5)

for each of the ranks.

The next step is where I'm starting to run into problems. I need to update the text field to remove the rank information, and then do the same process with the types.
Any ideas on how to update the text field to remove the types?
Any ideas on how to do both steps at once?

MySQL Forums :: Newbie :: Find double (not uniqe) entries in a table: I've got a table that looks like this (offcourse, the real one is much bigger):

id| name | city
1 | john | 19
2 | bella | 11
3 | john | 12
4 | mike | 06
5 | alex | 12
6 | simon | 19
7 | alex | 11

.. and I want to select the 'id', 'name' and 'city' of those entries that the field
'name' isn't uniqe, in this case, john and alex.

How should my sql-query look like? The sql statements to recreate the table are...

CREATE TABLE `mydupes` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`city` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `mydupes` VALUES ( '1', 'john', '19');
INSERT INTO `mydupes` VALUES ( '2', 'bella', '11');
INSERT INTO `mydupes` VALUES ( '3', 'john', '12');
INSERT INTO `mydupes` VALUES ( '4', 'mike', '6');
INSERT INTO `mydupes` VALUES ( '5', 'alex', '12');
INSERT INTO `mydupes` VALUES ( '6', 'simon', '19');
INSERT INTO `mydupes` VALUES ( '7', 'alex', '11');



Answer:

The simple way to find out the duplicates is as follows:

SELECT id, name, city, count(*) AS cnt
FROM mydupes
GROUP BY name
HAVING cnt > 1

id name city cnt
5 alex 12 2
1 john 19 2

_____

But if you want all records of alex and john
then you need a subquery joined by IN or EXISTS.

id name city
1 john 19
3 john 12
5 alex 12
7 alex 11

SELECT id, name, city FROM mydupes
WHERE name IN (SELECT name
FROM mydupes
GROUP BY name
HAVING COUNT(name) > 1)

SELECT id, name, city FROM mydupes as t1
WHERE EXISTS(SELECT name
FROM mydupes t2
WHERE t2.name = t1.name
GROUP BY t2.name
HAVING COUNT(t2.name) > 1)

_____

Assuming id is always unique, there is another way out.

SELECT id, name, city FROM mydupes t1
WHERE EXISTS (SELECT NULL
FROM mydupes t2
WHERE t1.name = t2.name AND t1.id <> t2.id)

If id by itself is not unique, you can add the city field too:
SELECT id, name, city
FROM mydupes t1
WHERE EXISTS (SELECT NULL
FROM mydupes t2
WHERE t1.name = t2.name AND (t1.id <> t2.id OR t1.city <> t2.city))

MySQL Forums :: Newbie :: Merge Tables ?:
Has anyone tried to merge two tables in MySQL ? It seems to be a headache !

What I have are two tables that I want to merge into one.

Lets take an example ...

TABLE A
-----------
|NAME|TOWN|
------------------------
1.|PIET | KLAWER |
2.|JOHN | PARYS |
3.|KOOS| | DURBAN|

TABLE B
----------
TOWN| PCODE|
-----------------
1.KLAWER|1212|
2.PARYS|1234
3.DURBAN|4000

Ok, assuming you understand and my Graphics turn out OK,

I want to now create TABLE C - Which should look like ..

TABLE C
-----------
|NAME|TOWN|PCODE
------------------------
1.|PIET | KLAWER |1212
2.|JOHN | PARYS |1234
3.|KOOS| | DURBAN|4000


Any help will be much appreciated !

MySQL Forums :: Newbie :: Avoid couples in the resultset:
I use a query like this:
SELECT t1.id_topics, i1.id_indices, t2.id_topics
FROM indices i1, indices i2, topics t1, topics t2
WHERE (i1.ind = 'test' AND i1.id_urls = t1.id_urls
AND i2.id_urls = t2.id_urls AND i1.ind = i2.ind
AND t1.id_topics <> t2.id_topics);

it gives results like those:
'id_topic1','id_indices','id_topic2'
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the 'inverted' mates of couples in the resultset. That means in the example
'36,682,37' should be eliminated since '37,682,36' is already part of the resultset. Is it possoble to express this request in the query?

MySQL Forums :: Newbie :: Pulling my hair out with this query:
I have a PHP/MySQL database that has student grade data. Every student has at least one row of data, though they may have up to 3 rows depending on how many years they have been enrolled (each year gets one row).

Here is a very simplified view:

NAME|YEAR|TEST1|
------|------|-------|
John-|2002|---80|
Anna-|2002|---67|
John-|2003|---78|
Bill---|2003|---76|
Anna-|2003|---77|
John-|2004|---88|
Bill---|2004|---77|
Phil--|2004|---76|

*Note that John is there for all 3 years, Anna for only the first two, Bill for only the last 2, and Phil came in only in the last year.

The data needs to be displayed on the web page ordered by name with the 3 years in columns exactly like this:

Name|2002|2003|2004|
------|------|------|------|
Anna-|--67|--77--|null|
Bill---|null-|--76--|--77|
John-|--80-|--78-|--88|
Phil--|null-|null---|--76|

Currently I created 4 dataset queries like this:

'Select distinctrow NAME from mydata ORDER BY NAME'; //returns all names
'SELECT * FROM mydata WHERE `YEAR` = 2002 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2003 ORDER BY NAME';
'SELECT * FROM mydata WHERE `YEAR` = 2004 ORDER BY NAME';

I then use the result set to populate the columns.

The problem is that since not every student has data in all 3 years, the columns begin to shift and the data is placed in the incorrect row like this:

Name|2002|2003|2004|
-------|-----|------|-----|
Anna-|--67-|--77-|--77|
Bill---|--80-|--76-|--88|
John-|------|--78-|--76|
Phil--|------|null--|----|

Is there a way to create ONE query to return data for all of the students, but insert null values where there is no data? Is this done through a 'join' statement? What's the best approach?

Please help, I'm pulling my hair out!

A New World Economy: "As Cisco's Scheinman puts it: 'We came to India for the costs, we stayed for the quality, and we're now investing for the innovation.'"

I have a student Table and a SubjectGrade table

Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)

Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)

let's say have following record in SubjectGrade

1 Maths A
1 Phys B
1 Chem A
2 Maths A
2 Chem A
3 Bio C
3 Chem A

I want to find out students who have got A in both Maths and Chem
How the SQL look like?

I am using mysql 4.0.25 with no subquery support.

I'm having difficulty writing a query as follows. I'm unsure if I need a
subquery, a union or if this isn't actually possible without using
temporary tables.

I have two tables for members.

Table 1 (members) Table 2 (payments)
+----------+------------+ +---------+--------------+
| memno | group | | memno | payment_type |
+----------+------------+ +---------+--------------+
| 1 | a | | 1 | cash |
| 2 | b | | 2 | cash |
| 3 | a | | 3 | creditcard |
| 4 | a | | 4 | check |
| 5 | c | | 5 | creditcard |
... ...

I'd like a query that returns the total number in each group, together
with the number paying by credit card.

Obviously I can build two queries, and use a temporary table, but is
there a way to get a table like that below in a single query?

+----------+------------+------------------+
| group | members | pay_by_card |
+----------+------------+------------------+
| a | 5 | 3 |
| b | 26 | 18 |
...

This will be using MySQL 5 if that helps

I'm searching a table of people who own properties, and I want to also include the total count of related properties, and the count of related properties whose (status is 'Active' and approval is 'Active'). I've got:

select accounts.name, count(properties.property_id) as totalcount
from accounts, properties
where accounts.account_id=properties.account_id
group by accounts.account_id;

Works fine. Now I just need to figure out how to add that second count of property records meeting the two conditions. Anyone?

I have two tables: news and comments.

i want to count the number of comments for each newsid and update the
count in news.comment

comments.newsid belongs to news.id

can i do this with sub queries? im using v4.1.x
i have many records in the comments table so im not sure what the most
efficient way to do it.

i will likely run this as update every few minutes because doing a join
on the fly is going to hurt performance when i need to display the
comment count on the webpage i think.

any help?

table: news
+----+-----------+
| id | comments |
+----+-----------+
| 26 | 0 |
| 21 | 0 |
| 29 | 0 |
+---------------

table: comments
+-----+--------+
| id | newsid |
+-----+--------+
| 1 | 26 |
| 2 | 21 |
| 3 | 29 |
| 4 | 29 |
| 5 | 29 |
+-------------+

I have a table name "p" like that:

mysql> select * from p;
+-------+-------+--------+
| id | price | vendor |
+-------+-------+--------+
| OG012 | 40 | a |
| OG012 | 20 | b |
| OG012 | 20 | c |
| OG013 | 40 | c |
+-------+-------+--------+

and I have another table name q:
mysql> select * from q;
+--------+--------+
| vendor | rating |
+--------+--------+
| a | 1 |
| b | 2 |
| c | 3 |
+--------+--------+

My question is how do I find the minimal price for each id and vendor rating is highest.

I wish my result is:

+-------+-------+--------+
| id | price | vendor |
+-------+-------+--------+
| OG012 | 20 | b |
| OG013 | 40 | c |
+-------+-------+--------+

Since vender b and c are the same price for id(OG012), but vendor b has minimal rating then vendor c.

In which order does MySQL store the data? When I insert a new record does it gets stored at the end?

No. You can not control the way MySQL stores the records. You cannot expect your results to be ordered wihout an explicit ORDER BY clause. Without ORDER BY, mysql is free to return rows in any order it chooses (generally in the order they are found). For example:

CREATE TABLE atable (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
acol CHAR(3), bcol INT, ccol CHAR(3),
INDEX an_idx (acol, bcol, ccol)
);

INSERT INTO atable (acol, bcol, ccol)
VALUES ('aaa',1,'aaa'), ('bbb',1,'aaa'), ('aaa',3,'aaa'),
('aaa',2,'abc'), ('aaa',1,'bbb'), ('bbb',3,'abc'),
('aaa',2,'aaa'), ('aaa',1,'abc'), ('abc',3,'def'),
('bbb',2,'aaa');

DELETE FROM atable WHERE bcol = 3;

INSERT INTO atable (acol, bcol, ccol)
VALUES ('ccc',1,'aaa'), ('ccc',2,'aaa'), ('ccc',1,'bbb');

SELECT pk, acol, bcol, ccol FROM atable;
+----+------+------+------+
| pk | acol | bcol | ccol |
+----+------+------+------+
| 1 | aaa | 1 | aaa |
| 2 | bbb | 1 | aaa |
| 13 | ccc | 1 | bbb |
| 4 | aaa | 2 | abc |
| 5 | aaa | 1 | bbb |
| 12 | ccc | 2 | aaa |
| 7 | aaa | 2 | aaa |
| 8 | aaa | 1 | abc |
| 11 | ccc | 1 | aaa |
| 10 | bbb | 2 | aaa |
+----+------+------+------+
10 rows in set (0.00 sec)

You see? Without ORDER BY, the rows are returned as they are found. Note that rows from the second INSERT are where the deleted rows were.

An exception may occur if you only ask for columns which are covered by a single index. In that case, mysql may choose to read the data from the covering index, rather than from the table. Then you'd get results in index order:

mysql> SELECT pk FROM atable;
+----+
| pk |
+----+
| 1 |
| 2 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
| 13 |
+----+
10 rows in set (0.00 sec)

mysql> SELECT acol, bcol, ccol FROM atable;
+------+------+------+
| acol | bcol | ccol |
+------+------+------+
| aaa | 1 | aaa |
| aaa | 1 | abc |
| aaa | 1 | bbb |
| aaa | 2 | aaa |
| aaa | 2 | abc |
| bbb | 1 | aaa |
| bbb | 2 | aaa |
| ccc | 1 | aaa |
| ccc | 1 | bbb |
| ccc | 2 | aaa |
+------+------+------+
10 rows in set (0.00 sec)

I wouldn't count on this though. If you need ordered results, you need ORDER BY.

The indexed records are always stored in ascending order.
Note the last line of this excerpt from The Fine Manual :-)
http://dev.mysql.com/doc/mysql/en/create-index.html

> An index_col_name specification can end with ASC or DESC. These keywords
> are allowed for future extensions for specifying ascending or descending
> index value storage. Currently they are parsed but ignored; index values
> are always stored in ascending order.

Angel Investor News.com: "Many times I want to strangle the writer to simply tell me what they do in five words or less."

I have a table candidateSkills where I store the skillName and skillDuration. The table looks like:

skillid | ReumeId| skillName | skillDuration |
---------------------------------------------
1. | 1 | java | 32 |
2. | 1 | jsp | 43 |
3. | 1 | oracle | 34 |
4. | 2 | jsp | 48 |
5. | 2 | oracle | 83 |
6. | 3 | jsp | 67 |

I want to query such that I get the resumeid where the skillName is jsp and the skill duration is > 10
and the skill name is oracle and skillDuration is > 15.

While I was using oracle, the sql query that worked was

SELECT ResumeId from candidateSkills where skillName = 'jsp' AND
skillDuration > 10
INTERSECT
SELECT ResumeId from candidateSkills where skillName = 'oracle' AND
skillDuration > 15

The output was [1, 2]

But, I see that mysql does not support intersect. How do I go about ?

I'd like to know a way to know which users had a birthday in last 7 days...

Example, I've this:

Name - Born
John - 1963-12-24
Lisa - 1979-11-23
Peter - 1981-08-01

Then, script should return "Peter, 1981-08-01"

The php script will be execd through cron once a week, and no matter which day it is executed, it must give the same result...

How could I do a query to acchieve this ?