CODEWITHSUNDEEP
phpmysql
Gisto
Gisto

Reputation: 886

MySQL PHP: Return rows based on a column

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!

Upvotes: 0

Views: 151

Answers (4)

deej
deej

Reputation: 2564

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.

Upvotes: 1

iWantSimpleLife
iWantSimpleLife

Reputation: 1954

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

Upvotes: 2

Okonomiyaki3000
Okonomiyaki3000

Reputation: 3696

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.

Upvotes: 1

deej
deej

Reputation: 2564

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

Upvotes: 0

Related Questions