while mysqli_fetch_assoc to work while theres columns instead of rows

Question

Sorry i didn't know how to make sense of this in the title.

Basically i have a table with two row in.

now i want to show the reference and then all the doc columns on my page.

However you can see i have the same reference on two rows. if the user uploads documents with the same ref i want to be able to show all the docs, under the reference.

For example i want to look like this on page:

11111111
word.jpg
a17.gif
Matthew Smart CV.docx
word.jpg
a17.png
Matthew Smart CV.docx

This is what i have done so far and got stuck

PHP/MYSQL:

    <?php

    $query  = "SELECT * ";
    $query .= "FROM customers ";
    $query .= "WHERE reference = 11111111";
    $results = mysqli_query($connection, $query);

    $cnt = 0;
    $customer_doc = array();
    while($customer_details = mysqli_fetch_assoc($results)) {
        $customer_ref = $customer_details["reference"];
        $cnt ++;
        $customer_doc[] = $customer_details['doc' . $cnt];
    }
    echo $customer_ref;


    ?>
    <pre>
        <?php
        print_r($customer_doc);
        ?>
    </pre>

I dont know how to do this.

Can anyone be kind enough to help me so i can learn from it?

Answer 1

Assuming you would eventually be querying for more than one reference value in a result set, the technique CBroe is mentioning in the main comment thread involves storing the last value for reference on each iteration of the while loop. If the value has changed from the previous one, you print the output. If it is the same, you do not need to print it in that loop and can instead just print the other columns.

Option 1 compare variable to last iteration's value:

Note: I'm doing this with extremely raw output here, just bare echo with a linebreak.

// Start with an empty value for the "last one"
$last = null;
while ($customer_details = mysqli_fetch_assoc($results)) {
  // Check if the newly fetched row differs from the previous value
  if ($customer_details['reference'] != $last) {
    // It is different! Print it out
    echo $customer_details['reference']  . "\n";
  }
  // Store this current one into the $last var for 
  // comparison on the next round
  $last = $customer_details['reference'];

  // Print the other columns...
  echo $customer_details['doc1'] . "\n";
  echo $customer_details['doc2'] . "\n";
  echo $customer_details['doc3'] . "\n";
}

Option 2 - indexed array storage (probably the best option, certainly the one I would use)

Now, suppose you want to store these into an array. This would be handy because you can make an array indexed by the reference column, with sub-arrays for the other columns. This is easier and you don't need to check if the value changes. You need only to append a sub-array on every while iteration.

// Array to hold all results:
$all = array();
while ($customer_details = mysqli_fetch_assoc($results)) {
  // Append a new sub-array of the 3 other cols to
  // the main array's key by reference using the []
  // array append syntax
  $all[$customer_details['reference']][] = array(
    'doc1' => $customer_details['doc1'],
    'doc2' => $customer_details['doc2'],
    'doc3' => $customer_details['doc3']
  );
}

The array now looks like

Array (
  '1111111' => Array (
     Array (
       'doc1' => 'word.jpg'
       'doc2' => 'a17.gif',
       'doc3' => 'Matthew Smart CV.docx'
     ),
     Array (
       'doc1' => 'word.jpg'
       'doc2' => 'a17.gif',
       'doc3' => 'Matthew Smart CV.docx'
     )
  ),
  '222222' => Array (
     Array (
       'doc1' => 'xyz.jpg'
       'doc2' => 'z17.gif',
       'doc3' => 'Matthew Smart CV.pdf'
     ),
     Array (
       'doc1' => 'xyz.jpg'
       'doc2' => 'z17.gif',
       'doc3' => 'Matthew Smart CV.pdf'
     ),
)

And so you can loop over it with a nested foreach. The outer gets the reference and the inner gets the other values:

foreach ($all as $reference => $rows) {
  // Write out the reference, which was the array key
  echo $reference . "\n";
  // Then in a loop, write out the others
  foreach ($rows as $row) {
    echo $row['doc1'] . "\n";
    echo $row['doc2'] . "\n";
    echo $row['doc3'] . "\n";
  }
}

Option 3 - query hack:

This last one is a GROUP BY hack you can use on your query. I don't exactly recommend it, but want to show it is possible. If you use GROUP_CONCAT() together with a normal CONCAT_WS(), you can produce in one row, the reference, followed by all the other documents separated by something like ||. In PHP then, you just need one loop and to explode() on the delimiter ||.

$query = "SELECT reference, GROUP_CONCAT(CONCAT_WS('||', doc1, doc2, doc3) SEPARATOR '||') AS docs FROM customers GROUP BY reference";

This would produce rows literally structured like:

1111111, word.jpg||a17.gif||Matthew Smart CV.docx||word.jpg||a17.gif||Matthew Smart CV.docx

That is, the reference in a column, then all other strings joined by || as a column called docs.

// Execute the query, then fetch.
while ($customer_details = mysqli_fetch_assoc($results)) {
  echo $customer_details['reference'] . "\n";

  // Then explode() the || delimited string with linebreaks
  echo implode("\n", explode("||", $customer_details['docs']));
}

Again, I don't recommend actually using this, but it can be done this way.

Answer 2

You need "group by reference" in array:

$customer_docs = [];
while($customer_details = mysqli_fetch_assoc($results)) 
    $customer_doc[$customer_details["reference"]][] = $customer_details;


foreach($customer_docs as $reference=>$docs){
    echo $reference;

    foreach($docs as $doc){
        echo $doc['doc1'];
        //...
    }  
    // "breakline"     
}

Or "classic way": add temp variable for current reference before while-loop, and every iteration compare previous reference with current.