[RESOLVED] PHP MySQL getting info from multiple tables

madhead29

Hello all, I have two tables that I want to get infomation from.

I have a table called info_db with the fields i_id, title, info and in_area

I have a table called area_db with the fields area_id, area_name

I echo out the data into a tabular table.

but what im trying to do is instead of echoing out the in_area field in the info_db table ( for example 31) is go to the area_db table and get the name of the area (which is the the area_name field) with the id 31 or whatever the id is in the in_area field of the info_db table.

I have read about using inner joins but im not sure how they would work with this.

Thanks.

bradgrafelman

Not sure how to explain it without an example, so here's an example:

SELECT i_id, title, info, area_name
FROM info_db i
  INNER JOIN area_db a ON a.area_id = i.in_area

madhead29

Thanks for the info bradgrafelman I will give it a try. Could I add a where in there for a username as normal? Would the way I echo out the information change?

Are the i and a to link the two fields together? Thanks.

bradgrafelman

madhead29;10976079 wrote:
Could I add a where in there for a username as normal?

Try it and see. 🙂

madhead29;10976079 wrote:
Would the way I echo out the information change?

That depends on a) how you 'echo out' the 'information', and b) how your final query looks (since no one ever copies-and-pastes code and instead understands the concepts being conveyed and writes their own implementation, riiiiight?). If, for example, a column name is ambiguous in a JOIN and you must include a table reference (e.g. I could have said "SELECT i.i_id, i.title, ..." etc.), then an associative array will include those table references; just do a [man]print_r/man on your results array if you don't know what it looks like.

madhead29;10976079 wrote:
Are the i and a to link the two fields together? Thanks.

No, those are just aliases to make typing easier (e.g. you can use "i" (1 character) throughout the query to reference table name "info_db" (7 characters)).

The "link" part is the JOIN.

madhead29

Wow thanks for all the info I will have a go and get back.

madhead29

Hi bradgrafelman. I got it working :-) thanks for your help. I used the following code:

SELECT info_db.info,info_db.title,area_db.area_name FROM info_db INNER JOIN area_db on info_db.in_area = area_db.area_id

It works exactly the way I wanted it to but there is a little problem.

If for example there is no in_area set for a record or the record that is in the in_area has been deleted the whole info row doesnt show in the results. I quess this is because of the inner join. Is there a way for it to show the record even if there is no value in the in_area field or if the area has been deleted from the area_db.

Some of the info won't have an area or the area could get deleted. it seems abit daft that if an area gets deleted then the info record won't show in the list say there area 50 info records with this one area then when its deleted none of them will show. Thanks for your help.

johanafm

LEFT JOIN

madhead29

Thanks johanafm that worked perfectly.