Page 1 of 1

attache more categories to one post

Posted: Wed Jul 25, 2007 6:31 pm
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)

Posted: Wed Jul 25, 2007 7:29 pm
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
}

Posted: Wed Jul 25, 2007 8:09 pm
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

Posted: Wed Jul 25, 2007 8:15 pm
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.

Posted: Wed Jul 25, 2007 8:29 pm
by spamyboy
I Thought that example should work with

Code: Select all

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

Posted: Wed Jul 25, 2007 8:46 pm
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.