PHP & MySQL Mega Menu Help Needed

[mbhanley]

Having A Problem in getting results to display correctly I seem to be getting two sets of results displayed for categories "cat_title" when I only need it displayed once.
What is the best way around this does anyone know whether I should define using an mysql query or PHP.

function get_categories($id='') {
		
		if($id != ""):
		$id = mysql_real_escape_string($id);
		$sql = "SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title, s.cat_id 
		FROM subcategories AS s LEFT JOIN categories AS c ON c.cat_id = s.cat_id ORDER BY subcat_id ";
		else:
		$sql = "SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title, s.cat_id 
		FROM subcategories AS s LEFT JOIN categories AS c ON c.cat_id = s.cat_id ORDER BY subcat_id ";
		endif;
		$res = mysql_query($sql) or die(mysql_error()); 
		if(mysql_num_rows($res) != 0):
		while($row = mysql_fetch_assoc($res)) {
		
		$base_url = 'http://www.awholesaledirectory.com/';
		$cat_title = $row['cat_title'];
		
		$cat_title =preg_replace('/[^A-Za-z0-9-]+/', '-', $row['cat_title'] );
		$category_title = strtolower( trim( $cat_title ) );
		
		$subcat_title = $row['subcat_title'];
		$subcat_title =preg_replace('/[^A-Za-z0-9-]+/', '-', $row['subcat_title'] );
		$subcategory_title = strtolower( trim( $subcat_title ) );
		
		echo '<ul><li><h2><a href="'.$base_url.'' .$category_title. '" title="' .$row['cat_title']. '">' .$row['cat_title']. '</a><h2></li>';
		echo '<li><a href="'.$base_url.'' .$subcategory_title. '" title="' .$row['subcat_title']. '">' .$row['subcat_title']. '</a></li>'; 
		
		
		}
		else:
			echo '';
		endif;	
}

[chintan@dani]

Try this . I think you had not used join correctly.

"SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title, s.cat_id
FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id ORDER BY subcat_id ";

[mbhanley]

Try this . I think you had not used join correctly.

"SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title, s.cat_id
FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id ORDER BY subcat_id ";

Nope still does not work getting a repeat on the category table row. And if I use a MySQL statement HAVING repeats i.e.

$sql = "SELECT COUNT(*) as repetitions, c.cat_id, c.cat_title, s.subcat_id, s.subcat_title, s.cat_id FROM subcategories AS s INNER JOIN categories AS c ON c.cat_id=s.cat_id GROUP BY s.cat_id HAVING repetitions > 1";

It cuts off the rest of the table rows for subcategories, subcat_title

[chintan@dani]

you are using same field cat_id in both the table. you don't need to select cat_id from subcategories table.

SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id ORDER BY c.cat_id

[mbhanley]

you are using same field cat_id in both the table. you don't need to select cat_id from subcategories table.

SELECT c.cat_id, c.cat_title, s.subcat_id, s.subcat_title FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id ORDER BY c.cat_id

Thanks for the help Chintan, I have got two table columns cat_id, in both tables categories and subcategories so that the subcategories can be identified to the categories table row based on its id the mysql table set up is like the this.

(categories)
cat_id cat_title

(subcategories)
subcat_id subcat_title cat_id

The problem is it seems to repeat the categories table once joined with the subcategories table and then when I echo out the cat_title row it repeats it more than once.

I only need it to echo the category once then echo all the subcategories underneath that category rather than repeating the cat_title with each subcat_title.