Join Problem

[SkyRanger]

The problem that I am having is that it will only display 1 output for each farea.  Not sure what the problem is

 

$queryfa = "SELECT  * FROM faq as f1 join faqcat as f2 where f1.farea = f2.cat_id group by f1.farea order by f1.ftitle DESC";

 

Database layout is:

Table: 
faq

Rows:
farea
ftitle
fdesc
posted

Table:
faqcat

Rows:
cat_id
cat_name

 

 

[Zane]

What does this give you?

 

SELECT * FROM faq as f1 
INNER JOIN faqcat as f2 
ON f1.farea = f2.cat_id
GROUP BY f1.farea
ORDER BY f1.ftitle DESC

Edited March 20, 2013 by Zane

[SkyRanger]

The query that I posted will only give 1 output per farea,  There is more than 1 entry per farea

 

So what is displaying is:

 

cat_name1

ftitle1  fdesc1 posted1

 

cat_name2

ftitle2 fdesc2 posted2

 

cat_name3

ftitle5 fdesc5 posted5

 

 

but there is 

cat_name1

ftitle1 des...

ftitle3 des...

 

cat_name2

ftitle2 .....

ftitle4....

 

cat_name3

ftitle5

etc....

Edited March 20, 2013 by SkyRanger

[Barand]

That's what GROUP BY does - it gives one row per item. It is normally used for calculating totals, counts or averages per item

[SkyRanger]

Ok, thanks barand, but how would I go about doing what I need it to do?  Do you know of a link I could go to to get an example on what I need to do.

[SkyRanger]

Tried to edit post.  But here is my full code that I am working with:

 

 <?php
						$queryfa = "SELECT  * FROM faq as f1 join faqcat as f2 where f1.farea = f2.cat_id group by f1.farea order by f1.ftitle DESC";
                        $resultfa = $mysqli->query($queryfa) or die($mysqli->error.__LINE__);
						

                        while($rowfa = $resultfa->fetch_assoc()){
							
						?>
				<div class="grid_16">
					<table>
						<thead>
							<tr>
								<th colspan="3">FAQ - <?php echo $rowfa['cat_name']; ?></th>
								<th colspan="2">Actions</th>
							</tr>
						</thead>
						<tfoot>
							<tr>
								<td colspan="5" class="pagination">
									View More
								</td>
							</tr>
						</tfoot>
						<tbody>
                           <tr>
                               <td width="21%"><u><em><strong>Title</strong></em></u></td>
                               <td width="50%"><u><em><strong>Description</strong></em></u></td>
                               <td width="17%"><u><em><strong>Date Added</strong></em></u></td>
                               <td width="6%"></td><td width="6%"></td>
                           </tr>    
							<tr>
								<td><?php echo $rowfa['ftitle']; ?></td>
								<td><?php echo $rowfa['fdesc']; ?></td>
								<td><?php echo $rowfa['posted']; ?></td>
								<td><a href="viewfaq.php?faq=<?php echo $rows['fid']; ?>" class="edit">View</a></td>
								<td><a href="delfaq.php?faq=<?php echo $rows['fid']; ?>" class="delete">Delete</a></td>
							</tr>
						<?php
						}
                        ?>	
						</tbody>
					</table>
				</div>
			</div>

Edited March 20, 2013 by SkyRanger

[Barand]

here's how

 

$sql = "SELECT f2.cat_name, f1.ftitle, f1.fdesc, f1.posted
        FROM faq as f1
            INNER JOIN faqcat as f2 ON f1.farea = f2.cat_id
        ORDER BY f1.area, f1.title DESC";
$result = mysqli->query($sql);

$prevcat = '';
while (list($catname, $title, $desc, $posted) = $result->fetch_row()) {
    // if it's a new catname, print it
    if ($prevcat != $catname) {
        echo "<br><b>$catname</b><br>";
        $prevcat = $catname;
    }
    echo "$title $desc $posted<br>";
}

[SkyRanger]

Thanks Barand, just made a few code changes to make it fit in with my code and it worked perfectly.