MySQL Query Within MySQL Query

[moviemastersdk]

I'm creating a mysql forum, and i need to echo mysql queries within mysql queries

 

 

any help is greatly appreciated

 

 

regards 

 

Daniel Holst

[Ch0cu3r]

You'll want to look into using joins. An example query would be like

SELECT c.category_id, c.category_title, etc...        # gets the category id and title
       f.forum_id, f.forum_title, etc...              # gets the forum id and title
FROM forum_categories AS c
LEFT JOIN forums f ON c.category_id = f.category_id  # selects forums that matches category id
ORDER BY c.category_id, f.forum_id                   # order results by category id and forum id

You'd process the above query using

$stmt = $mysqli->query($sql); 

$forum_categories = array();
while($row = $stmt->fetch_array(FETCH_ASSOC))
{
        // define forum category
	$forum_categories[$row['id']] = array(
		'title' => $row['category_title'],
	);

        // add forums to category
	$forum_categories[$row['id']]['forums'][] = array(
		'id' => $row['forum_id'],
		'title' => $row['forum_title']
	);
}

To display the categories and forums you'd do something like

<?php
//display forums caregories
foreach($forum_categories as $category_id => $category): ?>
<table>
	<tr>
		<th><a href="forum.php?cat=<?php echo $category_id; ?>"><?php echo $category['title']; ?></a></th>
	</tr>
	<?php 
	// diplay category forums
	foreach($category as $forums): ?>
	<tr>
		<td><a href="forum.php?cat=<?php echo $category_id; ?>&id=<?php echo $forum['id']; ?>"><?php echo $forum['title']; ?></a></td>
	</tr>
	<?php endforeach; ?>
</table>
<?php endforeach; ?>

[moviemastersdk]

Thanks very good answer 

 

i was just wondering what the LEFT JOIN does exactly?

[Ch0cu3r]

JOINS allow you to query more than one table at a time. There are many different variations of joins.

 

In my query example the left join will include the forum id and title in the result if it has the same category_id as the category's id. If it cant find a matching forum it'll return null.

 

The following article will explain it a lot clearer http://www.mysqltutorial.org/mysql-left-join.aspx