SIte Search Engine

[Insite]

Hi

I am currently working on a site which visitors can submit & search links to various resources. I am trying to set up an advanced search feature (similar to google) and I have several ENUM Fields within the table that I want visitors to be able to check or uncheck within the search. I also have a username table and would like to be able to measure the popularity of a link so that I can have rankings for different types of resources and count the numbr of times a user submits a link, which user submits which link, number of times a user clicks on a link etc. I am v. new to database design and am not sure whether the ENUM fields should be in a seperate table or whether to keep it as it is:

CREATE TABLE `lnks` (
`lnk_id` int(11) NOT NULL auto_increment,
`lnk_name` varchar(45) NOT NULL default '',
`lnk_desc` text NOT NULL,
`lnk_url` varchar(120) NOT NULL default '',
`lnk_a1` enum('Yes','No') default 'No',
`lnk_a2` enum('Yes','No') default 'No',
`lnk_a3` enum('Yes','No') default 'No',
`lnk_a4` enum('Yes','No') default 'No',
`lnk_a5` enum('Yes','No') default 'No',
`lnk_a6` enum('Yes','No') default 'No',
`lnk_a7` enum('Yes','No') default 'No',
`lnk_a8` enum('Yes','No') default 'No',
`lnk_a9` enum('Yes','No') default 'No',
`lnk_a10` enum('Yes','No') default 'No',
`lnk_a11` enum('Yes','No') default 'No',
`lnk_a12` enum('Yes','No') default 'No',
`lnk_a13` enum('Yes','No') default 'No',
`lnk_a14` enum('Yes','No') default 'No',
`lnk_a15` enum('Yes','No') default 'No',
`lnk_a16` tinyint(127) NOT NULL default '1',
PRIMARY KEY (`lnk_id`),
UNIQUE KEY `lnk_id` (`lnk_id`)
) TYPE=InnoDB

It would also be v. helpful to know the best way to set up the 'advanced form' and the query/recordset i.e. the SQL Statement.


Hope you can help!?!

 

[BNPMike]

MySQL has a datatype BOOLEAN which is altogether more appropriate than using ENUM.

You need to explain what you mean by this:
set up the 'advanced form' and the query/recordset i.e. the SQL Statement.

MySQL doesn't have forms. You need something else to add that functionality.

When you write 'query/recordset' this sounds a bit like Microsoft Access/Jet. If this is the case, MySQL does not presently support views.

 

[Insite]

Hi

Thanks for replying - knew there would probably be a better way than ENUM! I am creating a php site using Dreamweaver & MySQL. The purpose of the site is to let people submit links to & search various media types and would like to set up an advanced search for the links similar to google - advanced search:

http://www.google.com/advanced_search?hl=en&lr=&ie=UTF-8&oe=UTF-8

But would like visitors to be able to use combinations of 'check' boxes and keywords to get the most appropriate media. As there are various different types of media everything from streaming to pdf and various different areas that the media cover I have been thinking up until now that using a something similar to the above link would work best. I would also like to be able to collect information about how many people use a link so that I can use a ranking system.

Thanks for your help!

Insite

 

[BNPMike]

From what you've said, you don't need a table with check box fields. They only appear in Dreamweaver/PHP. The PHP will examine the check boxes and then build a search query to extract records from your MySQL database of media entries.