Return array of values with MYSQL SELECT [duplicate]

Question

Possible Duplicate:
Can MySQL Nested Select return list of results

I have one table containing the users (id, name) and another one containing courseAttrs a user has applied to (userID,courseID). I would like to select all users and have this select return the name and a comma separated list of courseIDs the user has applied to. What is a possible solution?

I tried selecting all courses where it would return all users and their courses. Example: if I have 2 users, the first one has 2 courseAttr entries and the second one has 4 then the query would return 6 rows, each with a different courseID but not comma separated. I could handle this with PHP but I am assuming there is a simpler solution through MYSQL. I have the feeling JOIN ON could help but I don't know how the JOIN command works. — JNK
– JNK, Commented Sep 26, 2012 at 17:27
I found a similar question with which I was able to get the result I intended. stackoverflow.com/questions/2524848/… — JNK
– JNK, Commented Sep 26, 2012 at 17:41

jpganz18 · Accepted Answer · 2012-09-26 17:32:46Z

1

You should check the manual of mysql, so, as far as you explain u should create the query for two tables... you can use the procedures of mysql to get results.. u can always use a foreach to read all values, and if u want an specific kind of array you can create it meanwhile the loop is running through your object/array returned by mysql

I think if you create an array (like i think u want to create) with the form (name, courseid, courseid, courseid) u might get a mess with all the data, and might create an array per user. I think the best is to recieve the objects or arrays that u get from the query.

for the query, assuming you have an associative field, you can do something like this

select * from user, courseAttrs where user.courseattr = courseattr.id_couse_attr

If you use mysql_fetch_object

while ($row = mysql_fetch_object($result)) {
    echo $row->user_id;
    echo $row->fullname;

}

mysql_fetch_assoc() returns associative array:

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
}

mysql_fetch_array() returns array

while ($row = mysql_fetch_array($result)) {
    echo $row[0];
    echo $row[1] ;
}

answered Sep 26, 2012 at 17:32

jpganz18

5,90821 gold badges74 silver badges131 bronze badges

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

2 Comments

JNK Over a year ago

the $row result I would like to get from MYSQL would be array("name"=>"Joe", "courseIDs"=>"1,2,3,6"); The courseIDs string could then be run through explode("1,2,3,6") which I could then process further. If I am not able to find anything I will just implement it with PHP.

jpganz18 Over a year ago

but what if u you have more than one row of results?? would u create another array?

Collectives™ on Stack Overflow

Return array of values with MYSQL SELECT [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related