Create a multidimensional array by the union of two mysql extraction

Question

I have problem extracting data from 2 mysql db and create a multidimensional array:

$query = "SELECT * FROM MRT_PRODUCTS" or die( "Error in the consult.." . mysqli_error( $link ) );
    $result = mysqli_query( $link, $query );
    $rows = array();
    while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {
        $rows[] = array(
            'IDF' => $row[ 'IDF' ],
            'NAME' => $row[ 'NAME' ],
            'DETAILS' => $row[ 'DETAILS' ]
        );
    }

with this I extract product but I have another db that contains the pictures of products, so I would extract also them like to create this array:

products[
IDF:'1',
NAME:'Computer',
DETAILS:'Computer desktop celeron intel',
PIC:['foto1.jpg',foto2.jpg,ecc..]
]

how is it possible?

If I well understand you have 2 databases : product and picture. Plus, picture contains product's pictures ? — Lucas, Aug 07 '19 at 13:43
If it that so, there should be a commun ID ? Ex : pictures contains the ID of the product ? — Lucas, Aug 07 '19 at 13:44
I assume you mean ONE Database, with TWO Tables and not 2 databases? — RiggsFolly, Aug 07 '19 at 13:44
For completeness, please show us the schema for these 2 tables. Do a `show create table table_name;` for each table and post the output — RiggsFolly, Aug 07 '19 at 13:46
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Aug 08 '19 at 20:10

ScaisEdge · Answer 1 · 2019-08-07T13:47:38.433

Assuming you have a related table MY_PIC with the PIC name and each rows of the PIC table contain in the product_id column a valid ref to column id in table MRT_PRODUCTS

you could obtain all the values in a single query

  $query = "SELECT P.IDF IDF , P.NAME NAME, P.DETAILS DETAILS, I.PIC  PIC
        FROM MRT_PRODUCTS P 
        INNER JOIN MY_PIC I ON I.product_id = P.id" or die( "Error in the consult.." . mysqli_error( $link ) );
  $result = mysqli_query( $link, $query );
  $rows = array();
  while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {
      $rows[] = array(
          'IDF' => $row[ 'IDF' ],
          'NAME' => $row[ 'NAME' ],
          'DETAILS' => $row[ 'DETAILS' ],
          'PIC' => $row['PIC']
      );
  }

or if you have several pic for the same product and wany all the pic name in the same rows

  $query = "SELECT P.IDF IDF , P.NAME NAME, P.DETAILS DETAILS, group_concat(I.PIC)  PIC
        FROM MRT_PRODUCTS P 
        INNER JOIN MY_PIC I ON I.product_id = P.id
        GROUP BY P.IDF, P.NAME, P.DETAILS" or die( "Error in the consult.." . mysqli_error( $link ) );
  $result = mysqli_query( $link, $query );
  $rows = array();
  while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {
      $rows[] = array(
          'IDF' => $row[ 'IDF' ],
          'NAME' => $row[ 'NAME' ],
          'DETAILS' => $row[ 'DETAILS' ],
          'PIC' => $row['PIC']
      );

with inner join I obtain multiple same result because I have more pics for same products.... and for the value PIC I need multiple results — DigitalXP, Aug 07 '19 at 13:47
Note: It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Aug 08 '19 at 20:12

Create a multidimensional array by the union of two mysql extraction

1 Answers1