Shermaine Chaingan Shermaine Chaingan - 2 months ago 15
PHP Question

Group two column and distinct

I tried this code to output this (Correct Output)

enter image description here

But it gave me this which is wrong output
enter image description here

I need to distinct the name and group the item by brand but it did not happen in my query see the picture above.

$datefilter=mysqli_query($link,"select
SUM(INVOICE_ITEM_AMOUNT_MX) AS totalamount,
INVOICE_NO_MX,
INVOICE_CUSTOMER_NAME
,INVOICE_DATE_MX,ITEM_BRAND_MX
,GROUP_CONCAT(INVOICE_ITEM_CODE_MX SEPARATOR '<BR>') as itemcode
,GROUP_CONCAT(INVOICE_ITEM_QTY_MX SEPARATOR '<BR>') as quantity
,GROUP_CONCAT(INVOICE_ITEM_UPRICE_MX SEPARATOR '<BR>') as price
,GROUP_CONCAT(INVOICE_ITEM_SERIAL_MX SEPARATOR '<BR>') as serial
,GROUP_CONCAT( DISTINCT (INVOICE_SALES_REP_MX) SEPARATOR '<BR>') as salesrep

,GROUP_CONCAT(DISTINCT (ITEM_BRAND_MX) SEPARATOR '<BR>') as brand


from invoice where INVOICE_DATE_MX BETWEEN '" .$datefrom. "' AND '" . $dateto ."' and
BRANCH_CODE_MX='".$display_branchcode."' AND INVOICE_STATUS_MX='PAID' GROUP BY INVOICE_SALES_REP_MX,ITEM_BRAND_MX ");

$count=mysqli_num_rows($datefilter);
echo '<div class="col-xs-12">

<br>
<div class="col-xs-6">
<label> Total Invoice Number: </label> '.$count.'
</div>
<div class="col-xs-6">
<label> Date Range: </label> '.$convertedfrom.' - '.$convertedto.'
</div>
</div>';


while($daterow=mysqli_fetch_array($datefilter)){
$date=$daterow["INVOICE_DATE_MX"];
$customername=$daterow["INVOICE_CUSTOMER_NAME"];
$invoiceref=$daterow["INVOICE_NO_MX"];
$salesrep=$daterow["salesrep"];
$itemcode=$daterow["itemcode"];
$imeiserial=$daterow["serial"];
$totalnotformat=$daterow["totalamount"];
$upricenotformat=$daterow["price"];
$quantity=$daterow["quantity"];
$itembrand=$daterow["brand"];

$total=number_format($totalnotformat,2);
$uprice=number_format($upricenotformat,2);

echo'
<div class="col-xs-12">
<br>
<div class="panel panel-warning">
<div id="shade" class="panel-body"><label>'.$salesrep.'</label></div>
<div id="shade" class="panel-body">
<div class="col-xs-10">
<div class="col-xs-4">';
echo '
<label>Brand</label>: '.$itembrand.'<br>
<div class="col-xs-7">
<label>Item Code</label> <br>'.$itemcode.'

</div>
<div class="col-xs-1">
<label> Quantity</label> <br>'.$quantity.'

</div>
</div>



</div>

</div>

Answer

I guess, the following should help and what you wanted I mean to show distinct names. But you could do it with PHP easily:

SELECT CASE WHEN t.id = (SELECT t2.id FROM process t2
WHERE t2.projectNo = t.projectNo
GROUP BY t2.projectNo LIMIT 1) THEN t.projectNo
ELSE
''
END AS Deatils, t.stuff
FROM process t

Sample input and output:

mysql_distinct

mysql_distinct_02

Comments