attache more categories to one post

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
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

attache more categories to one post

Post by spamyboy »

I have table movies, which contains (title, movie_categories)
could someone give me example HOW should I construct mysql that I be able to attache multy categories to one entery.
now here is how does it look for now

Code: Select all

CREATE TABLE `movies` (
  `movieID` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `categories` varchar(255) default NULL,
  UNIQUE KEY `catid` (`movieID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Code: Select all

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL default '0',
  `category_name` varchar(255) NOT NULL default '0',
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Code: Select all

CREATE TABLE `categories_relations` (
  `movie_id` int(11) NOT NULL default '0',
  `category_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
But I'm totaly lost, pleas give me example of mysql structure and how to use.
If possible (if know any) tutorial would be great.
I searched internet all night but havent found nothing exatly that I need.
(maybe I was searching for wrong stuff)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

To do multiple categories you either need a table to act as a middle-man, or use serialized objects / arrays. The serialization approach should be obvious. The middle-man table is structured as you have made it, but the other two are incorrect. You want:

Movies: `id`, `title`
Categories: `id`, `name`
Relationship: `movieId`, `categoryId`

You create the relationship through the middle-man table.

Code: Select all

SELECT `movies`.`title`, `categories`.`name` FROM `relationship` LEFT JOIN (`movies` CROSS JOIN `categories`) ON (`movies`.`id` = `relationship`.`movieId` AND `categories`.`id` = `relationship`.`categoryId`) WHERE `categories`.`movieId` = $id;
Of course, you would end up getting the title multiple times, so you may which to either query for it separately, or just only use it once.

Code: Select all

$result = mysql_query($query);
$data = mysql_fetch_object($result);

if($data)
{
    $title = $data->title;

    do
    {
        // whatever
    } while($data = mysql_fetch_object());
}
else
{
    // No records
}
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Post by spamyboy »

I done everything as you have said, but I get:

Code: Select all

mysql_fetch_object(): supplied argument is not a valid MySQL result resource
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

And do you know what that means? It's an obvious error.

And please don't tell me you just copied and pasted that code. It was an example.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Post by spamyboy »

I Thought that example should work with

Code: Select all

Movies: `id`, `title`
Categories: `id`, `name`
Relationship: `movieId`, `categoryId`
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The example is to give you an idea of how it is supposed to work. I haven't created these tables or tested this code. That's up to you to do. Read it, learn it, do it.
Post Reply