Member Avatar for 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 = '';
		$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;	
}
  • 2 Contributors
  • 4 Replies
  • 325 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mbhanley

Dani AI

Generated

Quick diagnosis: the JOIN is fine — as suggested — but the duplicate category names come from printing the category inside the row loop. A JOIN returns one row per category–subcategory pair, so if you echo the category for every row you will see it repeated for each subcategory. Using GROUP BY the way you tried will collapse rows and drop subcategory rows unless you aggregate them, which explains the missing subcats.

Three simple fixes (ordered by clarity):

  • Build a nested array in PHP after fetching the rows, then render one category header with its sub-list. This avoids repeated output and keeps a single DB call.
  • Run two queries: one to fetch categories, one to fetch subcategories (or fetch all subcategories and index them by cat_id). This is easy to reason about and efficient with proper indexing.
  • Use SQL aggregation (GROUP_CONCAT) only if you want subcategory titles combined into a single field; beware of length limits and that you must GROUP BY the category columns.

Example grouping pattern (assumes you fetched rows into $rows with fields cat_id, cat_title, subcat_id, subcat_title):

// build grouped menu
$menu = [];
foreach ($rows as $r) {
    $cid = (int)$r['cat_id'];
    if (!isset($menu[$cid])) {
        $menu[$cid] = ['title' => $r['cat_title'], 'subs' => []];
    }
    if ($r['subcat_id'] !== null) {
        $menu[$cid]['subs'][] = ['id' => $r['subcat_id'], 'title' => $r['subcat_title']];
    }
}

// render nested lists
foreach ($menu as $cat) {
    echo '<li><h2>' . htmlspecialchars($cat['title']) . '</h2>';
    if ($cat['subs']) {
        echo '<ul>';
        foreach ($cat['subs'] as $s) {
            echo '<li><a href="' . rawurlencode($s['title']) . '">' . htmlspecialchars($s['title']) . '</a></li>';
        }
        echo '</ul>';
    }
    echo '</li>';
}

Quick tips: ORDER BY category then subcategory so rows are contiguous, escape output with htmlspecialchars, build URL slugs with a tested function, index subcategories.catid, and stop using deprecated mysql* functions — use PDO or mysqli with prepared statements.

Recommended Answers

All 4 Replies

Member Avatar for 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 ";
Member Avatar for 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

Member Avatar for 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
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.