Shantanu's Blog

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

1) goto the site http://saraswaticlasses.com
2) If you want to send me an email, type the command
send shantanuoak@yahoo.com 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 shantanuoak@yahoo.com

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

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

http://rss2pdf.com/

is the answer.

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

For RSS & Atom (Add 'img=1' for Images)
http://rss2pdf.com?url=http://www.yourdomain.com/rss/yourfeed.xml
Or use the bookmarklet available.

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

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.

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?

SELECT
domainTable.name,
sum(NetworkTraffic.download) as "Weekly",
(select sum(NetworkTraffic.download) 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"
GROUP BY domainTable.name
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?

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?


Answer:

CREATE TABLE one2twelve (
i INT NOT NULL PRIMARY KEY
);

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

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

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

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:

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

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

Any assistance is greatly appreciated... thanks.

I have created a table called t as:

CREATE TABLE t (
name varchar(7) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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.

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.


Answer:
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...
Code:

select foo
, bar
, ship_date
from orders
where status = 'cancelled'
and date_add(
concat('20'
, substring(ship_date,7,2)
, '-'
, substring(ship_date,4,2)
, '-'
, substring(ship_date,1,2) )
, interval 0 day)
between
date_add(current_date, interval -7 day)
and current_date
order
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?

Answer:
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.name, e.state
from table1 e, table2 n, table3 c
where e.client in c.client and n.client NOT IN e.client;


Answer:

The correct syntax is:

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


But I suspect that's not what you want.

Try:

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

or the equivalent without subqueries:

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

mobile_no field values are:

213-999-9888
818-111-7777
646-555-1212
818-989-0444
413-630-3333
646-999-8089
206-890-0000

npa_nxx field values are:

298-773
646-555
313-872
212-222
845-922

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

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

http://forums.devshed.com/t295830/s.html

getting most recent entries from 1 table

Hi,

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.

http://forums.devshed.com/t297083/s.html

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

http://forums.devshed.com/t297395/s.html

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

SELECT DISTINCT reviewer
FROM BookAuthor JOIN BookReview ON BookAuthor.book=BookReview.book
WHERE author='Elmasri';

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

http://forums.devshed.com/t298255/s.html

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 202.21.23.21
2005-09-08 wah-wah 202.21.23.22
2005-09-09 Blah-blah 202.221.213.221
2005-09-09 nah-nah 202.21.23.21

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

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 202.21.23.21
2005-09-08 wah-wah 202.21.23.22
2005-09-09 nah-nah 202.21.23.21


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(*)
202.21.23.21 2
202.21.23.22 1
202.221.213.221 1

Once I have the user_ip, for e.g. I have found there are 2 instances of 202.21.23.21, 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)
from
(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 202.21.23.21 2 Blah-blah,nah-nah
4 202.21.23.22 1 wah-wah
4 202.221.213.221 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)
from
(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 202.21.23.21 2 Blah-blah,nah-nah
3 202.21.23 3 nah-nah,wah-wah,Blah-blah

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.

_____


CREATE TABLE `bill` (
`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;

CREATE TABLE `bill` (
`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!

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.

http://www.alwayson-network.com/comments.php?id=11920_0_1_0_C

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

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."
http://www.google.com/reader

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.

http://online.thinkfree.com/

substring and substring index

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

neil@yahoo.co.uk
simon@gmail.com
barrySomething456_1@hotmail.com
...
...
etc

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?

http://forums.devshed.com/t294507/s.html

Order Order!!

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

AIYC 1
AIYC 10
AIYC 11
AIYC 12
AIYC 2
AIYC 3
...
AIYC 8
AIYC 9

I am looking for this:

AIYC 1
AIYC 2
AIYC 3
....
AIYC 8
AIYC 9
AIYC 10
AIYC 11
AIYC 12

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?


http://forums.mysql.com/read.php?10,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.

SELECT * FROM table ORDER BY 0+SUBSTRING(name, 6)

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

AIYC 1
AIYC 10
AIYC 2

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),
0+substr(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);

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

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;
DROP TABLE IF EXISTS names;
CREATE TABLE names (
name CHAR(50) NOT NULL
);

INSERT INTO names VALUES
("James Henry William"), ("John Paul"), ("Gert");

DROP TABLE IF EXISTS ints;
CREATE TEMPORARY TABLE ints (
id INT NOT NULL PRIMARY KEY
);
INSERT INTO ints VALUES (1), (2), (3), (4), (5);

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

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?

http://forums.devshed.com/t294248/s.html

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?

Substring

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

eg

CY-Limassol
ESP-Albacete
ESP-Alcudia
ESP-Alhaurin

i have copied an example from the documentation:

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

and i get

ESP
FRA
GR
HR

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

Cross Tab query

This is my scenario:

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

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

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.

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
}

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

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.

http://bettypowerpoint.blogspot.com/2005/10/create-photo-album-presentation-in.html