CODEWITHSUNDEEP
phpsql
João Coelho
João Coelho

Reputation: 13

SQL PHP query multiple tables data

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";

Upvotes: 1

Views: 58

Answers (3)

Mukti Rani Ghosh
Mukti Rani Ghosh

Reputation: 577

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";

Upvotes: 1

Hardood
Hardood

Reputation: 523

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";

Upvotes: 1

Adam Winter
Adam Winter

Reputation: 1914

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.

Upvotes: 0

Related Questions