CODEWITHSUNDEEP
phpmysql
kevin
kevin

Reputation: 11

help with a join and group

There's got to be a way so that this only uses 1 query. I'm assuming using a join and a group by

Here's my current code:

$sql = mysql_query("select * from `databases` where `id` in (select `id_database` from `categories`)");

while ($row = mysql_fetch_assoc($sql))
{
   echo $row['name'] . '<br />';

   $sql_c = mysql_query("select * from `categories` where `id_database`='" . $row['id'] . "'");

   while ($row_c = mysql_fetch_assoc($sql_c))
   {
      echo $row_c['title'] . '<br />';
   }
}

Upvotes: 1

Views: 52

Answers (2)

mitch
mitch

Reputation: 282

select d.name, c.title from databases as d, categories as c where d.id=c.id_database;

Upvotes: 1

Lee
Lee

Reputation: 13542

There is... something like this should do the trick.

$sql = mysql_query("select * from databases db JOIN categories cat on (db.id=cat.id_database) ORDER BY db.name");

$last_name = null;
while ($row = mysql_fetch_assoc($sql))
{
   if( $row['db.name'] !== $previous_name ) {
       echo $row['db.name'] . '<br />';
       $previous_name = $row['db.name'];
   }
   echo $row['cat.title'] . '<br />';
}

Upvotes: 1

Related Questions