Re: newbie SELECT question

2003-10-01 Thread Michael Johnson 
How about this?

SELECT
  SUBSTRING_INDEX(SUBSTRING(url, LOCATE("//", url) + 2), '/', 1) AS domain
FROM referals
Michael

On Wed, 1 Oct 2003 14:54:24 +0100, Graham Nichols 
<[EMAIL PROTECTED]> wrote:

Hi,

I have a table containing page referral URLs gleaned from users browsing 
my website. Is there a way for me to use SELECT based on a portion 
record's contents?

eg

table contents:

http://www.yahoo.com/adirectory/apage.htm
http://google.net/adirectory/anotherpage.php
I wish to return only the portion between the // and /. Sort of a //*/ 
thing so I can use COUNT with this to allow me to build a table in PHP 
of the most popular root domains which go to my site.

I hope this all makes sense ;-)

kind regards,  Graham Nichols.


--
Michael Johnson < [EMAIL PROTECTED] >
Internet Application Programmer, Pitsco, Inc.
620-231-2424x516
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: newbie SELECT question

2003-10-01 Thread Percy Williams 
Could look at instr?

> -Original Message-
> From: Graham Nichols [mailto:[EMAIL PROTECTED]
> Sent: 01 October 2003 14:54
> To: [EMAIL PROTECTED]
> Subject: newbie SELECT question
> 
> Hi,
> 
> I have a table containing page referral URLs gleaned from users
browsing
> my website. Is there a way for me to use SELECT based on a portion
> record's contents?
> 
> eg
> 
> table contents:
> 
> http://www.yahoo.com/adirectory/apage.htm
> http://google.net/adirectory/anotherpage.php
> 
> I wish to return only the portion between the // and /. Sort of a //*/
> thing so I can use COUNT with this to allow me to build a table in PHP
of
> the most popular root domains which go to my site.
> 
> I hope this all makes sense ;-)
> 
> kind regards,  Graham Nichols.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie SELECT question

2003-10-01 Thread Graham Nichols 
Hi,

I have a table containing page referral URLs gleaned from users browsing my website. 
Is there a way for me to use SELECT based on a portion record's contents?

eg

table contents:

http://www.yahoo.com/adirectory/apage.htm
http://google.net/adirectory/anotherpage.php

I wish to return only the portion between the // and /. Sort of a //*/ thing so I can 
use COUNT with this to allow me to build a table in PHP of the most popular root 
domains which go to my site.

I hope this all makes sense ;-)

kind regards,  Graham Nichols.

Re: Newbie SELECT Question

2003-08-17 Thread Ivan Cukic 

Steve> select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and
Steve> VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs');
Steve> Empty set (0.00 sec)

Just analyze the query. You asked for a record in which
name = 'FavoriteSport' AND name = 'FavoriteFood' which is always false
because name can not be both 'FavoriteSport' and 'FavoriteFood' at the
same time.

One of the possible solutions (not so good) is this:

select a.entity from attribute a, attribute b where a.entity =
b.entity and a.NAME='FavoriteSport' and
a.VALUE='Soccer' and b.NAME='FavoriteFood' and b.VALUE='CornDogs';



or you can upgrade to 4.1 and do this

select entity from attribute where NAME='FavoriteSport' and
VALUE='Soccer' and entity in (select entity from attribute where
NAME='FavoriteFood' and VALUE='CornDogs');


in the near future (i think in 5.x tree) you will be able to intersect
two queries

select ... INTERSECT select ...;

select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and
VALUE='Soccer' INTERSECT select ENTITY from ATTRIBUTE where
NAME='FavoriteFood' and VALUE='CornDogs';



Or you can do it through script or something like that

   Ivan

__

One World, one Web, one Program
-- Microsoft promotional ad 

Ein Volk, ein Reich, ein Fuhrer 
-- Adolf Hitler 
__
http://alas.matf.bg.ac.yu/~mr02014
   ___ _ _ _ __ ___  _
  / __/___ __     | __| _  _ ___  \
 / _/ / . / _\/\  | _| \ \/ / ._\  Ivan Cukic, Form Eye 2003.  \
/_/  /___/_/ /_/_/_/  |___|_\  /\___>  web development and design  /
  <__ /   _ _ __ ___  /



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie SELECT Question

2003-08-17 Thread Hans-Peter Grimm 
Steve Cote wrote:
We are having problems with what we think is a simple select statement:

select ENTITY from ATTRIBUTE
  where (NAME='FavoriteSport' and VALUE='Soccer')
and (NAME='FavoriteFood' and VALUE='CornDogs');
You are trying to find a row with a NAME value of 'FavoriteSport' and 
'FavoriteFood' at the same time, also with a VALUE value of 'Soccer' and 
'CornDogs' at the same time. This can't work.

Here's a query that does what you want:

SELECT a1.ENTITY FROM ATTRIBUTE a1, ATTRIBUTE a2
WHERE a1.ENTITY = a2.ENTITY
  AND a1.NAME='FavoriteSport' and a1.VALUE='Soccer'
  AND a2.NAME='FavoriteFood' and a2.VALUE='CornDogs'

[...]
Finally, let's try to get a list of entities that have both a favorite 
sport of Soccer and a favorite food of CornDogs with just one query:

mysql> select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and 
VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs');
Empty set (0.00 sec)

This isn't what we expect. We should see ENTITY 118 appear in the result 
list
since the previous two queries returned ENTITY 118. What single query will
return just the records that both sets (NAME & VALUE) of tests?
[...]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie SELECT Question

2003-08-17 Thread Steve Cote 
We are having problems with what we think is a simple select statement:

select ENTITY from ATTRIBUTE
  where (NAME='FavoriteSport' and VALUE='Soccer')
and (NAME='FavoriteFood' and VALUE='CornDogs');
First, we are running an older version of MySQL:

mysql> select version();
+--+
| version()|
+--+
| 3.23.22-beta-log |
+--+
1 row in set (0.00 sec)
First, we create a table:

CREATE TABLE ATTRIBUTE
(
  ENTITY int(10) unsigned NOT NULL,
  NAME varchar(64) NOT NULL,
  TYPE varchar(8),
  VALUE text,
  UNIT varchar(12),
  READONLY char(1),
  ENTERED timestamp(14),
  UPDATED timestamp(14),
  PRIMARY KEY (ENTITY,NAME)
);
Next we populate it so it contains the following data:

mysql> select ENTITY, NAME, VALUE from ATTRIBUTE;
++---+-+
| ENTITY | NAME  | VALUE   |
++---+-+
|128 | Age   | 7   |
|128 | FavoriteFood  | Sandwich|
|128 | FavoriteSport | Tennis  |
|127 | Age   | 5   |
|127 | FavoriteFood  | Peanuts |
|127 | FavoriteSport | Hockey  |
|125 | FavoriteFood  | Tacos   |
|125 | Age   | 7   |
|125 | FavoriteSport | Lacrosse|
|124 | FavoriteFood  | Hamburgers  |
|124 | Age   | 8   |
|124 | FavoriteSport | Soccer  |
|122 | FavoriteSport | Tennis  |
|122 | Age   | 7   |
|122 | FavoriteFood  | Sandwich|
|118 | FavoriteSport | Soccer  |
|118 | Age   | 6   |
|118 | FavoriteFood  | CornDogs|
|119 | FavoriteSport | Swimming|
|119 | Age   | 8   |
|119 | FavoriteFood  | Salad   |
|121 | FavoriteSport | Hockey  |
|121 | Age   | 5   |
|121 | FavoriteFood  | Ice Cream   |
++---+-+
24 rows in set (0.01 sec)
Now we want a list of entities that have an attribute named 'FavoriteSport'
and a value of 'Soccer':
mysql> select ENTITY from ATTRIBUTE where NAME='FavoriteSport' and VALUE='Soccer';
++
| ENTITY |
++
|124 |
|118 |
++
2 rows in set (0.01 sec)
Now get a list of entities that have an attribute named 'FavoriteFood' and a 
value of 'CornDogs':

mysql> select ENTITY from ATTRIBUTE where (NAME='FavoriteFood' and 
VALUE='CornDogs');
++
| ENTITY |
++
|118 |
++
1 row in set (0.01 sec)

Apparently ENTITY 118 has both a favorite sport of Soccer and a favorite food
of CornDogs because it appears on the result list for each of the queries.
Finally, let's try to get a list of entities that have both a favorite sport 
of Soccer and a favorite food of CornDogs with just one query:

mysql> select ENTITY from ATTRIBUTE where (NAME='FavoriteSport' and 
VALUE='Soccer') and (NAME='FavoriteFood' and VALUE='CornDogs');
Empty set (0.00 sec)

This isn't what we expect. We should see ENTITY 118 appear in the result list
since the previous two queries returned ENTITY 118. What single query will
return just the records that both sets (NAME & VALUE) of tests?
-Steve



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]