我正试图按某些字母数字范围1-3 &A-B前15场比赛,但我没有得到任何输出。使用MeekroDB。不知道如何将我这里的内容转换为他们的方法。
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet
FROM gallery ORDER BY (CASE theme
WHEN '1%' THEN 1
WHEN '2%' THEN 2
WHEN '3%' THEN 3
WHEN 'A%' THEN 4
WHEN 'B%' THEN 5
ELSE -1
END) LIMIT 15");
$x = 0
foreach ($results as $row) {
$x++;
if ($x == 1) { // first in query
$t1 = $row['theme'];
$d1 = $row['developer'];
$th1 = $row['thumb'];
$thlg1 = $row['thumb_lg'];
}
...
}
正文中的Echo示例:
<img src="<?php echo($th1); ?>" data-retina="<?php echo($thlg1); ?>" alt="<?php echo($t1); ?>" />
<span><p class="hname"><?php echo($t1); ?></p>
<p class="hdev"><?php echo($d1); ?></p></span>
更新2:
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg
FROM gallery ORDER BY (CASE
WHEN theme LIKE '1%' THEN 1
WHEN theme LIKE '2%' THEN 2
WHEN theme LIKE '3%' THEN 3
WHEN theme LIKE 'A%' THEN 4
WHEN theme LIKE 'B%' THEN 5
ELSE -1
END) LIMIT 15");
Update3:
$results = DB::query("SELECT substr(theme, 1, 1) as Alphabet, theme, developer, thumb, thumb_lg FROM gallery
ORDER BY (CASE Alphabet
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN 'A' THEN 4
WHEN 'B' THEN 5
ELSE 6
END)
LIMIT 15");
ORDER BY
子句应为:
ORDER BY CASE Alphabet
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN 'A' THEN 4
WHEN 'B' THEN 5
ELSE 6
END, theme
你的代码正在对整个主题进行精确匹配,'1%'应该使用LIKE
进行匹配。我的版本只使用第一个字符,你已经提取到Alphabet
。
ORDER BY IF(LOCATE(Alphabet, "123AB"), 0, 1), theme
这可以工作,因为您的第一个字符的顺序符合正常的字典顺序。