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, Aug 21 '15 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, Aug 21 '15 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, Aug 21 '15 at 12:58
@chris85 Yes It is right, I have updated the question with all the possible details now ! — Mica Butu, Aug 21 '15 at 13:07
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, Aug 21 '15 at 13:10
^if that is right answer below won't work; I presume each row is its own record. — chris85, Aug 21 '15 at 13:11

chris85 · Accepted Answer · 2015-08-21T14:26:07.077

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 '15 at 14:26

answered Aug 21 '15 at 12:57

chris85

23,846
7
34
51

Thankyou , I hope this works , Im going to test it now , thankyou ! – Mica Butu Aug 21 '15 at 13:12
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! – Mica Butu Aug 21 '15 at 14:18
Wait, `songslist` is a comma separated list..and this works? – chris85 Aug 21 '15 at 14:27
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'` – Mica Butu Aug 21 '15 at 14:28
Yes it does work!!! But As I said , it only displaying 1 result whatever is the first value in `songslist` – Mica Butu Aug 21 '15 at 14:29
If it is comma separated then `um.songslist` will not equal `m.id`...or maybe in one example if only one song/id is listed? Is that what you are getting? – chris85 Aug 21 '15 at 14:30
Yes I'm getting 1 song with 1 id that is what Im trying to say, it only gives 1 answer – Mica Butu Aug 21 '15 at 14:31
Yes because `1,2,3` will not equal a single id. You should normalize the data. 1 row per song. – chris85 Aug 21 '15 at 14:33
So is there a solution? – Mica Butu Aug 21 '15 at 14:33
Separate the data into their own records or their might be some hacky solution to get it work but in the future you will continue to run into issues. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – chris85 Aug 21 '15 at 14:34
Well at least your answer Was close to a solution, I might just use it separating the songslist into songs or something like that – Mica Butu Aug 21 '15 at 14:38
1

You could try out this answer as well for normalizing the data, http://stackoverflow.com/questions/8648115/split-comma-separated-values-into-individual-rows. – chris85 Aug 21 '15 at 14:39
I was thinking to do WHERE id = phparray or something like that http://stackoverflow.com/questions/9476146/mysql-php-select-where-id-array , Ill see how it goes , Ill let you know once I found what has worked :) – Mica Butu Aug 21 '15 at 15:00

Select using join tables and array in a table

1 Answers1