使用多列的最早日期时间

My table contains 10 datetime columns (item1 - item10), which represent 10 services offered by my members. In any given "city/county" there are only 12 positions available for each of 10 services. Basically, I need the twelve oldest datetimes from each column to be returned respectively as "item1", "item2", "item3", etc., and else as "".

The item#'s are echoed into member's listing, where I use jquery to filter the listings.

My current code sets a limit, but not according to datetime. The problem shows up if all positions on a page are filled. A senior member can then "bump" newer members off if they decide to offer additional services.

In case I'm not explaining this well, here's a working example: http://integritycontractingofva.com/pest_control/Richmond_VA.php

$result = @mysqli_query($db, "SELECT * FROM table WHERE category LIKE '%$category%' AND territories LIKE '%$area.%' AND exp >= NOW()");
if (!$result){echo ("<p>Error performing listing query:" . mysql_error() . "</p>");}

$item1cnt = $item2cnt = $item3cnt = $item4cnt = $item5cnt = $item6cnt = $item7cnt = $item8cnt = $item9cnt = $item10cnt = 0;   //start counter at "0"
$items = array();
$today = date('Y-m-d');
  while ($row = mysqli_fetch_array($result)){
    if($row["exp"] > $today){

      if($row["item1"]!="0000-00-00 00:00:00"){ // if datetime is set
        $item1cnt++;                            // add one to counter
        if($item1cnt > 12){                     // if counter is greater than 12
          $row["item1"]="";                     // itemx = ""
        }else{                                  // if counter is less than 12
          $row["item1"]="item1";                // item = itemx
        }
      }else{                                    // if datetime is not set
          $row["item1"]="";                     // itemx = ""
       }

// repeat above for all 10 items


// part of member's listing used by jquery to filter services
  $items[] = "<li class=\"services " . $row["item1"] . " " . $row["item2"] . " " . $row["item3"] . " " . $row["item4"] . " " . $row["item5"] . " " . $row["item6"] . " " . $row["item7"] . " " . $row["item8"] . " " . $row["item9"] . " " . $row["item10"] . "\">";

  }
}

If a member has datetime set for item1, item3, and item9, the printed result would be <li class="services item1 item3 item9">

Not sure this will work and not sure about performance, but you can try something like this.

SELECT
    t.*,
    CONCAT_WS(' ',
        a1.class,
        a2.class,
        ...
        a10.class
    ) AS class
FROM table t
    LEFT JOIN (SELECT id,'item1' AS class FROM table ORDER BY item1 desc LIMIT 12) a1 ON t.id=a1.id
    LEFT JOIN (SELECT id,'item2' AS class FROM table ORDER BY item2 desc LIMIT 12) a2 ON t.id=a2.id
    ...
    LEFT JOIN (SELECT id,'item10' AS class FROM table ORDER BY item10 desc LIMIT 12) a10 ON t.id=a10.id

and all of this

if($row["item1"]!="0000-00-00 00:00:00"){ // if datetime is set
    $item1cnt++;                          // add one to counter
    if($item1cnt > 12){                   // if counter is greater than 12
        $row["item1"]="";                 // itemx = ""
    }else{                                // if counter is less than 12
        $row["item1"]="item1";            // item = itemx
    }
}else{                                    // if datetime is not set
    $row["item1"]="";                     // itemx = ""
}
// repeat above for all 10 items

can be written simply like this (for all 10 elements):

// config
$item_count = 10;
$show_records = 12;
// process
$cnt = array_fill(1, $item_count, 0);
for ($i = 1; $i <= $item_count; $i++) {
    $n = "item" . $i;
    if (!$row[$n] || $row[$n] === "0000-00-00 00:00:00" || $cnt[$i] >= $show_records) {
        $row[$n] = "";
    } else {
        $cnt[$n]++;
    }
}

Final solution:

// === SELECTING ===
$result = mysqli_query($db, "
    SELECT
        t.*,
        CONCAT_WS(' ',
            a1.class,
            a2.class,
            ...
            a10.class
        ) AS classes
    FROM table t
        LEFT JOIN (SELECT id,'item1' AS class FROM table ORDER BY item1 desc LIMIT 12) a1 ON t.id=a1.id
        LEFT JOIN (SELECT id,'item2' AS class FROM table ORDER BY item2 desc LIMIT 12) a2 ON t.id=a2.id
        ...
        LEFT JOIN (SELECT id,'item10' AS class FROM table ORDER BY item10 desc LIMIT 12) a10 ON t.id=a10.id
    WHERE
        t.category LIKE '%$category%'
        AND t.territories LIKE '%$area.%'
        AND t.exp >= NOW()    
");
if (!$result){
    echo ("<p>Error performing listing query:" . mysql_error() . "</p>");
    exit;
}

// === PARSING ===

// config
$item_count = 10;
$show_records = 12;
// process
$cnt = array_fill(1, $item_count, 0);
for ($i = 1; $i <= $item_count; $i++) {
    $n = "item" . $i;
    if (!$row[$n] || $row[$n] === "0000-00-00 00:00:00" || $cnt[$i] >= $show_records) {
        $row[$n] = "";
    } else {
        $cnt[$n]++;
    }
}

// part of member's listing used by jquery to filter services
$items[] = '<li class="services ' . $row['classes'] . '">';

P.S. You should properly escape $category and $area if it's user input before injecting them in SQL query. This can be done with mysql_real_escape.