Pulling data from 2 separate tables

[codeline]

I've got a table (tbl_items) that holds a number of items and the individual information for each. Within 'tbl_items' I also have a row labeled 'category_id' in which each item is given a number (ex: 2).

 

I've got a separate table (tbl_items_categories) that only has 2 rows: 'category_id' and 'category_name'.

 

I wasn't sure if a JOIN method would be best for this but what would be the best approach to list all items and within each item, also echo out the name of the category they belong to and not the category_id number?

[Psycho]

Yes, you want to use a JOIN. Also, they are not "rows", they are "fields" or "columns".

 

Since both tables use the same field you doa JOIN using the USING command.

SELECT *
FROM tbl_items
JOIN tbl_items_categories USING `category_id`

[codeline]

@mjdamato.. Thanks! I'll try it out.

 

I was curious, can you also use a JOIN when the fields do not have the same name? For instance, maybe within 'tbl_items' the field was labeled "category_id" but within 'tbl_items_categories' the field was just 'id'?

[Psycho]

I was curious, can you also use a JOIN when the fields do not have the same name? For instance, maybe within 'tbl_items' the field was labeled "category_id" but within 'tbl_items_categories' the field was just 'id'?

 

Yes, absolutely you can. But, by using the same name for foreign keys does simplfy things and makes debugging much easier. BUt if the field names were different, this is one way to join the tables

SELECT *
FROM tbl_items
JOIN tbl_items_categories ON tbl_items.category_id = tbl_items_categories.id

 

By the way, I used '*' in my examples above, but I am of the opinioin you should always explicitly include the field names in the SELECT statemetn for the data you want.Using '*' increawses overhead on the server and can lead to potential problems - especially when joining tables