MySQL PHP: Return rows based on a column

Question

This seems easy but haven't found the magic search term yet.

One table

ID | UserID | Mod_number

1      43      1
2      43      2
3      43      3 
4      43      4
5      55      1
6      55      2

I want to return a query that looks like:

UserID | Mod_1&ID | Mod_2&ID ....  [EDITED below]

43     |   1&1    |  2&2 ....
55     |   1&5    |  2&6 ....

The Mod numbers are fixed (there are 7 of them).

The concatenation I know how to do, it's the rearranging values into rows that's tripping me up.

Any help or direction would be awesome, thanks! :)

====================== EDIT:

Hoping that the downvote was lack of clarity than quality of question - let me try to explain better.

This is for an assessment tool being developed in Joomla. Each module (section) gets checked off by a supervisor. When that happens there's an ID, a UserID, and a ModID recorded (among other data).

That results in a table that looks like this:

ID | UserID | Mod_number

1      43      1
2      43      2
3      43      3 
4      43      4
5      55      1
6      55      2
7      61      2
8      61      4

Each UserID is a user. I need to form this into an array where each row is one user and also contains the ID for each module. I think this presentation might be clearer (skipping the concatenation):

UserID | ID_M1 | ID_M2 | ID_M3 |ID_M4

43     |   1    |   2  |   3   |   4
55     |   5    |   6  |  
61     |        |   7  |       |   8

So that now there's one unique UserID per row (could be considered a key) with the ID recorded for each Module number. As an array that would probably look something like array(UserID=>array(ID_M1, ID_M2, ID_M3, ID_M4)). But getting that data into this is befuddling me.

Also, if you have other suggestions on structuring the data that would make it easier I'd love to hear - I'm a bit new to php/mysql/Joomla dev and there could be a simpler way of doing this.

Hope that makes more sense, thanks!

I don't really understand your question. Can you elaborate?

Norse
– Norse

2012-05-17 06:11:30 +00:00
Commented May 17, 2012 at 6:11 — Norse
– Norse, Commented May 17, 2012 at 6:11
Edited above, let me know if that makes more sense, thanks!

Gisto
– Gisto

2012-05-19 04:32:20 +00:00
Commented May 19, 2012 at 4:32 — Gisto
– Gisto, Commented May 19, 2012 at 4:32

iWantSimpleLife · Accepted Answer · 2012-05-21 01:06:12Z

2

This is a pivot table. you can look at this for help. http://www.artfulsoftware.com/infotree/queries.php#787

answered May 21, 2012 at 1:06

iWantSimpleLife

1,96414 silver badges22 bronze badges

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

1 Comment

Gisto Over a year ago

That's what it is! I figured this had to be done fairly often, very useful resource - thank you!

Okonomiyaki3000 · Accepted Answer · 2012-05-21 00:48:27Z

1

You could write a really ugly query where you GROUP BY UserID and then do some complex string manipulations, I suppose. But you'd be better off just getting this data as is and then restructuring it into an array in PHP.

$rows; // say this is an array direct from the db with id, user_id, mod_id fields
$grouped = array(); // we will build this array from your data

foreach ($rows as $row)
{
    if (!isset($grouped[$row['user_id']]))
    {
        $grouped[$row['user_id']] = array();
    }

    $grouped[$row['user_id']]['mod_' . $row['mod_id']] = $row['id'];
}

Now, when this has run, you should have an array, keyed by user_id, containing arrays keyed like 'mod_1', 'mod_2', etc. where the value is the 'id'. I think this is basically what you want.

edited May 21, 2012 at 0:48

answered May 17, 2012 at 6:08

Okonomiyaki3000

3,70625 silver badges23 bronze badges

2 Comments

Gisto Over a year ago

I've been playing with this all day but haven't had too much luck - would you have any quasi code that I can try out and run with? Thanks!

Gisto Over a year ago

(Quasi code in php, the logic of what to do is tripping me up a bit)

deej · Accepted Answer · 2012-05-21 17:18:20Z

1

How about following?

SELECT 
UserID,
IF (ID > 0 && Mod_Number = 1, ID, null) ID_M1,
IF (ID > 0 && Mod_Number = 2, ID, null) ID_M2,
IF (ID > 0 && Mod_Number = 3, ID, null) ID_M3,
IF (ID > 0 && Mod_Number = 4, ID, null) ID_M4
from <YourTable>

Here the IF ID_M part you can programatically generate from PHP as I think that's going to be dynamic.

edited May 21, 2012 at 17:18

answered May 21, 2012 at 10:49

deej

2,5744 gold badges31 silver badges52 bronze badges

2 Comments

Gisto Over a year ago

I tried out @Elijah's first. I see where you're going with this though, thanks for another approach!

deej Over a year ago

hmm, good. Basically above sql snippet will just fetch you things from database avoiding you from much processing later on... :-) I made a mistake by putting up Mod_Number = 4 for all the columns and there were some typo just corrected them. I think it should make some more sense now. Good luck.!

deej · Accepted Answer · 2012-05-17 06:21:20Z

0

Something like below should help:

SELECT user_id, GROUP_CONCAT(ID) as MOD_ID, GROUP_CONCAT(mod_number) as MOD_Number FROM GROUP BY user_id

answered May 17, 2012 at 6:21

deej

2,5744 gold badges31 silver badges52 bronze badges

2 Comments

Gisto Over a year ago

I tried this - it didn't quite get it in the form I needed it in. Let me know if my edits above make more sense - this may be close but I could be confused on applying it.

deej Over a year ago

Yes, now after your edit it's much clear. I have posted another answer I think that should match your requirement.

Collectives™ on Stack Overflow

MySQL PHP: Return rows based on a column

4 Answers 4

1 Comment

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related