PHP/SQL get proper array from SQL table

Question

I got the following table in a database

- ID | successor 
- 01 | 02
- 01 | 04
- 01 | 08
- 02 | 03
- 04 | 05
- 05 | 06

This is just a simple assignment table (is this the correct english term?) for content of an E-Learning system.

My Current php code is this

$sqlget = "SELECT * from successor";                        
$result = $conn->query($sqlget);                                                        
$dbsuccessor = [];
    
    if ($result->num_rows > 0) {                                                            
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $dbsuccessor[] = $row;                                                              
            
        }
    } else {
        echo "0 results";
    }

after parsing this to js with json_encode i get a not usefull construct like this

var successors = <?php echo json_encode($dbsuccessor);?>;
console.log(successor);

Array[9]

0:

Object ID: "1" - successor: "2"

1:

Object ID: "1" - successor: "4"

2:

Object ID: "1" - successor: "8"

to be fair, this is obviously exactly what you would expect with my code used above.

My goal is an array output like this:

ID: "1" -> successor"2","4","8"

so i can see all successors of module with ID 1 when typing console.log(successors[1]); in javascript

best thing would be if my db table stay like it is. How can i achieve my goal?

Please check the answers below and if any one worked for you then mark and up-vote it as an accepted answer. Up-vote others too if they are useful. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Aug 22, 2016 at 11:12
@Martin please close your question if you do not need further help. — Glubus
– Glubus, Commented Aug 22, 2016 at 12:35
i read that i need reputation of 250 to close something. If this is not true, plese tell me how to. — Martin WasGehtSieDasAn
– Martin WasGehtSieDasAn, Commented Aug 23, 2016 at 13:17

Vinie · Accepted Answer · 2016-08-22 11:21:43Z

2

Use Group_concat() to achieve this

Change your query to

$sqlget = "SELECT ID,group_concat(successor) from successor group by ID";
$result = $conn->query($sqlget);                                                        
$dbsuccessor = [];

if ($result->num_rows > 0) {                                                            
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $dbsuccessor[] = $row;                                                              

    }
} else {
    echo "0 results";
}

answered Aug 22, 2016 at 11:21

Vinie

2,9931 gold badge20 silver badges30 bronze badges

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

2 Comments

Death-is-the-real-truth Over a year ago

A better answer must be appreciated.+10

Martin WasGehtSieDasAn Over a year ago

thats it, thank you very much. i didnt test any other solution posted here.

Max P. · Accepted Answer · 2016-08-22 09:52:03Z

0

Change code that generates array to:

$dbsuccessor[$row['ID']][] = $row['successor'];

answered Aug 22, 2016 at 9:52

Max P.

5,6892 gold badges17 silver badges35 bronze badges

Collectives™ on Stack Overflow

PHP/SQL get proper array from SQL table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related