Result set order changing in php

Question

I have a result set and need to be displayed as it is on the screen.But the problem is while displaying each row of the result set using echo command the order is getting changed.Can anyone say why this is happening and provide me a way to overcome it.Here are my actual and printed outputs.

Actual Result set:

JAIKE-ILENE-WACKI-MAZIE-REGLE-SBJ-KMMU  
LVZ-HARTY-MUGZY-STW 
MAZIE-SIXIE-SBJ-KMMU  
PXT-LOUIE-GATBY-RAZER-BUZIE-JAIKE-ILENE-WACKI-MAZIE  
SWANN-GATBY-RAZER-BUZIE-JAIKE-ILENE-WACKI-MAZIE

Output:

 JAIKE-SBJ-ILENE-KMMU-WACKI-MAZIE-REGLE
 MUGZY-STW-LVZ-HARTY
 SBJ-KMMU-MAZIE-SIXIE
 ILENE-GATBY-WACKI-RAZER-MAZIE-BUZIE-PXT-JAIKE-LOUIE
 WACKI-RAZER-MAZIE-BUZIE-JAIKE-SWANN-ILENE-GATBY

Here is my code

$sql3="SELECT GROUP_CONCAT(l.fix_ident SEPARATOR '-') AS fix_seq,l.airport_ident,x.star_ident,x.transition_ident,
                 x.fix_ident from corept.std_star_leg l
                 JOIN
                    (SELECT DISTINCT c.airport_ident,c.star_ident,c.transition_ident,c.fix_ident
                     FROM corept.std_star_leg c
                     INNER JOIN
                          (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
                           FROM corept.std_star_leg
                           WHERE data_supplier='J'
                           AND airport_ident='KMMU'
                           GROUP BY star_ident,
                           transition_ident)b ON c.sequence_num=b.seq
                           AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
                           LEFT JOIN
                               (SELECT name,trans
                                FROM skyplan_deploy.deploy_stars
                                WHERE apt='KMMU'
                                AND name!=trans) d
                                ON d.name=c.star_ident
                                AND d.trans=c.fix_ident
                                WHERE c.data_supplier='J'
                                AND c.airport_ident='KMMU' AND d.name is null)x
                                where l.airport_ident='KMMU' and l.transition_ident=x.transition_ident
                                and l.star_ident=x.star_ident and l.data_supplier='J'
                                group by x.star_ident,x.transition_ident
                                order by l.star_ident,x.transition_ident,l.sequence_num";
  $res3=mysqli_query($mysqli,$sql3);  
if($res3)
  {
    while($newArray3=mysqli_fetch_array($res3,MYSQLI_ASSOC))
    {
    $apt=$newArray3['airport_ident'];
    $star_ident=$newArray3['star_ident'];
    $trans_ident=$newArray3['transition_ident'];
    $fix_ident=$newArray3['fix_ident'];
    $fix_seq=$newArray3['fix_seq'];
    echo $apt.",".$star_ident.",".$trans_ident.",".$fix_ident.",COREPT,".$fix_seq;
    echo "<br>";
    }
  }
 else
 {
   printf("ERROR:%s\n",mysqli_error($mysqli));
 }

@Adder I didnt gut u.I used connection statements but i omitted them here — user2341693, May 15 '13 at 09:21
Try moving the last "order by" from the joined table into the outermost select. (It is ok, you showed what I wanted to see) — Adder, May 15 '13 at 09:21
No the query is working fine..the problem is with the php code. — user2341693, May 15 '13 at 09:22
See here: http://stackoverflow.com/questions/5432547/group-concat-change-group-by-order There is an ORDER BY clause for the GROUP BY. — Adder, May 15 '13 at 09:54
Thank you for your help.But when i run the query in mysql query browser i am getting the result correctly.But when displayed it using php i am going wrong somewhere. — user2341693, May 15 '13 at 09:58

score 0 · Accepted Answer · answered May 15 '13 at 10:04

Your query looks overly complex. It appears to be finding the groupwise maximum std_star_leg records by sequence_num (grouped on start_ident and transition_ident), excluding those for which there is already a matching non-self-referencing deploy_star, then returning the results grouped again with all matching fix_ident values concatenated into a string?

If so, the following greatly simplified query ought to achieve the same outcome:

SELECT   GROUP_CONCAT(fix_ident SEPARATOR '-') AS fix_seq,
         airport_ident,
         star_ident,
         transition_ident
FROM     corept.std_star_leg l NATURAL JOIN (
           SELECT   star_ident, transition_ident,
                    data_supplier, airport_ident,
                    MAX(sequence_num) sequence_num
           FROM     corept.std_star_leg
           WHERE    data_supplier = 'J'
                AND airport_ident = 'KMMU'
           GROUP BY star_ident, transition_ident
         ) b
WHERE    NOT EXISTS (
           SELECT NULL
           FROM   skyplan_deploy.deploy_stars d
           WHERE  d.name != d.trans
              AND d.name  = l.star_ident
              AND d.trans = l.fix_ident
              AND d.apt   = l.airport_ident
         )
GROUP BY star_ident, transition_ident

Note that whereas you were previously selecting x.fix_ident in the outermost select list, I have omitted such column because its value would be indeterminately selected by the server from amongst those in the fix_seq.

Now, as to your problem (which appears to be related to the order in which fix_ident values appear within the GROUP_CONCAT() string fix_seq—although it's terribly hard to appreciate that from your question), perhaps you want to use the ORDER BY parameter to the GROUP_CONCAT() function? For example:

SELECT GROUP_CONCAT(fix_ident SEPARATOR '-' ORDER BY ...) AS fix_seq

However, it is not clear to me what ordering you require (the only ORDER BY clause in your original query was entirely redundant).

Result set order changing in php

1 Answers1