How to show category only once when active ads

[max2me99]

I've a mySQL db with two tables - advertisers and category. If the advertiser is active, I'd like to have the category name appear, with all active advertisers below, and then the following category, etc. Right now I'm testing and have everything active.

Quick structure on tables are
advertisers contains id, category_id, name, img1, img2, and active (tinyint with all set to 1 for now)
category contains id, category

My current code outputs the category with a space and then the name with a break.

Really need to be
Category name (this will be an h1)
Advertiser name (paragraph)
another Advertiser name
Next Category name (and so on)

Here's what I have to date.

<?php require_once("includes/connection.php"); ?>

<table>
	<tr>
		<td>

	<?php
	// Perform database query
	$result = mysql_query("SELECT * FROM advertisers
      INNER JOIN category
      ON advertisers.category_id = category.id
      WHERE advertisers.active = 1
      ORDER BY name");
	if (!$result) {
		die("Database query failed: " . mysql_error());
	}
	
	// Use returned data
	while ($row = mysql_fetch_array($result)) {
		echo $row["category"]." ".$row["name"]."<br />";
	}
	
	?>
		</td>
	</tr>
</table>
<?php include("includes/footer.php"); ?>

Point me please. I'm thrilled to have gotten this far.:$

Donna

[nav33n]

I am not sure if you can do that using joins. Join returns all the records which matches the condition. So, there is no way to find out which advertiser belongs to which category.

$query = "select id from category";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
    $categoryid = $row['id'];
  $query2 = "select * from advertisers where category_id='$categoryid' and active=1";
$result2 = mysql_query($query2);
if(mysql_num_rows($result2) > 0 ) {
echo "<h1>".$categoryid."</h1>";
  while($row2 = mysql_fetch_array($result2)) {
$advertisername = $row2['name'];
  echo "Advertiser is ".$advertisername;
}
} else {
 echo "No advertisers found for this category..";
}
}

This way, you can print Category first, then get all the advertisers for that category.

[max2me99]

Thanks. I managed to add to a row to use the actual category name rather than just the id. Not sure if this is the cleanest way, but I duplicated and it does output.

<?php
		$query = "select * from category";
		 $result = mysql_query($query);

		 while($row = mysql_fetch_array($result)) {
			$categoryid = $row['id'];
			$categoryname = $row['category'];
				$query2 = "select * from advertisers where category_id='$categoryid' and active=1";
				$result2 = mysql_query($query2);

		 if(mysql_num_rows($result2) > 0 ) {
			 echo "<h1>".$categoryname."</h1>";
				while($row2 = mysql_fetch_array($result2)) {
				$advertisername = $row2['name'];
			 echo "Advertiser is ".$advertisername. "<br />";
   		   }

      } else {
    	  echo "No advertisers found for this category.";
   			   }
	  
      }
	
	?>

Should I not make that extra row? If it's okay, I'll leave since it does output.

Thanks,
Donna

[nav33n]

This looks good! :)