PHP MySQL多维数组按条件日期排序

I am trying to sort by modification date descending (meaning latest first) within a multi-dimensional array with the condition that status = 0 (unrated image) and status != 1 (rated image).

我试图通过状态= 0(未分级图像)和状态!= 1(额定图像)的多维数组中的修改日期降序(意味着最新的第一个)进行排序。

Is it possible to sort the array so that the top 2 would be following the logic below:

是否可以对数组进行排序,以便前2个遵循以下逻辑:

If (there are multiple versions of an image)
   If (there is a version that has been rated)
      If (there is a new unrated version of the image)
         Display (the most recent version that has a rating) and 
         (the most recent unrated version) as top 2 of array
      Else
         Display (the most recent version that has a rating) as top 1
   Else
      Display (the most recent unrated version) as top 1
Else
   Display (the only version) as top 1

---

Here's my UPDATED PHP code below (UPDATED 9/6/2013):

这是我下面的更新PHP代码(更新时间:9/6/2013):

updateImageDisplayStatus($caseID,$patientID);

function updateImageDisplayStatus($caseid,$patientid) {

$connection = getMySqlConnection();
$sql = "SELECT image_name FROM images WHERE patientid = '{$patientid}' AND caseid = '{$caseid}' ";

$imageGroup = array();
$result = mysql_query($sql,$connection);
while($row = mysql_fetch_assoc($result)) {

    $fileName =  $row['image_name'];
    $tmpName = $row['image_name'];
    list($leftN,$rightN) = explode($caseid."_",$tmpName);
    //$spltName = explode('_',$rightN);
    $shortFileName = $rightN;
    list($noExtensionFileName,$extension) = explode('.',$shortFileName);
    //$extension = '.nii.gz';
    list($groupName,$appendDate) = explode('_',$noExtensionFileName);
    // echo $groupName = just the original name.

  $sql_2 = "SELECT image1.image_name as i1_image_name
  ,image1.comments   as i1_comments
  ,image1.status     as i1_status
  ,image1.mod_date   as i1_mod_date
  ,image2.image_name as i2_image_name
  ,image2.comments   as i2_comments
  ,image2.status     as i2_status
  ,image2.mod_date   as i2_mod_date
  FROM (SELECT CASE WHEN status = 0 THEN 1 ELSE 0 END as rated
          ,image_name
          ,comments
          ,status
          ,mod_date
      FROM images
     WHERE image_name like '%{$groupName}%' 
     ORDER BY 1 ASC, mod_date DESC
     LIMIT 1
   ) image1
  LEFT OUTER
  JOIN images image2
    ON image1.status > 0
   AND image2.status = 0
   AND image2.mod_date > image1.mod_date
   AND image2.image_name like '%{$groupName}%'
      AND patientid = '{$patientid}' AND caseid = '{$caseid}'
 ORDER BY image2.mod_date DESC
 LIMIT 1";

$result_2 = mysql_query($sql_2,$connection);
if($row = mysql_fetch_assoc($result_2)) {
    $imageGroup[$groupName]   = array();
    $imageGroup[$groupName][] = array(
        'image_name'  => $row['i1_image_name'],
        'comments'    => $row['i1_comments'], 
        'status'      => $row['i1_status'],
        'mod_date'    => $row['i1_mod_date']
        );
    if ( !empty( $row['i2_image_name'] ) ) {
        $imageGroup[$groupName][] = array(
            'image_name'  => $row['i2_image_name'],
            'comments'    => $row['i2_comments'], 
            'status'      => $row['i2_status'],
            'mod_date'    => $row['i2_mod_date']
        );
    }
}
echo "Group $groupName:<br />";
echo "Size of Group:".sizeof($imageGroup[$groupName]).'<br />';

echo $imageGroup[$groupName][0]['image_name'].'<br />';
echo $imageGroup[$groupName][1]['image_name'].'<br />';
}
}

---

UPDATE:

Thanks to @gwc for the suggestion to let SQL do the work. I forgot to add that the 1-2 images that are searched and sorted have to be within the specified $caseid and $patientid. As a result, the first sql statement should be: $sql = "SELECT image_name FROM images WHERE patientid = '{$patientid}' AND caseid = '{$caseid}' ";

感谢@gwc建议让SQL完成工作。我忘了补充说,搜索和排序的1-2个图像必须在指定的$ caseid和$ patientid内。因此,第一个sql语句应为:$ sql =“SELECT image_name FROM images WHERE patientid ='{$ patientid}'AND caseid ='{$ caseid}'”;

2 个解决方案

#1

Updated SQL to limit to within the same patient and case

更新了SQL以限制在同一患者和病例内

If I understand your problem correctly, you could let SQL do the work for you. No loops or arrays required. The following SQL should return a single row. Unfortunately, I don't have a way to test this at the moment so I hope there are no errors in the SQL. This SQL would replace your $sql_2:

如果我正确理解您的问题,您可以让SQL为您完成工作。不需要循环或数组。以下SQL应返回单行。不幸的是,我目前没有办法测试这个,所以我希望SQL中没有错误。这个SQL会替换你的$ sql_2:

SELECT image1.patientid
      ,image1.caseid
      ,image1.image_name as i1_image_name
      ,image1.comments   as i1_comments
      ,image1.status     as i1_status
      ,image1.mod_date   as i1_mod_date
      ,image2.image_name as i2_image_name
      ,image2.comments   as i2_comments
      ,image2.status     as i2_status
      ,image2.mod_date   as i2_mod_date
  FROM (SELECT CASE WHEN status = 0 THEN 1 ELSE 0 END as rated
              ,patientid
              ,caseid
              ,image_name
              ,comments
              ,status
              ,mod_date
          FROM images
         WHERE patientid  = '{$patientid}'
           AND caseid     = '{$caseid}'
           AND image_name like '%{$group_name}%'
         ORDER BY 1 ASC, mod_date DESC
         LIMIT 1
       ) image1
  LEFT OUTER
  JOIN images image2
    ON image1.status     > 0
   AND image2.patientid  = image1.patientid
   AND image2.caseid     = image1.caseid
   AND image2.status     = 0
   AND image2.mod_date   > image1.mod_date
   AND image2.image_name like '%{$group_name}%'
 ORDER BY image2.mod_date DESC
 LIMIT 1

The returned row contains 10 columns:

返回的行包含10列:

• patientid
• caseid
• i1_image_name
• i1_comments
• i1_status
• i1_mod_date
• i2_image_name
• i2_comments
• i2_status
• i2_mod_date

The i1_* columns are from the first image and the i2_* columns are from the second image (if there is one). If there is a rated image, i1_* will represent the most recently modified rated image and i2_* will represent the most recently modified unrated image that was modified after the i1_* image. If there are not unrated images that were modified after the rated image then i2_* will be NULL. If there are no rated images, then i1_* will represent the most recently modified unrated image and i2_* will be NULL.

i1_ *列来自第一个图像,i2_ *列来自第二个图像(如果有的话)。如果存在评级图像,则i1_ *将表示最近修改的评级图像,而i2_ *将表示在i1_ *图像之后修改的最近修改的未评级图像。如果没有在评级图像之后修改的未评级图像,则i2_ *将为NULL。如果没有评级图像,则i1_ *将表示最近修改的未评级图像,i2_ *将为NULL。

Updated

Here's how I see it being used.

这是我看到它被使用的方式。

    $sql_2 = "SELECT image1.patientid
      ,image1.caseid
      ,image1.image_name as i1_image_name
      ,image1.comments   as i1_comments
      ,image1.status     as i1_status
      ,image1.mod_date   as i1_mod_date
      ,image2.image_name as i2_image_name
      ,image2.comments   as i2_comments
      ,image2.status     as i2_status
      ,image2.mod_date   as i2_mod_date
  FROM (SELECT CASE WHEN status = 0 THEN 1 ELSE 0 END as rated
              ,patientid
              ,caseid
              ,image_name
              ,comments
              ,status
              ,mod_date
          FROM images
         WHERE image_name like '%{$group_name}%'
         ORDER BY 1 ASC, mod_date DESC
         LIMIT 1
       ) image1
  LEFT OUTER
  JOIN images image2
    ON image1.status > 0
   AND image2.patientid  = image1.patientid
   AND image2.caseid     = image1.caseid
   AND image2.status     = 0
   AND image2.mod_date   > image1.mod_date
   AND image2.image_name like '%{$group_name}%'
 ORDER BY image2.mod_date DESC
 LIMIT 1";

    $result_2 = mysql_query($sql_2,$connection);
    if($row = mysql_fetch_assoc($result_2)) {
        $imageGroup[$groupName]   = array();
        $imageGroup[$groupName][] = array(
            'image_name'  => $row['i1_image_name'],
            'comments'    => $row['i1_comments'], 
            'status'      => $row['i1_status'],
            'mod_date'    => $row['i1_mod_date']
            );
        if ( !empty( $row['i2_image_name'] ) ) {
            $imageGroup[$groupName][] = array(
                'image_name'  => $row['i2_image_name'],
                'comments'    => $row['i2_comments'], 
                'status'      => $row['i2_status'],
                'mod_date'    => $row['i2_mod_date']
            );
        }
    }

replaces:

if (!isset($imageGroup[$groupName]) {   
    $imageGroup[$groupName] = array();

    $sql_2 = "SELECT * FROM images WHERE image_name like '%".$groupName."%'";
    $result_2 = mysql_query($sql_2,$connection);
    while($row - mysql_fetch_assoc($result_2)) {
        $imageGroup[$groupName][] = array( // sort each field as 
            'image_name' => $row['image_name'],
            'comments' => $row['comments'], 
            'status' => $row['status'], // determines whether image is rated or unrated
            'mod_date' => $row['mod_date'] // the date we need to compare
            );
    }
}
else {
    for ($i=0; $i <sizeof($imageGroup[$groupName]); $i++) {
        if ($imageGroup[$groupName][$i]['status'] == 0) {
        // compare??    

        }
        if ($imageGroup[$groupName][$i]['status'] !== 0) {
        // compare??    

        }


    }
}

upon completion, $imageGroup[$groupName] will contain an array with either one or two elements.

完成后,$ imageGroup [$ groupName]将包含一个包含一个或两个元素的数组。

Hope this is on target. :)

希望这是目标。 :)

#2

Here's an old bit of code you can use:

这是您可以使用的旧代码:

class ProductHelper {

// sort products by name
static function sort_name_asc($a,$b) {
    return strcmp($a['name'],$b['name']);
}
static function sort_name_desc($a,$b) {
    return strcmp($b['name'],$a['name']);
}
// sort products by price
static function sort_price_asc($a,$b) {
    return $a['price'] > $b['price'];
}
static function sort_price_desc($a,$b) {
    return $a['price'] < $b['price'];
}
// sort products by last update
static function sort_date_asc($a,$b) {
    return $a['created'] > $b['created'];
}
static function sort_date_desc($a,$b) {
    return $a['created'] < $b['created'];
}
// sort array by sort_order
static function sort_sort_order($a,$b) {
    return $a['sort_order'] > $b['sort_order'];
}

    function sortProducts($products, $sortMode) {
        if (!is_array($products)) return null;
        usort($products, 'ProductHelper::sort_'.$sortMode);
        return $products;
    }

}

Example use:

$ProductHelper = new ProductHelper();
$ProductHelper->sortProducts($array_of_products, "date_desc");