JOIN problem, combined with PHP loop

[MasterACE14]

I have the following PHP code...

$query1 = $this->db->query("SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`
                                        FROM `".$this->dbname."`.`categories`
                                        LEFT JOIN `".$this->dbname."`.`forums`
                                        USING (`cat_id`)
                                        ORDER BY `categories`.`cat_order` ASC");

              $i = 0;
                while($row = $this->db->fetch_assoc($query1))
                {
                    $content .= ($i % 2 == 0 ? '<tr class="odd">' : '<tr>');
                    $content .= '<td><p><a href="index.php?cg=cgs&page=forum&do=forums:'.$row['cat_id'].'">'.$row['cat_name'].'</a></p>';
                    $content .= ' - '.$row['forum_name'].'</td>';
                    $content .= '</tr>';
                    $i++;
                }

 

I'm getting duplicate rows of the first table (categories) being outputted. So I'm currently getting...

Crikey Games

- Suggestions and Ideas

Crikey Games

- General Discussion

Crikey Games

- Announcements

Realm Battles

- Bugs, Glitches and Exploits

Realm Battles

- Recruiting

Realm Battles

- General Discussion

Realm Battles

- Suggestions and Ideas

Realm Battles

- Battle Grounds

Realm Battles

- Announcements

 

When I want to be getting...

Crikey Games

- Suggestions and Ideas

- General Discussion

- Announcements

 

Realm Battles

- Bugs, Glitches and Exploits

- Recruiting

- General Discussion

- Suggestions and Ideas

- Battle Grounds

- Announcements

 

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) NOT NULL,
  `cat_order` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`cat_id`, `cat_name`, `cat_order`) VALUES
(1, 'Crikey Games', 1),
(2, 'Realm Battles', 2);

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

--
-- Table structure for table `forums`
--

CREATE TABLE IF NOT EXISTS `forums` (
  `forum_id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) NOT NULL,
  `forum_name` varchar(50) NOT NULL,
  `forum_description` varchar(225) NOT NULL,
  `forum_order` int(5) NOT NULL DEFAULT '0',
  `forum_locked` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`forum_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `forums`
--

INSERT INTO `forums` (`forum_id`, `cat_id`, `forum_name`, `forum_description`, `forum_order`, `forum_locked`) VALUES
(1, 1, 'General Discussion', 'talk about anything you like', 2, 0),
(2, 1, 'Announcements', '', 1, 0),
(3, 1, 'Suggestions and Ideas', 'if you have a suggestion or idea, share it here', 3, 0),
(4, 2, 'Announcements', '', 4, 0),
(5, 2, 'General Discussion', 'talk about anything Realm Battles related here', 5, 0),
(6, 2, 'Battle Grounds', '', 6, 0),
(7, 2, 'Recruiting', '', 7, 0),
(8, 2, 'Suggestions and Ideas', 'if you have a suggestion or idea for Realm Battles, share it here', 8, 0),
(9, 2, 'Bugs, Glitches and Exploits', 'if you have discovered something that needs to be fixed, share it here', 9, 0);

 

Not sure whether I'm doing something wrong with the PHP side of things, or the MySQL query.

 

Also I can't figure out how to ORDER BY `forums`.`forum_order` ASC , when trying to add it to this query.

 

Thanks in advance,

Ace

[Muddy_Funster]

you need to use a conditional check to see if you are going to output the catagory name or not :

$i = 0;
$cat='new';
                while($row = $this->db->fetch_assoc($query1))
                {
                    $content .= ($i % 2 == 0 ? '<tr class="odd">' : '<tr>');
                   if($cat != $row['cat_name']){
                    $cat = $row['cat_name'];  
                    $content .= '<td><p><a href="index.php?cg=cgs&page=forum&do=forums:'.$row['cat_id'].'">'.$row['cat_name'].'</a></p>';
                   }
                    $content .= ' - '.$row['forum_name'].'</td>';
                    $content .= '</tr>';
                    $i++;
                }

That should work, it's untested so may need a little tweeking.

[MasterACE14]

this appears to be illogical to me :/

[kickstart]

Hi

 

Every row will have the columns from both tables in it. Therefore what you need to do is only output the fields from the first table when they change, which is what Muddy_Funsters code does (although there is a couple of missing table td tags).

 

All the best

 

Keith

[PFMaBiSmAd]

The way to output a new heading/category/section once, each time it changes, is to remember the last value (initialized to a value that will never exist in the data), detect when it changes and start a new heading/category/section, and remember the new value. The basic logic using the $cat variable that Muddy_Funster posted is the correct method of doing this.

[fenway]

Even better is to pre-process the result, build a hash based on cat_name, push onto an array, and then output it.  Much cleaner that way.

[MasterACE14]

I have tried a heap of different combinations and I can't get it to display correctly.

            if($this->db->num_rows($query1) > 0)
            {
                $i = 0;
                $cat = 'new';
                while($row = $this->db->fetch_assoc($query1))
                {
                    $content .= ($i % 2 == 0 ? '<tr class="odd">' : '<tr>');
                    if($row['cat_name'] != $cat) {
                        $cat = $row['cat_name'];
                        $content .= '<td><p><a href="index.php?cg=cgs&page=forum&do=forums:'.$row['cat_id'].'">'.$row['cat_name'].'</a></p>';
                    }
                    $content .= ' - '.$row['forum_name'];
                    $content .= '</td></tr>';
                    $i++;
                }
            }

 

It's coming up like:

- General Discussion - Announcements - Recruiting - General Discussion - Suggestions and Ideas - Battle Grounds - Announcements

 

Categories

Crikey Games

- Suggestions and Ideas

Realm Battles

- Bugs, Glitches and Exploits

 

And the HTML source looks like this:

<table id="forum-hor-zebra" summary="Categories"><th>Categories</th><tbody>
<tr class="odd">
<td><p><a href="index.php?cg=cgs&page=forum&do=forums:1">Crikey Games</a></p> 
- Suggestions and Ideas</td></tr>
<tr> - General Discussion</td></tr>
<tr class="odd"> - Announcements</td></tr>
<tr><td><p><a href="index.php?cg=cgs&page=forum&do=forums:2">Realm Battles</a></p> - Bugs, Glitches and Exploits</td></tr>
<tr class="odd"> - Recruiting</td></tr><tr> - General Discussion</td></tr>
<tr class="odd"> - Suggestions and Ideas</td></tr>
<tr> - Battle Grounds</td></tr>
<tr class="odd"> - Announcements</td></tr></tbody></table>

[PFMaBiSmAd]

This problem is actually a php/html problem, so moving thread to the php help forum section...

 

You need to start by getting your <tr><td></td></tr> tags correct so that the table is valid and the correct information is within those tags. Assuming that you want each line (headings and data under those headings) to be a separate html table row, see the following minimal code -

 

<?php
$cat = ''; // initialize to a value that will never exist as data
$content = "<table>\n";
while($row = $this->db->fetch_assoc($query1)){
	if($row['cat_name'] != $cat) {
		// the category changed
		$cat = $row['cat_name']; // save the new cat
		// output a new heading here...
		$content .= "<tr><td><p><a href='index.php?cg=cgs&page=forum&do=forums:{$row['cat_id']}'>{$row['cat_name']}</a></p></td></tr>\n";
	}
	// output each piece of data under a heading here...
	$content .= "<tr><td> - {$row['forum_name']}</td></tr>\n";
}
$content .= "</table>";

echo $content;

 

I did not include the $i and class="odd" logic in that for a couple of reasons - 1) You need to get the html tags correct before you worry about styling it, and 2) I don't know if you want to strictly alternate the class="odd" regardless of if the row is a heading or data or if you want to start over and just alternate for the data under each heading. Once you see where the <tr> tags are at in the correct output, adding the logic to insert the class="odd" at the correct point for either of those methods should be simple.

[MasterACE14]

I did not include the $i and class="odd" logic in that for a couple of reasons - 1) You need to get the html tags correct before you worry about styling it, and 2) I don't know if you want to strictly alternate the class="odd" regardless of if the row is a heading or data or if you want to start over and just alternate for the data under each heading. Once you see where the <tr> tags are at in the correct output, adding the logic to insert the class="odd" at the correct point for either of those methods should be simple.

 

I've managed to get the desired result. Thank you kindly guys for your help and patience.

 

Regards, Ace