[SOLVED] Problem with join?

[iarp]

$query = "
SELECT cat.cat_name, con.page_id, con.content 
FROM MOHA_content AS con, MOHA_page_cat AS cat 
WHERE con.page_name='$parts[2]' && 
(
SELECT cat.cat_id, con.page_cat_id 
FROM MOHA_content AS con, MOHA_page_cat AS cat 
WHERE cat.cat_id = con.page_cat_id
) = '$parts[1]'
";

 

Is that written correct? I've never done it before.

 

Basically i have 2 tables MOHA_content with 'page_cat_id' contains the relating id number for 'cat_id' in MOHA_page_cat

 

CREATE TABLE `MOHA_page_cat` (
  `cat_id` int(10) unsigned NOT NULL auto_increment,
  `cat_name` varchar(90) NOT NULL default '',
  `cat_url` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`cat_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `MOHA_page_cat`
-- 

INSERT INTO `MOHA_page_cat` VALUES (1, 'home', '');
INSERT INTO `MOHA_page_cat` VALUES (2, 'League Info', 'league-info');
INSERT INTO `MOHA_page_cat` VALUES (3, 'Calendar', 'calendar');
INSERT INTO `MOHA_page_cat` VALUES (4, 'Rep Rangers', 'rep-rangers');
INSERT INTO `MOHA_page_cat` VALUES (5, 'House League', 'house-league');
INSERT INTO `MOHA_page_cat` VALUES (6, 'Links', 'links');
INSERT INTO `MOHA_page_cat` VALUES (7, 'Contact Us', 'contact-us');

 

CREATE TABLE `MOHA_content` (
  `page_id` int(10) unsigned NOT NULL auto_increment,
  `page_name` varchar(60) NOT NULL default '',
  `page_cat_id` int(2) NOT NULL default '0',
  `title` varchar(90) NOT NULL default '',
  `content` longtext NOT NULL,
  `sidebar` longtext NOT NULL,
  `sidebar_active` tinyint(1) NOT NULL default '0',
  `editor` varchar(20) NOT NULL default '',
  `last_edited` varchar(40) NOT NULL default '',
  `created` timestamp(14) NOT NULL,
  `active` int(1) NOT NULL default '1',
  PRIMARY KEY  (`page_id`)
) TYPE=MyISAM AUTO_INCREMENT=31 ;

-- 
-- Dumping data for table `MOHA_content`
-- 

INSERT INTO `MOHA_content` VALUES (1, 'menu', 0, '', '<!-- START SECONDARY NAVIGATION BAR, CHROME -->\r\n	<div class="chromestyle" id="chromemenu">\r\n		<ul>\r\n			<!-- <li><a href-"LINK_LOCATION" rel="NAME">NAME</a></li> - This is the basic template for a link on the second row. rel="NAME" replace NAME with whatever you want. Read on down for more info -->\r\n			<li><a href="../rep_rangers/" rel="rep_rangers">Rep Rangers</a></li>\r\n			<li><a href="../house_league/" rel="house_league">House League</a></li>\r\n			<li><a href="../league_info/" rel="league_info">League Info</a></li>\r\n			<li><a rel="events">Events</a></li>\r\n			<li><a href="../forms/" rel="forms">Forms</a></li>\r\n			<li><a href="../news/" rel="news">News</a></li>\r\n			<li><a href="../MOMS.php">Minor Oaks Moms</a></li> <!-- MOMS does NOT have a rel="" because there''s no other pages for that section -->\r\n		</ul>\r\n	</div>\r\n\r\n	<!-- if you look above at the Rep Rangers link, you''ll see rel="rep_rangers". This area below is what causes the drop down box. -->\r\n	<!-- REP RANGERS --> \r\n	<div id="rep_rangers" class="dropmenudiv_a"> \r\n		<a href="../TR_Coaches_07_08.php">Ranger Coaches 07/08</a>\r\n		<a href="../rep_rangers/richard_bell.php">RB Xmas Tourn.</a>\r\n		<a href="../rep_rangers/clinics_dev.php">Development</a>\r\n	</div>\r\n\r\n\r\n	<!--HOUSE LEAGUE --> \r\n	<div id="house_league" class="dropmenudiv_a">\r\n		<a href="../house_league/HL_Division.php">HL Divisions</a>\r\n		<a href="../house_league/awards_week_2008.php">Awards Week 2008</a>\r\n		<a href="../house_league/HL_news.php">HL News</a>\r\n		<a href="../house_league/HL_Key_Contacts.php">HL Key Contacts</a>\r\n		<a href="../house_league/HL_sponsors.php">Sponsors</a>\r\n		<a href="../house_league/HL_Tournaments.php">HL Tournaments</a>\r\n		<a href="../house_league/awards_week_2007.php">Awards Week 2007</a>\r\n		<a href="../house_league/timbits_ip.php">Timbits IP</a>\r\n		<a href="../house_league/HL_playing_rules.php">Playing Rules</a>\r\n	</div>\r\n\r\n	<!-- LEAGUE INFO -->\r\n	<div id="league_info" class="dropmenudiv_a">\r\n		<a href="../league_info/faqs.php">FAQ''s</a>\r\n		<a href="../league_info/moha_gym_time.php">MOHA Gym Time</a>\r\n		<a href="../league_info/07_08_registration.php">Registration(07/08)</a>\r\n		<a href="../league_info/oakville_arenas.php">Oakville Arenas</a>\r\n		<a href="../league_info/clinics.php">Clinics</a>\r\n	</div>\r\n\r\n	<!-- EVENTS -->\r\n	<div id="events" class="dropmenudiv_a">\r\n		<a href="../events/toy_drive.php">Toy Drive</a>\r\n		<a href="../events/toy_drive_pics.php">Toy Drive Pics</a>\r\n		<a href="../events/calendar.php">Calendar</a>\r\n	</div>\r\n\r\n	<!-- FORMS -->\r\n	<div id="forms" class="dropmenudiv_a">\r\n		<a href="../forms/RIC_role.php">RIC Role</a>\r\n	</div>\r\n\r\n	<!-- NEWS -->\r\n	<div id="news" class="dropmenudiv_a">\r\n		<a href="../news/calendar_draw.php">Calendar Draw</a>\r\n		<a href="../news/questions.php">Questions ?</a>\r\n	</div>\r\n\r\n	<!-- LEAVE THIS -->\r\n	<script type="text/javascript">\r\n		cssdropdown.startchrome("chromemenu")\r\n	</script>', '', 0, 'Ian', '', '20080723153343', 0);
INSERT INTO `MOHA_content` VALUES (3, 'Home', 1, 'Home', '<p>Welcome, to the Minor Oaks Hockey Association (MOHA) Web Site. We hope that our site provides enough information to answer your questions either about our association or what is happening. Please take some time to "look around" and feel free to provide us with your <a href="contact.php">feedback</a></p>\r\n<p align="center"><strong> <a class="new" style="color: black" href="forms">MOHA 2008/09 Registration Form</a><br /> <a style="color: navy;" href="../calendar">February Calendar Draw Winners</a><br /> <a style="color: red" rel="external" href="http://www.ohfatomaaa08.ca">MOHA to Host Atom AAA OHF - April 08</a><br /> <a style="color: green;" href="league-info/clinics">MOHA to Host Pilot combined CHIP / NCCP Coach / Speak Out Clinic</a><br /> <a style="color: blue;" href="league-info/clinics">Mitron Pre-Tryout Hockey School</a><br /> <a class="new" style="color: blue" href="TR%20Spring%20Tryouts.htm">2008 / 09 Town Rep Ranger Try-out Schedule</a> </strong></p>\r\n<h2>A word from our President</h2>\r\n<p>The Minor Oaks Hockey Association or MOHA is Oakville''s community based hockey association.<br /> <br /> We are a non profit volunteer driven organization that ices 250+ teams. The MOHA provides both a Rep and House League program starting with the Initiation Program and continuing right up to Juvenile. In Town Rep, we have teams in the "A", "AA", "AAA" and "AE" classifications. For the 2007/08 season we also plan on expanding the highly successful AE Teams to include two additional entries, separate Minor Atom and Atom teams and a Minor Midget AE subject to participant interest at that level. AE teams will now play in Minor Midget, Bantam, Minor Bantam, Peewee, Minor Peewee, Atom and Minor Atom to increase the development opportunities for our Oakville players.<br /> <br /> The Rep teams are all single age groups from Novice to Minor Midget. Only the Midget teams are a two year mixed age group. <br /> House League is broken down into Red, White and Blue divisions with Major and Minor groups in each age through Midget. Our red divisions plays contact starting at Minor Peewee. White and Blue are non contact throughout. For 2007/08 our House League again plans to provide separate Minor Midget and Midget divisions (as introduced in 2005/06) with the Major Midgets and first year Juveniles playing together.<br /> <br /> For more information about the Minor Oaks Hockey Association, please contact our office at 905-338-9220 or <a href="contact.php">e-mail us here </a>.<br /> <br /> We look forward to an exciting season and wish everyone a safe and prosperous year.<br /> <br /> Best Wishes,<br /> Mike Zardo<br /> <em>President, MOHA</em></p>', '<p><a href="../votes.php">Enter votes here.</a></p>\r\n<p><strong>Upcoming Dates</strong><br /><strong>May 22</strong> - <a href="../MOHA_AGM">MOHA AGM</a><br /><br />MOHA’s Annual General Meeting will be held at Holy Trinity High School<br /><br /><strong>April 3</strong> - <a href="../download.php?uid=14">08/09 Registration Form</a> <br /><br />Blank copy available for download - pdf format<br /><br /><strong>April  1 - Registration Starts</strong><br /><br />2008/09 Registrations will be available April 1. Forms will on the web site and  mailed in late March to current Players at address on file.</p>', 1, 'Ian', 'Jul/23/08', '20080723170733', 1);
INSERT INTO `MOHA_content` VALUES (11, 'Development', 4, 'Development', '<h1>Hockey Development Focus</h1>\r\n<p><a href="../league/clincs">Current Clinics</a><br /><br /> Hockey Development for our volunteers is a key commitment of the MOHA. We believe that more aware, informed and educated volunteers are critical to the development and safety of our players.<br /><br /> As an example of this commitment, MOHA provides a variety of OMHA recognized clinics and has established Mitron as a component of our Town Rep development program.</p>\r\n<dl> <dt><strong>Mitron</strong> </dt><dd>Mitron as a system represents a  training process for developing and conditioning hockey players. This training process is based on twenty years of experience with scientific principles and sport philosophies from Western, Eastern and European cultures.</dd> <dd>The mastery of hockey skills and systems is a long term process entailing various phases of learning according to the age and the proficiency of the athlete. The Mitron High Performance Hockey System was created to facilitate the technical, tactical, physical and mental development of hockey players in order to bring their hockey to the highest performance level they are capable of achieving.</dd> <a href="#top">Back to top</a> <dt><strong>NCCP and CHIP training and certification</strong> </dt><dd>The NCCP and CHIP Coaching Programs offered by the OMHA are designed to provide volunteer coaches with the tools necessary to improve athlete development. Whether it is certifications programs, specialty clinics, mentorship or high performance, ultimately the goal is to provide the player with the best possible experience. Coaches are encouraged to continually hone and build on their current skills by taking an NCCP certification course, Coach Refresher Clinic, finding a Coach Mentor, or taking specialty courses within their local association</dd> <a href="#top">Back to top</a> <dt><strong>HTCP Hockey Trainer programs</strong> </dt><dd>The HTCP is a risk management and safety education program for the volunteer hockey trainer. The ultimate goal of the program is to have all Hockey Trainers implement effective risk management on their own teams, where safety is the first priority at all times, both on and off the ice. All Hockey Trainers should utilize a proactive, preventative approach to safety while being prepared to react in the event of accidents, injuries or medical emergencies.</dd> <dd>The HTCP is designed to educate people in the prevention, recognition and treatment of hockey related injuries. The HTCP offers three levels of certification, which are designed to assist the individual trainer as his or her experience grows. Program content, administration and policies are established through the HDCO Trainers Committee and through consultation with Hockey Canada''s National Trainers Committee. </dd> <a href="#top">Back to top</a> <dt><strong>PRS Speak Out programs</strong> </dt><dd>The Prevention Services "Speak Out" Clinic on abuse and harassment prevention is available to any OMHA or OHA member association or team. This clinic is 3.5 hours in length and can be hosted locally by any member association. The clinic requires a minimum of 25 and a maximum of 50 participants and is conducted by OMHA instructors who have been prepared and certified by the Canadian Red Cross.</dd> <dt><strong>Speak Out! "It''s More Than Just a Game" is a four-hour interactive workshop focused on the prevention of harassment and abuse in the game of hockey. Topics include:</strong> </dt><dd>Definitions of abuse and harassment</dd> <dd>Responding to disclosures</dd> <dd>Prevention guidelines for coaches</dd> <dd>Showers and locker rooms</dd> <dd>Dressing for sports</dd> <dd>Transporting of participants</dd> <dd>Injured and ill children</dd> <dd>Road trips</dd> <dd>Integrated teams</dd> <dd>Prevention guidelines for playing</dd> <dd>Prevention of harassment and abuse during competition</dd> <dd>Fair Play Codes</dd> <dd>While focused towards team officials, this program is an excellent opportunity for parents to gain an appreciation of some of the challenges and difficult situations facing today''s volunteer.</dd> <a href="#top">Back to top</a> </dl>', '', 0, 'Ian', 'Jul/23/08', '20080723153343', 1);

[Barand]

Is that written correct? I've never done it before.

 

If we knew what you were trying to do then perhaps we could help

[bluejay002]

yeah... give more details with what you want to achieve, then we will give our insights.

[iarp]

lol i guess that would help...

 

 

This was the original pages coding

$navString = $_SERVER['REQUEST_URI'];
$parts = explode('/', $navString);

if (empty($parts[1])) {
$parts[2] = 'home';
}

$query = "SELECT page_id, content FROM " . DB_CONTENT . " WHERE folder1='$parts[1]' && page_name='$parts[2]'"; 
$result = mysql_query($query);
$num = mysql_num_rows($result);
$content = mysql_fetch_array($result, MYSQL_ASSOC);

 

As as it was for this script, each entry in the MOHA_content table had a value for folder1 and page_name.

 

folder1 referred to the part between the main domain and the ending page.... i.e. http://moha.iarp.ca/league-info/faq/ So in the database for the page named faq, the value in folder1 was league-info.

 

After adding alot more links(and a ton more to come) i realized using folder1 in this fashion was a bad idea, so i remember you could join tables and decided to make MOHA_page_cat table that consists of cat_id, cat_name, cat_url (1, League Info, league-info). Then just refer to 1 in a new column in MOHA_content i named page_cat_id.

 

So now in order to get the above script to work, i needed a script to grab the MOHA_content row information, and then grab the corresponding cat_id(and cat_url)... and then i got lost even thinking up how to do that.... and here we are

 

Hope thats more useful.

[iarp]

I hate bumping, just bit short on time.

 

If theres a way to just grab the cat_url(where MOHA_cat.cat_id = MOHA_content.page_cat_id) and page_name so that i could use it in the original code i posted that'd be great.

[iarp]

Solved.

 

SELECT con.page_name, cat.cat_url, con.page_id, con.content

FROM MOHA_content AS con, MOHA_page_cat AS cat

WHERE con.page_name = '$parts2[2]' AND (con.page_cat_id = cat.cat_id) AND (cat.cat_url = '$parts2[1]')

seems to be working just fine now.