Need help with a weird SELECT...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Need help with a weird SELECT...

Post by Skara »

Ok, I give up. I have a table that lists photos. One row is a varchar of category ids. In other words...

Code: Select all

CREATE TABLE `photos` (
  `id` smallint(4) unsigned NOT NULL auto_increment,
  -- stuff
  `categories` varchar(30) NOT NULL default '',
  -- more stuff
  FULLTEXT KEY `categories` (`categories`)
) ;
Where the categories field might contain something like this:

Code: Select all

-1- -3- -8- -4-
or
1;3;8;4;
or
1 3 8 4
-- I've tried each with no results. :/

Anyway, I wan't to display all photos from a certain category. Haha, I know I didn't plan to well for that. This is what I've tried (to no effect):

Code: Select all

SELECT * FROM photos WHERE MATCH (categories) AGAINST ('$cat');
ERRN, no results.

I know the best thing would be to have a row for each category, but the categories are rather dynamic themselves, and there are a lot of them.

Anyone have a solution?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sounds like poor design, no offense. You should have a secondary table that has 2 fields: photo id, category id. I bet you can guess what does in it. :)
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

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*
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I was talking about creating a many-to-many relational table for you to use. Instead of a finite number of associations a photo may have using your system, the m2m table allows infinite numbers of associations.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Need help with a weird SELECT...

Post by Weirdan »

Skara wrote:

Code: Select all

SELECT * FROM photos WHERE MATCH (categories) AGAINST ('$cat');
ERRN, no results.
Fulltext search works with 3-character (minimum) keywords. You can use regexp instead:

Code: Select all

select 
  * 
from 
  `photos` 
where
  `categories` regexp '[[:<:]]$cat[[:>:]]'
but I would urge you to follow feyd's suggestion and implement many-to-many relation between photos and categories tables. Later I'll post a script to create relation table using your existing tables (it's just two queries, but I need to experiment a bit).
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Weirdan wrote: Later I'll post a script to create relation table using your existing tables (it's just two queries, but I need to experiment a bit).
ok, here it is:

Code: Select all

create table `photos_categories`
select distinct
  `photos`.`id` as `photo_id`,
  `categories`.`id` as `category_id`
from
   `photos`
inner join
   `categories`
on `photos`.`categories` regexp concat('[[:<:]]', `categories`.`id`, '[[:>:]]');

alter table `photos_categories` add unique (
`photo_id` ,
`category_id`
);
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

hm. Ok, using the separate table... How would I go about ordering it by a row in the photo table and only selecting x number or columns? Or selecting from the photo table where a it matches in the photos_categories table?
I need something sorta like...

Code: Select all

SELECT * FROM photos WHERE ($photoid and $categoryid are in a photos_categories row) ORDER BY date DESC;
or, if I can't do that...

Code: Select all

SELECT photo_id FROM photos_categories WHERE category_id='$categoryid' ORDER BY date DESC (in the photos table, where photo_id=photos.id);
I'm thinking I can do it somehow with a JOIN, but I don't know how.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

Nevermind. After much trial and error, I got the following:

Code: Select all

LEFT JOIN photos_cats ON photos.id=photos_cats.photo_id WHERE photos_cats.category_id='$cat'
*shoo!* Glad that's done! ^_^

Thanks for the help. I think I might better plan my tables from the start next time... >.>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

fyi, you can have multiple joining conditionals, which may optimize your query a bit more

Code: Select all

LEFT JOIN photos_cats ON photos.id=photos_cats.photo_id AND photos_cats.category_id='$cat'
Post Reply