SEO

Hi,
I'm trying to get a query working.
Basically I just want the list of tags per user and the number of
hits:
I have urls_userurl that have 2 foreign keys(to user table and url
table) and 1 many-to-many to tags table, with an auxiliary table
urls_userurl_tags:
I want something like, for user with id=3:
tag_name | count
test 4
mysql 43
....


mysql> describe urls_url;
+----------------+------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------+------------------+------+-----+---------
+----------------+
| id | int(11) | NO | PRI | NULL |
auto_increment |
| url | varchar(1000) | NO | UNI |
| |
| smallr_url | varchar(50) | NO | |
| |
| count | int(10) unsigned | YES | | NULL
| |
| rating | int(11) | YES | | NULL
| |
| hits | int(10) unsigned | YES | | NULL
| |
| first_username | varchar(100) | NO | |
| |
| first_title | varchar(255) | NO | |
| |
| created | datetime | NO | |
| |
+----------------+------------------+------+-----+---------
+----------------+
9 rows in set (0.00 sec)

mysql> describe urls_tag;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | | |
| count | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe urls_userurl;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | | |
| url_id | int(11) | NO | MUL | | |
| tags_tokens | longtext | YES | | NULL | |
| title | varchar(255) | NO | MUL | | |
| notes | longtext | YES | | NULL | |
| rating | int(11) | YES | | NULL | |
| ip | char(15) | YES | | NULL | |
| archive | tinyint(1) | NO | | | |
| created | datetime | NO | | | |
| updated | datetime | NO | | | |
+-------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> describe urls_userurl_tags;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| userurl_id | int(11) | NO | MUL | | |
| tag_id | int(11) | NO | | | |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Any idea?

Thanks,
Tyler

On Tue, 04 Dec 2007 12:18:36 +0100, tylersticky@gmail.com
<tylersticky@gmail.com> wrote:

> Hi,
> I'm trying to get a query working.
> Basically I just want the list of tags per user and the number of
> hits:
> I have urls_userurl that have 2 foreign keys(to user table and url
> table) and 1 many-to-many to tags table, with an auxiliary table
> urls_userurl_tags:
> I want something like, for user with id=3:
> tag_name | count
> test 4
> mysql 43

What is the count here? Just the field of urls_tag or something else? And
if so, what should this field reflect when a tag is matched to a user
multiple times (by different urls for instance)?
--
Rik Wasmus

On Dec 4, 2:45 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:

> What is the count here? Just the field of urls_tag or something else? And
> if so, what should this field reflect when a tag is matched to a user
> multiple times (by different urls for instance)?
> --
> Rik Wasmus

I think I've got there with:

select name,count(*) from (select c.name from urls_userurl a,
urls_userurl_tags b, urls_tag c where a.id = b.userurl_id and b.tag_id
= c.id and a.user_id = 3) as result group by name;

I don't know is a sub-select is needed.