Php multi-dimensional array from mysql

Question

I have a mysql table that looks like this:

id | id_transaction | id_item    | 
1  |  it0001        | T0001      | 
2  |  it0001        | T0002      |
2  |  it0001        | T0003      |
3  |  it0002        | T0001      |
4  |  it0002        | T0003      |
5  |  it0003        | T0003      |
6  |  it0003        | T0004      |

and I am trying to build a multi-dimensional array that would end up looking like this:

Array
(
    array('T0001','T0002','T0003'),

    array('T0001','T0003'),

    array('T0003','T0004'),

);

I am using the select query:

$result = mysql_query("SELECT id_item,id_transaction from transaction_item ")or die("<br/><br/>".mysql_error());

Does anyone know how to do this?

1. don't use mysql_* it's deprecated+removed now. Use mysqli_* OR PDO for this 2. you din't did anything after query execution — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Sep 2, 2017 at 5:55

Death-is-the-real-truth · Accepted Answer · 2017-09-02 10:23:41Z

1.Don't use mysql_* it's deprecated+removed now. Use mysqli_* OR PDO for this

2.You din't did anything after query execution.

A mysqli_* version code is:-

 <?php
     $dbhost = 'localhost'; //change credentails
     $dbuser = 'root';//change credentails
     $dbpass = '';//change credentails
     $dbname = 'TUTORIALS';//change credentails
     $conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);

     if(! $conn ) {
        die('Could not connect: ' . mysqli_error());
     }
     $sql = 'SELECT id_item,id_transaction from transaction_item';
     $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
     $final_array = array();
     if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
           $final_array[$row['id_transaction']][] = $row['id_item'];
        }
        echo "<pre/>";print_r(array_values($final_array));
     } else {
        echo "0 results";
     }
     mysqli_close($conn);

?>

Ok,it worked, but how can i get a result without the id_transaction? I have edited the question just now. Thanks.

CodeGodie · Accepted Answer · 2017-09-02 07:46:37Z

3

Use GROUP_CONCAT in this manner:

SELECT id_transaction, GROUP_CONCAT(id_item)  
FROM transaction_item
GROUP BY id_transaction

This will result in the following:

+----------------+-------------------+
| id_transaction | id_item           |
+----------------+-------------------+
| it001          | T0001,T0002,T0003 |
+----------------+-------------------+
| it002          | T0001,T0003       |
+----------------+-------------------+
| it003          | T0003,T0004       |
+----------------+-------------------+

which you can then use php's explode() to create your arrays in the following manner:

$result = mysqli_query($conn, $sql_query);

while($row = mysqli_fetch_assoc($result)) {
    $final[] = explode(',', $row["id_item"]);
}

print_r($final);

Doing it in this manner will give you faster results as PHP would only iterate 3 times to build your array.

edited Sep 2, 2017 at 7:46

answered Sep 2, 2017 at 5:59

CodeGodie

12.2k6 gold badges40 silver badges66 bronze badges

4 Comments

Strawberry Over a year ago

I just don't understand the point of group concatenating something only to explode it again.

CodeGodie Over a year ago

@Strawberry faster performance when you let SQL do the work. If not, youll end up iterating too many times with php. Not so obvious with small data sets but when you think of a big list, the difference is pretty clear

Strawberry Over a year ago

I suspect that the opposite is true.

Ilham Prasetya Over a year ago

but how can i get a result without the id_transaction? I have edited the question just now. Thanks.

Collectives™ on Stack Overflow

Php multi-dimensional array from mysql

2 Answers 2

1 Comment

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Related