Streamlining amount of MYSQL Queries

[brianbehrens]

I often run into the problem of using to many queries in my opinion.  I don't think the way I've been doing this really saves on the amount of queries to the database.  In the example below, there should be a way to only query the database 2 times for the 2 different tables, but having limited knowledge of arrays, I'm not sure exactly how to do so.

 

Any help would be greatly appreciated... below is one example.

 

 

 

 

 

 

 

<?php
require_once ('db_connect.inc');
$sql = "SELECT * FROM MAIN_MENU ORDER BY id ASC";
$mainMenu = @mysql_query($sql)  or die (mysql_error());
if (@mysql_num_rows($mainMenu) < 1) {
//
} else {
while ($row = @mysql_fetch_array($mainMenu)) {
	$id = $row['id'];
	$title = $row['title'];
	$contentID = $row['content_id'];
	echo "$title<br />";

	$sql = "SELECT * FROM SUB_MENU WHERE main_menu_id = $id ORDER BY id ASC";
	$subMenu = @mysql_query($sql)  or die (mysql_error());
	if (@mysql_num_rows($mainMenu) < 1) {
		//
	} else {
		while ($row2 = @mysql_fetch_array($subMenu)) {
			$sub_id = $row2['id'];
			$main_menu_id = $row2['main_menu_id'];
			$sub_title = $row2['title'];
			$sub_contentID = $row['content_id'];
			echo "$sub_title - ";
		}
		echo "<br />";		
	}
}
}
mysql_close($conn);
?>

[brianbehrens]

The results of this query look like the following:

 

http://marxcreative.com/test.php

 

This isn't obviously going to be the end result, but gives you an idea of what I'm going for.

[Barand]

In that situation you only need a single query to get the data from both tables.

SELECT m.id, m.title as main_title, m.content_id as main_content_id,
    s.title as sub_title, s.content_id as sub_content_id
FROM main_menu m INNER JOIN sub_menu s ON m.id = s.main_menu_id
ORDER BY m.id, s.id

[brianbehrens]

I guess I have some reading up on mySQL to do because I'm not sure what that string does.  I'm playing with a while look, but I'm not sure how exactly to display that data.

[Barand]

try

<?php

$sql = "SELECT m.id, m.title as main_title, m.content_id as main_content_id,
        s.title as sub_title, s.content_id as sub_content_id
        FROM main_menu m I
            NNER JOIN sub_menu s ON m.id = s.main_menu_id
        ORDER BY m.id, s.id" ;
$res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');

$prev='';
while (list($mid, $mtitle, $mcontent, $stitle, $scontent) = mysql_fetch_row($res)) {
    if ($prev != $mtitle) {
        echo "$mtitle<br>";
        $prev = $mtitle;
    }
    echo " - $stitle<br>";
}

?>

[brianbehrens]

Great work.  It's not even that complicated after taking the time to understand it all.  Thanks a lot.  I'll be sure to remain a part of this forum.