getting url roots

[Wim Roffal]

I have a table with a collection of links. For simple duplicates I can check
with an index. But I want also check for more than one link from the same
domain.

So I tried the following:

SELECT name,count(*) AS dcount, SUBSTRING(url,0 ,(LOCATE('/', url,9))) AS
urlroot FROM complink GROUP BY urlroot

The problem I have is that "SUBSTRING(url, 0,(LOCATE('/', url,9)))" does not
work as desired: it always gives the same empty string. So I suspect that
the LOCATE in this position does not work and returns 0.

I would like to get some advise on:
- what goes wrong here
- how I can get the desired effect

Thanks,
Wim

[Aggro]

Wim Roffal wrote:

I have a table with a collection of links. For simple duplicates I can check
with an index. But I want also check for more than one link from the same
domain.

So I tried the following:

SELECT name,count(*) AS dcount, SUBSTRING(url,0 ,(LOCATE('/', url,9))) AS
urlroot FROM complink GROUP BY urlroot

The problem I have is that "SUBSTRING(url, 0,(LOCATE('/', url,9)))" does not
work as desired: it always gives the same empty string. So I suspect that
the LOCATE in this position does not work and returns 0.

Don't suspect, test it to find out. Broke your large query into smaller
peaces to see how those work, like this:

mysql> select locate('/', 'http://www.google.com/', 9);
+------------------------------------------+
| locate('/', 'http://www.google.com/', 9) |
+------------------------------------------+
| 22 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select SUBSTRING('http ://www.google.com/',0,22);
+------------------------------------------+
| SUBSTRING('http ://www.google.com/',0,22) |
+------------------------------------------+
| |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select SUBSTRING('http ://www.google.com/',1,22);
+------------------------------------------+
| SUBSTRING('http ://www.google.com/',1,22) |
+------------------------------------------+
| http://www.google.com/ |
+------------------------------------------+
1 row in set (0.00 sec)
And at this point, you should see what was wrong ;)

[Wim Roffil]

Hi Aggro,

Thanks for the tip!

Wim

"Aggro" <sp**********@y ahoo.com> schreef in bericht
news:q_******** *****@read3.ine t.fi...

Wim Roffal wrote:

I have a table with a collection of links. For simple duplicates I can check with an index. But I want also check for more than one link from the same domain.

So I tried the following:

SELECT name,count(*) AS dcount, SUBSTRING(url,0 ,(LOCATE('/', url,9))) AS
urlroot FROM complink GROUP BY urlroot

The problem I have is that "SUBSTRING(url, 0,(LOCATE('/', url,9)))" does not work as desired: it always gives the same empty string. So I suspect that the LOCATE in this position does not work and returns 0.

Don't suspect, test it to find out. Broke your large query into smaller
peaces to see how those work, like this:

mysql> select locate('/', 'http://www.google.com/', 9);
+------------------------------------------+
| locate('/', 'http://www.google.com/', 9) |
+------------------------------------------+
| 22 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select SUBSTRING('http ://www.google.com/',0,22);
+------------------------------------------+
| SUBSTRING('http ://www.google.com/',0,22) |
+------------------------------------------+
| |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select SUBSTRING('http ://www.google.com/',1,22);
+------------------------------------------+
| SUBSTRING('http ://www.google.com/',1,22) |
+------------------------------------------+
| http://www.google.com/ |
+------------------------------------------+
1 row in set (0.00 sec)
And at this point, you should see what was wrong ;)