JOIN problem, combined with PHP loop

MasterACE14 · August 26, 2011

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 · August 26, 2011

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 · August 26, 2011

this appears to be illogical to me :/

kickstart · August 26, 2011

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 · August 26, 2011

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 · August 26, 2011

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 · September 2, 2011

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 · September 2, 2011

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 · September 2, 2011

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

Sign In

JOIN problem, combined with PHP loop

Recommended Posts

MasterACE14

Link to comment

Share on other sites

Muddy_Funster

Link to comment

Share on other sites

MasterACE14

Link to comment

Share on other sites

kickstart

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

MasterACE14

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

MasterACE14

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information