Yeah, it is poor design. I didn't think about selecting for one category when I made it, though. :/
If you have any better ideas, that'd be great.
The problem with a second table is that I then have to sort it by another (user defined) column.
The entire query is:
Code: Select all
SELECT {$select} FROM photos{$where} ORDER BY {$soorder} LIMIT {$limit},{$numphotos}
Where $where might be...
Code: Select all
WHERE favorite='1'
WHERE -- MATCH (categories) -- the one I'm working on
or nothing at all.
$soorder is what to order it by, which is usually something like...
Code: Select all
rating ASC, date DESC, uploaded DESC
$limit is calculated from $numphotos * ($page - 1)
The entire photos table right now is:
Code: Select all
CREATE TABLE `photos` (
`id` smallint(4) unsigned NOT NULL auto_increment,
`file` varchar(50) NOT NULL default '',
`width` smallint(4) unsigned NOT NULL default '0',
`height` smallint(4) unsigned NOT NULL default '0',
`name` varchar(70) NOT NULL default '',
`date` smallint(10) NOT NULL default '',
`info` text NOT NULL,
`keywords` text NOT NULL,
`uploaded` int(10) unsigned NOT NULL default '0',
`categories` varchar(30) NOT NULL default '',
`rating` float unsigned NOT NULL default '0',
`ratings` smallint(3) unsigned NOT NULL default '0',
`comments` smallint(3) unsigned NOT NULL default '0',
`downloads` smallint(4) unsigned NOT NULL default '0',
`favorite` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `uploaded` (`uploaded`),
KEY `file` (`file`),
FULLTEXT KEY `searchinfo` (`name`,`date`,`info`,`keywords`),
FULLTEXT KEY `categories` (`categories`)
) ;
and the category table is:
Code: Select all
CREATE TABLE `cats` (
`id` smallint(3) unsigned NOT NULL auto_increment,
`order` tinyint(2) unsigned NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
`photos` smallint(4) unsigned NOT NULL default '0',
`subcat` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `order` (`order`)
) ;
*cries*