SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    The Omnipresent [ArcanE]'s Avatar
    Join Date
    Mar 2005
    Location
    Belgium
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table name stored in column

    Is there a way to solve the following problem with mysql only?

    I have a tags table
    Code:
    CREATE TABLE `tags` (
      `id` int(11) NOT NULL auto_increment,
      `tag` varchar(255) default NULL,
      `quicklink` varchar(255) default NULL,
      `related_table` varchar(255) default NULL,
      `related_id` int(11) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    In this tags table i save the information which other record from another table relates to. For example for a record in the tags table i could have 'people' in the related_table field and '1015' in the related_id field. This would imply that this tag relates to record 1015 in the peoples table. Clear enough i guess?

    Now i want to generate a tagCloud, therefor i have the following query which works perfectly.
    Code:
    SELECT
    	t.tag, t.quicklink, COUNT(t.tag) AS cnt
    FROM
    	tags AS t
    GROUP BY
    	t.tag
    The problem now is that we need to add one extra field to all the related tables called 'status' which is either 1 or 0 and indicates wether the record is active or not. The results from the tagcloud query above should only contain the tags that are related to a record that has status = 1 and that is where my problem comes up.

    How can i limit the tagcloud query to only count tags of which the related item in the related table has status 1?

    Thanks for any help on this one!
    Webdevelopment : Skyrocket Concepts Inventis Web Architects
    Ain't got time for the future or the past.
    Live for the moment, make it last.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my advice: never store the name of a table or column in your own table

    i would completely redesign what you're doing

    there would still be one tags table, but where you have people, buildings, and vehicles tables, you would then also have peopletags, buildingtags, and vehicletags tables to represent the many-to-many relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •