How to Optimize Code For an Organized List

[bensmithe1234]

I have a small application that basically lists links in a categorized way. The output looks like this:

Criminal Justice
Link 1
Link 2

Law Enforcement
Link 1
Link 2

I have working code to produce this list, but I am in the process of rewriting it to make it easier to maintain and update.

Currently for each category I have a different MySQL query for each category like this:

$query = 'SELECT * FROM `table` WHERE `category` = \'criminal_justice\'';
	
	$result = $db->query($query);
	$result = mysqli_query($db, $query);
	
	$num_results = $result->num_rows;
	if ( $num_results > 0 )
	{
	echo '<h1>Criminal Justice</h1>';
		for ($i=0; $i <$num_results; $i++)
		{
			$row = $result->fetch_assoc();
			echo '<p>ID: ' . $row['id'] . '</p>';
			echo '<p>Category: ' . $row['category'] . '</p>';
			echo '<p>Title: ' . $row['title'] . '</p>';
			echo '<p>Purpose: ' . $row['purpose'] . '</p>';
		}

		$result->free();
	}

So I'm wondering if there is any way to tidy this up, so that rather than have repeating queries is to have one query that selects everything, then use PHP to output an h1 category title when the category changes.

Any suggestions would be greatly appreciated.

 

[Sleidia]

You could simply do this in order to use only one query for all the categories :

$query = "SELECT * FROM 'table' ORDER BY category";
    
$result = $db->query($query);
$result = mysqli_query($db, $query);

$num_results = $result->num_rows;

    if ( $num_results > 0 ) {
    
        if($row['category'] != $old_category)  echo '<h1>' . $row['category'] . '</h1>';
    
        for ($i=0; $i <$num_results; $i++) {
        
        $row = $result->fetch_assoc();
        echo '<p>ID: ' . $row['id'] . '</p>';
        echo '<p>Category: ' . $row['category'] . '</p>';
        echo '<p>Title: ' . $row['title'] . '</p>';
        echo '<p>Purpose: ' . $row['purpose'] . '</p>';
        
        $old_category = $row['category'];
        
        }
    
    $result->free();
        
    }

 

[bensmithe1234]

Thanks Sleidia, that was exactly what I needed. Works great.