create search option for local webpages by tagging a page

Question

Hello,

My actual question is how would I go about creating a autocomplete dropdown off tags that will refer to a particular page, like on this site.

I already have a autocomlete dropdown and it looks in the db field for a word starting with something% (wildcard),but that is just one word

What if you would have like 5 tags, comma seperated like tag1, tag2, tag3, etc, in one field that will refer to one particular page. How would you start the query looking for tags starting with the letter F for example, extract them and put them in the dropdownlist and also get the url from the url field at the same time ofcourse.

and also by typing two or more tags, the results should get more precise

These are just my own thoughts, I have not figured out what would be the best way to acomplish this.

EDIT I used the information below to cook something up like this. I still have to make a page with the search results, so this is by long not ready.

CREATE TABLE tag_targets (
  tag_target_id MEDIUMINT UNSIGNED NOT NULL auto_increment,
  tag_target_naam varchar(30) NOT NULL,
  tag_target_url varchar(255) NOT NULL,
  PRIMARY KEY  (tag_target_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE tags (
  tag_id MEDIUMINT UNSIGNED NOT NULL auto_increment,
  tag varchar(30) NOT NULL,
  PRIMARY KEY  (tag_id),
  UNIQUE KEY tag (tag)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE target_tag (
  tag_target_id MEDIUMINT UNSIGNED NOT NULL,
  tag_id MEDIUMINT UNSIGNED NOT NULL,
  FOREIGN KEY (tag_target_id) REFERENCES tag_targets(tag_target_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


$q = "SELECT t1.tag_target_url,t2.tag FROM tag_targets AS t1,tags AS t2, target_tag AS t3 WHERE t1.tag_target_id = t3.tag_target_id AND t2.tag_id = t3.tag_id AND tag LIKE '$queryString%' LIMIT 10";

thanks, Richard

Answer 1

I would probably not have the tags in a single field for that page (comma separated) but a "tags" table:

tags:
ID | name
---------
 1 | tag1
 2 | tag2
 3 | tag3
 4 | tag4


page_tags:
page_id | tag_id
----------------
      1 | 2 
      1 | 3
      2 | 1
      3 | 4
      3 | 3

you can query for any tags in a simple way, and get any matching pages as well.

For example, if the user types "tag3" it would match page 1 and 3, but "tag2, tag3" (or "tag3, tag2" - the order is irrelevant) would only match page 1.

Example SQL query for the above:

SELECT 
    page_tags.page_id
FROM
    page_tags INNER JOIN tags
    ON page_tags.tag_id=tags.id
WHERE
    tags.name IN ('tag3', 'tag2') 
GROUP BY
    page_id HAVING count(page_tags.tag_id) = 2

Answer 2

I would recommend to use jQuery for making autocomplete fields. It has a good interface for making them:

http://docs.jquery.com/Plugins/Autocomplete