Link to home
Get AccessLog in
Avatar of sany101
sany101

asked on

MySQL many to many query

Hi I have the following tables:

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 09, 2011 at 08:20 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `c`
--

CREATE TABLE IF NOT EXISTS `c` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) COLLATE latin1_german2_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

--
-- Dumping data for table `c`
--

INSERT INTO `c` (`category_id`, `category_name`) VALUES
(1, 'cat-1'),
(2, 'cat-2'),
(3, 'cat-3'),
(4, 'cat-4');

-- --------------------------------------------------------

--
-- Table structure for table `projects`
--

CREATE TABLE IF NOT EXISTS `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_name` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `thumb` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `project_visibility` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`project_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4 ;

--
-- Dumping data for table `projects`
--

INSERT INTO `projects` (`project_id`, `project_name`, `thumb`, `project_visibility`) VALUES
(1, 'a', 'a-thumb', 1),
(2, 'b', 'b-thumb', 0),
(3, 'c', 'c-thumb', 1);

-- --------------------------------------------------------

--
-- Table structure for table `p_c`
--

CREATE TABLE IF NOT EXISTS `p_c` (
  `project_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

--
-- Dumping data for table `p_c`
--

INSERT INTO `p_c` (`project_id`, `category_id`) VALUES
(1, 3),
(1, 4),
(2, 1),
(2, 4),
(3, 2);

Open in new window



I currently have this query:
SELECT * FROM projects WHERE project_name = %s

Open in new window

which displays a project row based on the project_name passed via url variable.

I however would also like to return the category_names which this project belongs to and other project_names that have project_visibility set to "1" and  match this projects category_names


ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access