PHP MySQL display specific results from GROUP_CONCAT

Question

I have a table like this:

Company                                        CompanyEntity        OrderNumber       DeliveryDate 
Deeway HS,Deeway Pearl HS,Deeway Stony HS     20779,20780,1273     4608580,4608558    2020-11-11
Zaper LTD,Zaper CP LTD                           9995,4295         4630230,4607371    2020-11-11
Wilder                                             4224            6630210,7601371    2020-11-11

On that SAME table i have another column like the following where the user can click and see all serial/items listed(i didnt add the column to the table above because the formatted doesnt look good on this post:

         View/Edit                                      Company
     <a href="$id1">View</a>              Deeway HS,Deeway Pearl HS,Deeway Stony HS
     <a href="$id2">View</a>                     Zaper LTD,Zaper CP LTD
     <a href="$id3">View</a>                              Wilder

Here's a SQL Fiddle

When i click on the View Edit button, i want it to load another table like this

  Company            OrderNumber     Date          Serial     Items
Deeway HS.             4608580     2020-11-11       SN1       Item1 
Deeway Pearl HS        4608580     2020-11-11       SN2       Item2 
Deeway Stony HS        4608558     2020-11-11       SN3       Item3

I tried the following but its missing records:

 SELECT
 nextgenorder_id,
 GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
 GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity,
 GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
 nextgenorder_deliverydate,
 nextgenorder_item,
 nextgenorder_serialnumber
 FROM nextgenorders2
 WHERE 
 (nextgenorder_ordernumber='$nextgenorder_ordernumber' AND nextgenorder_deliverydate='$nextgenorder_deliverydate') 
 OR
 (nextgenorder_company_entity='$nextgenorder_company_entity' AND nextgenorder_deliverydate='$nextgenorder_deliverydate') 
 OR
 (nextgenorder_companyname='$nextgenorder_companyname' AND nextgenorder_deliverydate='$nextgenorder_deliverydate')
 OR
 (nextgenorder_shiptoadd1='$nextgenorder_shiptoadd1' AND nextgenorder_deliverydate='$nextgenorder_deliverydate')

Here's a SQL Fiddle

*Side note, sometimes a serial number is allocated at a later date. There's not always a serial number.

Thank you

what is missing? The query does exactly what you programmed, you only selected with your where clauses the first two rows, because of nextgenorder_ordernumber='4608580' there rest indicate only the first row, so show us what you exactly want as result. — nbk
– nbk, Commented Feb 23, 2020 at 22:33
On the SQL Fiddle, it doesn't show the $nextgenorder_ordernumber $nextgenorder_company_entity and $nextgenorder_companyname variables. Yes i know this is why i'm not getting all records. That's my issue. How can i make the query check each value? — Kyocera Alerts
– Kyocera Alerts, Commented Feb 23, 2020 at 22:43

nbk · Accepted Answer · 2020-02-24 00:33:05Z

1

You can use another where clause

 SELECT
 nextgenorder_id,
 GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
 GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity,
 GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
 nextgenorder_deliverydate,
 GROUP_CONCAT(DISTINCT nextgenorder_item) nextgenorder_item,
 GROUP_CONCAT(DISTINCT  nextgenorder_serialnumber) nextgenorder_serialnumber
 FROM nextgenorders2
 WHERE 
 (nextgenorder_companyname LIKE CONCAT(SUBSTRING_INDEX('Deeway  HS', ' ', 1),'%') AND nextgenorder_deliverydate='2020-11-11')

sqlfiddle example

edited Feb 24, 2020 at 0:33

answered Feb 23, 2020 at 22:55

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kyocera Alerts Over a year ago

If the company name is Deeway X it works but if the company name is Deeway X X it doesnt work.

nbk Over a year ago

Yes of course it does work With every Deeway. See sqlfiddle.com/#!9/9f9646d/1 Please show me an example where it doesn't work in sql fiddle. You should think about insertig a cloumn for The mither company insteaed of relaying in the name

Kyocera Alerts Over a year ago

I found the issue. The variable is Deeway HS, not Deeway so when i do the WHERE clause its going (nextgenorder_companyname LIKE 'Deeway HS %' and it doesn't capture the other records. I guess i'll have to do a substring on the variable first but this doesn't seem like it's going to be 100 percent accurate all the time. Do you have any other solutions that's more accurate?

nbk Over a year ago

I have edited my answer but as i said before, names are bad for that kind of thing, because what happen when it is call "Sa Deeway inc." and belongs to the same conglomerate. unite companys that belong together with the same identifying id

Kyocera Alerts Over a year ago

Unfortunately, I don't have complete control of the data. The only unique identifiers I have are the company name, order number and company entity and none of them are truly unique identifying ids. Any suggestions are welcomed.

|

Collectives™ on Stack Overflow

PHP MySQL display specific results from GROUP_CONCAT

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related