1

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

3
Contributors
7
Replies
39
Views
3 Years
Discussion Span
Last Post by hericles
1

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.

0

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

0
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

Edited by diafol: reformatted code

0

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
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.