Select using join tables and array in a table

Question

Hi there I have 2 tables

|id|musicname|url|image|type

and the second table

|id|user|songslist|

inside songsids theres an array like this 1,3,5,6,8 etc ...

What Im aiming to do is select * from table1 and echo out the table1 as in an array but instead of tables two array , the actual row of table1.

So basically To take out each row that contains the id in songslist and put them all into a php array.

I have learned a lot about PHP arrays , but I'm not that good with mysql , Any Idea of how can I do that ?

EDIT

$selectmusiclist = mysql_query("SELECT * FROM music");
$songslist = array();
while ($songs = mysql_fetch_assoc($selectmusiclist)){
 $songslist[] = $songs;
}

and then table 2 select:

 $username="user1";
 $selectuser = mysql_query("SELECT * FROM usersmusic where user=$username");
 $user = mysql_fetch_assoc($selectuser);
 $songslist = $user['songslist'];

NOW I need to tell the array $songslist[] to output only the songs with id $songslist contained ids

What is the current code you are selecting and outputting with? — chris85
– chris85, Commented Aug 21, 2015 at 12:51
$selectmusiclist = mysql_query("SELECT * FROM music"); $songslist = array(); while ($songs = mysql_fetch_assoc($selectmusiclist)){$songslist[] = $songs; } of course that only outputs table 1 but I need it to interact with table 2 I also use the foreach for outputting it separately — Mica Butu
– Mica Butu, Commented Aug 21, 2015 at 12:52
Please update your question with code so it is easier to read. Are you asking how to get all data from the two tables in one query or how to get just the ids? — chris85
– chris85, Commented Aug 21, 2015 at 12:58
So, just to be clear, the songslist column of the usersmusic table contains a comma separated string of song ids, that correspond to ids in the music table? — Justin McAleer
– Justin McAleer, Commented Aug 21, 2015 at 13:10

chris85 · Accepted Answer · 2015-08-21 14:26:07Z

2

I think running a join like this will give you the results you are after.

SELECT * FROM usersmusic as um
join music as m
on um.songslist = m.id
where user = '$username'

If $username is not a static value make sure you escape it; don't want to get SQL injected in the future.

Also note the mysql_ driver is now deprecated you should consider updating to mysqli or PDO.

edited Aug 21, 2015 at 14:26

answered Aug 21, 2015 at 12:57

chris85

23.9k7 gold badges36 silver badges51 bronze badges

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

13 Comments

Mica Butu Over a year ago

Thankyou , I hope this works , Im going to test it now , thankyou !

Mica Butu Over a year ago

yes you are right I did accept right away out of excitement, yes there was needed USE of ` and ' in your answer , but this is exactly what Ive been looking for! It does select exactly how I needed it to , but it only selects 1 row, usually 1st one of the songslist , If I could figure that out too it would be great!

chris85 Over a year ago

Wait, songslist is a comma separated list..and this works?

Mica Butu Over a year ago

I used the while loop! I even used straight in phpmyadmin this command SELECT * FROM usersmusic as um join music as m on um.songslist = m.id where user = 'user1'

chris85 Over a year ago

You could try out this answer as well for normalizing the data, stackoverflow.com/questions/8648115/….

|

Collectives™ on Stack Overflow

Select using join tables and array in a table

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related