PHP/MySQL get name where id = id

Question

Hi,

I have one mysql table with results assigned a category id, and a seperate table that lists all of the categories by name with their respective id's. All of the results are grouped by their id's and the id is displayed as the heading.

How do I display the id as its respective name from the other database? Here is the full code I'm using, with the line in question commented out. Any help greatly appreciated. S.

$subcatQuery=mysql_query("select * from issubcat order by id");
$subcatResult=mysql_fetch_array($subcatQuery);

$query = "SELECT * from isgallery where galleryPage ='1'";
$resultSet = mysql_query($query);        

if (mysql_num_rows($resultSet))
{
    $gallArray = array();

    while ($galleryResult = mysql_fetch_array($resultSet))
    {
        // if($galleryResult['subcatPage'] == $subcatResult['id'])
        // {
        //     $gallSection = $subcatResult['subcat'];
        // }

        if (!isset($gallArray[$gallSection]))
        {
            $gallArray[$gallSection] = array();
        }
        $gallArray[$gallSection][] = $galleryResult;                            
    }

    foreach($gallArray as $gallSection => $gallItems)
    {
        echo '<div class="com-gallery">' . $gallSection . '</div>' . PHP_EOL;
        echo '<ul class="photo-gallery">'. PHP_EOL;

        foreach ($gallItems as $photoresult)
        {
            echo '<li><a rel="gallery" href="'.$wwwUrl.'images/properties/gallery/'.$photoresult['imagename'].'" ';

            if($photoresult['galleryTitle'])
            {
                echo 'title="'.$photoresult['galleryTitle'].'"';
            }
            else
            {
                echo 'title="'.$photoresult['imagename'].'"';
            }

            echo '><img alt="" src="'.$wwwUrl.'images/properties/gallery/tn/'.$photoresult['imagename'].'" width="122" height="88" /></a></li>'. PHP_EOL;
                           }
            echo '</ul><br /><br />' . PHP_EOL;        
        }
    }
}

Answer 1

I'm not sure if I am missing something, but it looks like this can simply be done with a JOIN.

$query = "SELECT isgallery.*, issubcat.subcat
    FROM `isgallery`
    INNER JOIN `issubcat`
    ON `isgallery`.`subcatPage` = `issubcat`.`id` 
    WHERE `galleryPage` = '1'";

$resultSet = mysql_query($query);

if(mysql_num_rows($resultSet))
{
    $gallArray = array();

    while ($galleryResult = mysql_fetch_array($resultSet))
    {
        $gallSection = $galleryResult['subcat'];

        // The rest of your code...