MySQL order by problems

[xjshiya]

I have the following codes..

echo "<form><center><input type=submit name=subs value='Submit'></center></form>";

$val=$_POST['resulta']; //this is from a textarea name='resulta'
if (isset($_POST['subs'])) //from submit name='subs'
{
    $aa=mysql_query("select max(reservno) as 'maxr' from reservation") or die(mysql_error()); //select maximum reservno
    $bb=mysql_fetch_array($aa);
    $cc=$bb['maxr'];
    $lines = explode("\n", $val);
    foreach ($lines as $line) {
        mysql_query("insert into location_list (reservno, location) values ('$cc', '$line')")
          or die(mysql_error()); //insert value of textarea then save it separately in location_list if \n is found
    }

If I input the following data on the textarea (assume that I have maximum reservno '00014' from reservation table),

Davao - Cebu
Cebu - Davao

then submit it, I'll have these data in my location_list table:

loc_id || reservno || location
00001  || 00014    || Davao - Cebu
00002  || 00014    || Cebu - Davao

Then this code:

$gg=mysql_query("SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                            location, 
                            SUBSTRING_INDEX(location,' - ', 1)
                           ) 
                           ORDER BY loc_id ASC
                           SEPARATOR ' - ') AS locations

    FROM location_list, 
         (SELECT @cnt := COUNT(1), @var_ctr := 0 
          FROM location_list 
          WHERE reservno='$cc'
         ) dummy
    WHERE reservno='$cc'") or die(mysql_error()); //QUERY IN QUESTION
    $hh=mysql_fetch_array($gg);
    $ii=$hh['locations'];
    mysql_query("update reservation set itinerary = '$ii' where reservno = '$cc'")
      or die(mysql_error());

is supposed to update reservation table with 'Davao - Cebu - Davao' but it's returning this instead, 'Davao - Cebu - Cebu'. I can get it working if I run it in PHPMyAdmin SQL query window but through this code, I just can't get it to work. Please help me. Thanks in advance!

[urtrivedi]

You are depending on the data entered in text area.
This is poor design. If any how you solve your current problem, big unsolvable problems are waiting for you in future

[xjshiya]

@urtrivedi, yes I am depending on the data ordered because the format will be always be like that and it will never change. Please focus on the main goal of this post.

[smantscheff]

If it runs in another interface but not in your code, then your error is in how the query is built in PHP. Debug it. Show us which query actually is posed to the server. What does the string

"SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                            location, 
                            SUBSTRING_INDEX(location,' - ', 1)
                           ) 
                           ORDER BY loc_id ASC
                           SEPARATOR ' - ') AS locations
    FROM location_list, 
         (SELECT @cnt := COUNT(1), @var_ctr := 0 
          FROM location_list 
          WHERE reservno='$cc'
         ) dummy
    WHERE reservno='$cc'"

really resolve to? Which value as $cc? What is the result if you feed the resulting query directly to MySQL?

[urtrivedi]

Actully your query works fine here, but still I have did with different way in the link, I hope this links helps you http://urtrivedi.0fees.net/reservation.html