Hi All,
As usual I here with a PHP & MySQL problem.
Most of you know, I am developing Music Download system.
Today I have created Widget to display last 5 songs.

<?php
include('datab.php');

$get = "SELECT * FROM `songs` ORDER BY `id` DESC";
$getres = mysql_query($get) or die(mysql_error());
if(mysql_num_rows($getres)> 0)
{
    while ($row = mysql_fetch_array($getres))
    {
        echo $row['name'] . "<br>";
}
}
?>

But there is a problem.
I am saving artists to another table call, songartists, beacuse some songs have more than one artist.
I want to add 'Song Name - Artist'
How can I get data to this echo from songartists

Use a JOIN to connect the two tables. For that to happen both tables need to share a common key that you can JOIN them ON.
A sample JOIN looks like:

SELECT a.col1, b.col2 FROM tablea AS a JOIN tableb AS b ON a.col1 = b.col1 WHERE <some flter> 

As an aside you mentioed a widget to show the last 5 songs but your query, ifit is the query for your widget, gets all songs from the table. You can use LIMIT <some number> to specify how many rows to return.

easily you need to use a join..

As an aside you mentioed a widget to show the last 5 songs but your query, ifit is the query for your widget, gets all songs from the table. You can use LIMIT <some number> to specify how many rows to return.

Here is my table structure

songs

  id    int(11)              
    name    varchar(50)              
    size    int(10)              
    lyrics  varchar(35)              
    music   varchar(35)              
    mastered    int(35)              
    link    varchar(355)             
    added   timestamp

songartists

id  int(11)              
sid     int(10) << Song ID             
artist  varchar(355) 

Problem is how can I get artist that belonging to song?
For an example, Song ID 10's artists saved in songartists table

Can anyone explain me with an example

select s.name, sa.artist from songs as s join songartist as sa on s.id = sa.sid where s.id = 10

That will return a row (containg song name and artist) for every song artist that is linked to song id 10

That will return a row (containg song name and artist) for every song artist that is linked to song id 10

my tables are songs and songartists
is this code correct?

select songs.name, songartists.artist from `songs` as s join `songartists` as sa on songs.id = songsartists.sid where songs.id = 1

Amost. If you use AS to rename (alias) a table you then use that alias in the rest of the statement.
There is no point using AS s if you then use songs.name in the SELECT statement. So, if you remove the two AS statements (as s, as sa) it should work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.