getting info from two tables?

[marblo]

I have a problem. My code looks like this:

<?
$name = $HTTP_POST_VARS["name"];
if (!$name) {
echo "S&ouml;kf&auml;ltet var tomt!";
exit;
}
$db = mysql_connect("localhost");
mysql_select_db("musik", $db);
$query = "select grupp from grupper where grupp like '%".$name."%'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
echo "sorry, no matching results.";
echo $link;
exit;
}
while ($record = mysql_fetch_assoc($result)) {
while (list($fieldname, $fieldvalue) = each ($record)) {
echo "<b>".$fieldvalue."</b><BR>";

}
echo "<BR>";
}
echo $link;

?>

This works fine but I would like to select gruppID from grupper and use the number to create a list based on another table called album. Does anyone understand what I mean?

It´s quite simple... on one table i have a bunch of groups and all of them have a different id (gruppID). On the other table i have a bunch of albums. the albums have an id(gruppID) based on what group it belongs to.:rolleyes:

[eoghain]

Write your select statement like this:

SELECT a.*
  FROM albums a, groupper g
 WHERE a.grouppID = g.grouppID
   AND g.name like '%$name%';

This is a simple join of 2 tables that is returing all of the records from 1 table that have the same grouppID as the second table, but only returning the grouppIDs from the second table that match the name given.

[marblo]

Thanx eoghain... IT WORKED!
I came to think of another thing though...
now it echoes it like this:

Group
Genre
Album

Group
Genre
Album2

... and so on... How would I do to have it listed like this instead?:

Group
Genre
Album
Album2
Album3

[eoghain]

That you will have to handle in code. When you are printing out the information you will have to handle when to print the group/genre information. Since this query looks like it is only displaying 1 groups information you can just have that information only print when you are on the first record and on all subsequent records only print out the album information.

// ommited getting data from database assuming mysql
$row_id = 1;
while ($row = mysql_fectch_row($result) {
   if ($row_id == 1) {
   echo $row["group_name"] . "<br>\n";
   echo $row["genre_name"] . "<br>\n";
   }
   $row_id++;
   echo $row["album_name"] . "<br>\n";
}

[xblue]

just a head-up: when using mysql_fetch_row, you can't access the results as associative array, you'd need to use mysql_fetch_assoc or mysql_fetch_array or use numeric indexes ($row[0]) and so on.

something else: the above code would show group and genre only once at the very beginning, but from your query I'd think you'd like to display it whenever the group changes, is that correct?

then you'd need to keep track of the previous group, like

$last_group='';
while ($row = mysql_fetch_assoc($result) {
    if ($last_group != $row['group_name']) {
        echo $row['group_name'] . "<br>\n";
        echo $row['genre_name'] . "<br>\n";
        $last_group = $row['group_name']);
    }
    echo $row['album_name'] . "<br>\n";
}
unset($last_group);

[eoghain]

just a head-up: when using mysql_fetch_row, you can't access the results as associative array, you'd need to use mysql_fetch_assoc or mysql_fetch_array or use numeric indexes ($row[0]) and so on.

OOps, I forgot, it's been too long since I've worked with MySQL and PHP. ;->