Link 2 tables and show them up

[Kleidi]

Hello everyone.

I'm playing around on a site that will show up all the products that i have but i want to add them on the category and when i click to a category to appear only the products that are in that category. I know that somehow i should make a table for categories and a table for products but how to link the table products with the categories table, and how to show them up?

Ex:

I have table movcat where are listed all the categories like Action, Thriller, Horror, Comedy, etc

Then i have table movies where are listed all the movies but them are not inserted in any category.

When i want to show them up i call a query to db and show them up, but those are not on any category and are showing up all of them.

 

Can someone help me, PLEASE?

 

P.S. Hope that i was understandable. Sorry for my bad english

[AdRock]

You need to show your queries etc and possible table structures

[o3d]

If I understood you correctly,

 

You need at least three tables:

categories - contains all categories

movies - contains all movie data

movies_categories - contains all movies linked to categories

 

select c.category_name
from movies a join movies_categories b on b.movies_id = a.id join categories c on c.id = b.category_id
where a.movie_name = 'TERMINATOR 2'

 

should return

ACTION

SCI-FI

THRILLER

[Kleidi]

Ok ... got the idea, but, when i add a movie, how should look like the query?

 

Thank you!

[Ken2k7]

I think the movie table can have an id field linking to categories, removing the need of the third table. Then when you add movies, just add to the movie table.

[Kleidi]

I think the movie table can have an id field linking to categories, removing the need of the third table. Then when you add movies, just add to the movie table.

Any example of you idea, pls ?

 

Thanks!

[Kleidi]

I think the movie table can have an id field linking to categories, removing the need of the third table. Then when you add movies, just add to the movie table.

 

I have tried in many ways but i'm not getting what i need

 

My db tables looks like this:

 

p_kategori table

--
-- Table structure for table `p_kategori`
--

CREATE TABLE IF NOT EXISTS `p_kategori` (
  `id` int(6) NOT NULL auto_increment,
  `katemri` varchar(255) NOT NULL default '',
  `katpermalink` varchar(255) NOT NULL default '',
  `kataktiv` enum('N','Y') NOT NULL default 'Y',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `p_kategori`
--

INSERT INTO `p_kategori` (`id`, `katemri`, `katpermalink`, `kataktiv`) VALUES
(1, 'Gjeneratore', 'gjeneratore/', 'Y'),
(2, 'Elektroda', 'elektroda/', 'Y'),
(3, 'Test', 'test/', ''),
(4, 'Test2', 'test2/', ''),
(5, 'Prove', 'prove/', ''),
(6, 'Prove 2', 'prove2/', ''),
(7, 'Prove 3', 'prove3/', 'Y'),
(8, 'Test 4', 'test4/', 'Y');

 

 

p_produkte table

 

--
-- Table structure for table `p_produkte`
--

CREATE TABLE IF NOT EXISTS `p_produkte` (
  `id` int(6) NOT NULL auto_increment,
  `katid` varchar(255) NOT NULL default '',
  `prodemri` varchar(255) NOT NULL default '',
  `prodimg` varchar(255) NOT NULL default '',
  `prodinfo` text NOT NULL,
  `prodpermalink` varchar(255) NOT NULL default '',
  `prodaktiv` enum('N','Y') NOT NULL default 'Y',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `p_produkte`
--

INSERT INTO `p_produkte` (`id`, `katid`, `prodemri`, `prodimg`, `prodinfo`, `prodpermalink`, `prodaktiv`) VALUES
(2, '1', 'XCV 220W', '220w.jpg', '<p>\r\n	Gjenerator</p>\r\n', 'gjenerator_xcv220w/', 'Y');

 

 

kategorite.php - The page where appear the category list and, if clicks on a category, will appear a new page where should be listed all products on that category.

 

<?php
if (!isset($_GET['id'])) // if $_GET['id'] is NOT set
{
include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
include (BPATH . '/includet/dbconfig.php');
$query="SELECT * FROM `p_kategori` WHERE kataktiv = 'Y' ORDER BY `p_kategori`.`ID`";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
   
$i=0;
   while ($i < $num) {
   $id=mysql_result($result,$i,"id");
   $katemri=mysql_result($result,$i,"katemri");
   $katpermalink=mysql_result($result,$i,"katpermalink");
   $kataktiv=mysql_result($result,$i,"kataktiv");
  ?>

<img src="imazhet/bulona.jpg" width="15" hspace="5" vspace="3" align="absmiddle" /><a href="kategorite.php?id=<?php echo $id;  ?>"> <?php echo $katemri;  ?></a><br>

<?php
   $i++;
   }
   ?>	



<?php
} // end if()
else // $_GET['id'] IS set
{
   if (!empty($_GET['id'])) // if $_GET['id'] is NOT empty
   {
     
  
 // getting infos from db
  include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
 include (BPATH . '/includet/dbconfig.php');
   $query="SELECT * FROM `p_kategori` WHERE id = '$_GET[id]'";
   $result=mysql_query($query);
   $num=mysql_numrows($result);
   
   $id=mysql_result($result,$i,"id");
   $katemri=mysql_result($result,$i,"katemri");
   $katpermalink=mysql_result($result,$i,"katpermalink");
   $kataktiv=mysql_result($result,$i,"kataktiv");
   // end of getting infos from db
?>


...
...
...

I don't know how to continue now!

...
...
...





<?php     
   } // end if()
} // end else
?> 

 

 

I don't know how to continue now ! I have tried to use the query you suggest but i't didn;t worked bcz i don;t know how and where to use it.

 

Can you please help me?

 

Thank you in advance!