在将数据结果与已将项目放入"储物柜"的用户进行匹配时，我很难按字母顺序对其进行排序。
我有两个问题；第一个查询在数据库中搜索用户放在"储物柜"中的所有物品，第二个查询提取物品的详细信息，并将其分类到物品的品牌列表中。
我觉得有一种更好的方法可以做到这一点，而不是强迫页面为每个项目运行一次查询，但我不确定以最有效的方式编写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都会运行一次查询，因此无法让列表结果相互交流，因此无法按字母顺序按ASC排序，因为每个商品都会运行查询一次。
也正因为如此，DISTINCT标志没有任何效果，因为它与任何其他结果都不匹配。
举个例子，我的结果将按ID而不是品牌的顺序以div形式返回，并重复：
Nike彪马彪马匡威
而不是
匡威Nike彪马
将ORDERBY标志添加到第二个查询中没有帮助，所以我想我会尝试在这里询问一些想法。如果需要任何其他详细信息，请告诉我！
也许可以试试这个类。看看它是否能满足你的需求。如果不尝试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();
}