问题描述
我的表包含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
.
这篇关于使用最早的日期时间处理多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!