SQL PHP query multiple tables data

Question

Noob alert here! I'm trying to display some data on my php page from two tables but I don't know how to do it

db_bandas TABLE  
banda_id | banda_name
    1    |  Rolling Beatles
    2    |  Linkin Bizkit



db_albuns TABLE  
album_id | album_name | banda_id | album_ano
    1    |    Music   |    1     |  2000
    2    |   another  |    2     |  2014
    3    |  good one  |    1     |  2004

What I want to show on the page is like :

 1  | Music    | Rolling Beatles | 2000 
 2  | another  | Linkin Bizkit   | 2014 
 3  | good one | Rolling Beatles | 2004

I've tried the query like this

$sql = "SELECT db_albuns.album_nome AS album_nome, db_albuns.album_id AS album_id, db_albuns.album_ano AS album.ano, db_banda.banda_nome AS banda_nome FROM db_albuns,db_banda";

Hardood · Accepted Answer · 2020-05-31 17:43:45Z

1

You can use Join to do it

$sql = "SELECT db_albuns.album_nome, 
db_albuns.album_id, db_albuns.album_ano, 
db_banda.banda_nome
FROM db_albuns join db_banda 
on  db_albuns.banda_id = db_banda.banda_id";

edited May 31, 2020 at 17:43

answered May 31, 2020 at 17:40

Hardood

5331 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hardood Over a year ago

I didn't saw your tables structure. sorry i edited my answer

Mukti Rani Ghosh · Accepted Answer · 2020-05-31 19:21:58Z

1

Your table name should be albums and columns name should be id, name, banda_id, ano. Your table name is albums, so I think there is no need to use prefix 'albums' for each column.

As well as another table name should be bandas and columns name should be id, name

And your query should be like this

$sql = "SELECT albums.id, albums.name, bandas.name, albums.ano
        FROM albums JOIN bandas
        on albums.banda_id = bandas.id";

answered May 31, 2020 at 19:21

Mukti Rani Ghosh

5772 silver badges6 bronze badges

Comments

Adam Winter · Accepted Answer · 2020-05-31 17:42:46Z

0

There are a few different ways you can do this, but my go-to is a LEFT JOIN.

$sql = "SELECT album_id, album_name, banda_name, album_ano
        FROM albuns LEFT JOIN bandas
        USING (banda_id)
        WHERE 1=1;

On a side not, prefixing your tables with db_ is a little confusing, because that's the name of a table, not a database.

answered May 31, 2020 at 17:42

Adam Winter

2,0021 gold badge17 silver badges35 bronze badges

1 Comment

João Coelho Over a year ago

Perfect! many many many thanks!!! I know that I should not prefix with db.... thanks for warning!

Collectives™ on Stack Overflow

SQL PHP query multiple tables data

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related