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'] . "
";
}
// 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'] . "
";
echo $customer_details['doc2'] . "
";
echo $customer_details['doc3'] . "
";
}
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 . "
";
// Then in a loop, write out the others
foreach ($rows as $row) {
echo $row['doc1'] . "
";
echo $row['doc2'] . "
";
echo $row['doc3'] . "
";
}
}
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'] . "
";
// Then explode() the || delimited string with linebreaks
echo implode("
", explode("||", $customer_details['docs']));
}
Again, I don't recommend actually using this, but it can be done this way.