使用最早的日期时间处理多列

使用最早的日期时间处理多列

本文介绍了使用最早的日期时间处理多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的表包含10个datetime列( item1 - item10 ),代表我的成员提供的10项服务.在任何给定的城市/县"中,每10个服务中只有12个职位可用.基本上,我需要将每列中最早的十二个日期时间分别返回为"item1","item2","item3"等,否则返回为".

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 "".

item#被回显到成员的列表中,在这里我使用jquery来过滤列表.

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.

如果我不能很好地解释这一点,下面是一个有效的示例: http://integritycontractingofva.com/pest_control/Richmond_VA.php

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"] . "\">";

  }
}

如果成员为 item1 item3 item9 设置了日期时间,则打印结果将为< li class=服务item1 item3 item9">

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

可以这样简单地写(对于所有10个元素):

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]++;
    }
}


最终解决方案:

// === 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.如果将 $ category $ area 作为用户输入,则应在将它们插入SQL查询之前正确地对其进行转义.这可以通过 mysql_real_escape 完成.

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.

这篇关于使用最早的日期时间处理多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-28 05:31
查看更多