[SOLVED] Join table issues

[krv]

Hello.

 

I am having issues displaying mysql results.

I have a 2 tables, 1 is categories 2 is sub categories.

 

Categories

+----+--------------+

| id | title        |

+----+--------------+

 

Sub Categories

+----+-----+----------------------+

| id | cat | title                |

+----+-----+----------------------+

 

I am trying to show them like:

Categories.title

- sub_categories.title where sub_cat.cat = categories.id

 

Ive tried a cross join in mysql command line but cant figure it out in php

 

    $sql_map = "SELECT

ht_cat.id as hid,

ht_cat.title as htitle,

ht_sub_cat.id as sid,

ht_sub_cat.cat as scat,

ht_sub_cat.title as stitle

FROM ht_cat,ht_sub_cat ORDER BY ht_cat.title ASC

";

    $map_result = mysql_query($sql_map) or die(mysql_error());

    while ($row = mysql_fetch_array($map_result)) {

        foreach ($row[hid] as $row[htitle] ->$row[scat]) {

            echo "$row[htitle] ><br /> - $row[stitle]<br />";

        }

    }

 

what am I doing wrong?

[revraz]

SELECT Sub_Categories.cat, Sub_Categories.title FROM `table` JOIN Categories on (Sub_Categories.cat = Categories.id);

 

Like that?

 

[krv]

Yes thank you that works. but how do i display in a while loop from mysql

so it shows categories.title

then sub_categories.title where sub_categories.category=categories.title or id I would assume

[revraz]

If you post your actual table field name, I can change the code to work for you if you don't see what we're doing.

 

$query = "SELECT Sub_Categories.cat, Sub_Categories.title, Categories.title FROM Sub_Categories JOIN Categories on (Sub_Categories.cat = Categories.id)";

 

$result = mysql_query ($query) or DIE (mysql_error());

 

while ($row = mysql_fetch_array($result)) {

  echo "$row['htitle'] - $row['stitle']<br />";

}

 

[krv]

awesome last thing though how do i pull categories.title so its

$row[ctitle] - $row[stitle]

?

Thanks

[revraz]

categories.title is already being selected, so just add the appropriate echo:

 

while ($row = mysql_fetch_array($result)) {

    echo "$row['ctitle'] - $row['stitle']<br />";

 

}

 

[krv]

what if they are the both named title in both tables?

[revraz]

I would think you would have a few options:

 

Use AS as you did in your first post.

Use the ID of the field result instead of the name ($row[0], $row[1], $row[2]...etc)

 

Echo the $row array to see how it's being returned.

[krv]

Thanks a lot man.

[krv]

Hello again.

Its not selecting title from categories.

+-----+----------------------+

| cat | title                |

+-----+----------------------+

|  4 | Competition          |

|  4 | Metro Italia        |

|  4 | Geared/ Multipurpose |

|  4 | Carbon              |

|  4 | Neo-Classical        |

|  4 | Performance          |

|  4 | Fixed/ SS            |

|  4 | Steel                |

|  3 | Cruiser              |

|  3 | Comfort              |

|  3 | Commuter            |

|  1 | Freeride            |

|  1 | All-Mountain        |

|  1 | Dirt Jump            |

|  1 | XC Sport            |

+-----+----------------------+

cat should be the categorires.title for instance 1 would be MTB

any suggestions.

[revraz]

Figure it out?

[krv]

yes thank you