MySQL按问题排序 [英] MySQL order by problems

问题描述

我有以下代码.

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
    }

如果我在textarea上输入了以下数据(假设我在预订表中拥有最大的预订号'00014')，

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

Davao - Cebu
Cebu - Davao

然后提交它，我将在我的location_list表中保存这些数据:

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

然后输入此代码:

$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());

应该用'Davao - Cebu - Davao'更新保留表，但它会返回它，而返回'Davao - Cebu - Cebu'.该论坛以前曾帮助我使此代码正常工作，但现在我又遇到了另一个困难.只是无法使其正常工作.请帮我.预先感谢！

is supposed to update reservation table with 'Davao - Cebu - Davao' but it's returning this instead, 'Davao - Cebu - Cebu'. I was previously helped by this forum to have this code working but now I'm facing another difficulty. Just can't get it to work. Please help me. Thanks in advance!

推荐答案

只要我将phpMyAdmin操作loc_id设置为递增，我就可以正常工作(不使用ORDER BY loc_id ASC).但是，无论何时删除所有数据，它都会以loc_id降序返回，因此我必须将其重置.它不能完全解决问题，但我想这是我所能做到的. :))我只需要确保表列loc_id总是按升序排列.谢谢你们每一个人的帮助！对此，我真的非常感激！但是，如果您有更好的答案，例如如何始终以升序设置表格列或更好的查询等，请随时在此处发布.愿上帝保佑你们所有人！

I got it working (without ORDER BY loc_id ASC) as long as I set phpMyAdmin operations loc_id ascending. But whenever I delete all data, it goes back as loc_id descending so I have to reset it. It doesn't entirely solve the problem but I guess this is as far as I can go. :)) I just have to make sure that the table column loc_id is always in ascending order. Thank you everyone for your help! I really appreciate it! But if you have any better answer, like how to set the table column always in ascending order or better query, etc, feel free to post it here. May God bless you all!

这篇关于MySQL按问题排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！