多个查询时按字母顺序排序？

Question

我在将它们与已将项目放入其“Locker”中的用户进行匹配时，按字母顺序排序我的数据结果时遇到困难。

我有两个疑问;第一个在数据库中搜索用户放置在他们“储物柜”中的所有物品，第二个查询抽取物品的细节并将它们分类到物品所属品牌的列表中。

我觉得有一个更好的方法来做到这一点，而不是强迫页面为每个项目运行一次查询，但我不确定以最有效的方式写出mySQL的正确方法。

我认为解决方法是将所有ID作为数组，然后以某种方式在第二个查询中搜索并排序所有关联品牌。

我目前有：

//$lockerid is pulled earlier in the code based on which locker number is associated with this user 

    // Pull all of the items and their ids that are in this users locker 
    $userlockerquery= mysql_query("SELECT DISTINCT item_id FROM lockers WHERE user_id = '$profile_userid' AND locker_id ='$lockerid' "); 

    while($lockeritems=mysql_fetch_array($userlockerquery)){ 
      $indi_item=$lockeritems[item_id]; 
      $lockeritemdetails = mysql_query("SELECT DISTINCT brand FROM inventory WHERE id = '$indi_item' "); 
      $brands=mysql_fetch_array($lockeritemdetails); 
      $brandname=$brands[brand]; 
      echo '<div>'.$brandname.'</div>'; 
    } 

虽然结果确实显示了所有的品牌，我的问题似乎是，因为查询对每个项目的ID跑一次，就不能有列表结果互相交谈，因此不能让他们按字母顺序排列，因为查询每个项目都运行一次。

也因此，DISTINCT标志没有任何作用，因为它不匹配任何其他结果。

举个例子，我的结果将返回的div的ID，而不是品牌的订单，并重复：

耐克 彪马 彪马 匡威

不是

匡威 耐克 彪马

将ORDER BY标志添加到第二个查询没有帮助，所以我想我会试着在这里问一些想法。请让我知道是否需要其他细节！

Answer 1

也许尝试类似这个类。看看它是否会满足您的需求。没有尝试sql查询很难检查它，但是如果我已经正确编写它，它应该可以工作。

class MyLocker 
     { 
      // Protected means that you can't use this variable outside of the functions/class 
      // so you can not use $myLocker->_array; It will throw an error 
      protected $_array; 
      // Construct is basically used as an auto-function. It will execute automatically 
      // when you create a new instance of the class so as soon as you do this: 
      // $myLocker = new MyLocker($_locker); you initiate the __construct 
      // When you label as public, you allow it to be used outside of itself 
      public function __construct($_array) 
       { 
        // When you set this variable, it is now open to use in all 
        // other functions in this class. 
        $this->_array = $_array; 
       } 

      // This is the method that will do everything 
      public function LockerContents() 
       { 
        // Loop through query. Since the $_array was set in the __construct 
        // it is available in this function as $this->_array 
        while($lockeritems = mysql_fetch_array($this->_array)){ 
          // $brand is something we want to use in other functions but not 
          // outside the class so it is set here for use in the Fetch() function 
          $this->brand = $lockeritems['item_id']; 
          // We ant to use our Fetch() function to return our brand 
          $_brand   = $this->Fetch(); 
          // If brand available, set it to an array 
          if(!empty($_brand)) 
           $array[] = $_brand; 
         } 
        if(isset($array)) { 
          // Sort the array 
          asort($array); 
          // Finally, we use the Display() function for the final output 
          $this->Display($array); 
         } 
        else { ?> 
          <div>Locker is empty.</div><?php 
         } 
       } 
      // Establish this as an in-class variable 
      protected $brand; 
      // Establish this as a public function incase we want to use it by itself 
      // To do so you would write $myLocker->Fetch(); outside of the class. 
      // Since you need $brand for this function to work, you would need to turn 
      // $brand from "protected" to "public" and write $myLocker->brand = 'whatever'; 
      // before you run the $myLocker->Fetch(); 
      public function Fetch() 
       { 
        $query = mysql_query("SELECT DISTINCT brand FROM inventory WHERE id = '".$this->brand."'"); 
        $brands = mysql_fetch_array($query); 
        // Return brand 
        return (isset($brands['brand']))? $brands['brand']:""; 
       } 

      protected function Display($array) 
       { 
        if(is_array($array)) { 
          foreach($array as $object) { ?> 
           <div><?php echo $object; ?></div><?php 
           } 
         } 
       } 
     } 

    // You should be using mysqli_ or PDO for your db connections/functions. 
    $_locker = mysql_query("SELECT DISTINCT item_id FROM lockers WHERE user_id = '$profile_userid' AND locker_id ='$lockerid' "); 

    // If there are more than 0 rows, create locker. 
    if(mysql_num_rows($_locker) > 0) { 
      // Create new instance of the locker app 
      $myLocker = new MyLocker($_locker); 
      // Display the results 
      $myLocker->LockerContents(); 
     }