原来用RAND()显示随机文章非常占用性能，比如首页每个栏目随机显示文章，开个首页都要几分钟。
现在这样查询，性能好多了。
<?php
function displayContentByCatid($numRecords, $templateFunc, $catid = null) {
define('IN_YZMPHP', true);
define('YZMPHP_PATH', dirname(__FILE__).DIRECTORY_SEPARATOR);
// 包含配置文件
$config = include($_SERVER['DOCUMENT_ROOT'].'/common/config/config.php');
// 使用配置文件中的数据库信息
$db_host = $config['db_host'];
$db_user = $config['db_user'];
$db_pass = $config['db_pwd'];
$db_name = $config['db_name'];
$db_prefix = $config['db_prefix'];
// 使用持久连接
$conn = new mysqli('p:' . $db_host, $db_user, $db_pass, $db_name);
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
try {
// 首先获取子栏目ID集合
$arrchildid = '';
if ($catid !== null) {
$stmt = $conn->prepare("SELECT arrchildid FROM {$db_prefix}category WHERE catid = ?");
$stmt->bind_param("i", $catid);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
$arrchildid = $row['arrchildid'];
}
$stmt->close();
}
// 构建查询语句
$sql_ids = "SELECT id FROM {$db_prefix}article";
$params = [];
$types = "";
if ($arrchildid !== '') {
$sql_ids .= " WHERE FIND_IN_SET(catid, ?)";
$params[] = $arrchildid;
$types .= "s";
}
// 准备和执行查询
$stmt = $conn->prepare($sql_ids);
if (!$stmt) {
throw new Exception("Prepare failed: " . $conn->error);
}
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
if (!$stmt->execute()) {
throw new Exception("Execute failed: " . $stmt->error);
}
$result_ids = $stmt->get_result();
$stmt->close();
$ids = [];
while ($row = $result_ids->fetch_assoc()) {
$ids[] = $row["id"];
}
if (!empty($ids)) {
$numRecords = min($numRecords, count($ids));
$random_keys = array_rand($ids, $numRecords);
$random_keys = is_array($random_keys) ? $random_keys : [$random_keys];
$random_ids = array_map(function($key) use ($ids) {
return $ids[$key];
}, $random_keys);
// 查询随机选择的记录
$placeholders = implode(',', array_fill(0, count($random_ids), '?'));
$sql_random = "SELECT id, title, url, thumb, dexxxion
FROM {$db_prefix}article
WHERE id IN ($placeholders)";
$stmt = $conn->prepare($sql_random);
$stmt->bind_param(str_repeat('i', count($random_ids)), ...$random_ids);
$stmt->execute();
$result_random = $stmt->get_result();
$counter = 0;
while ($row = $result_random->fetch_assoc()) {
$row["displayThumb"] = ($counter === 0);
$templateFunc($row);
$counter++;
}
$stmt->close();
}
} catch (Exception $e) {
error_log("Error in Catid: " . $e->getMessage());
} finally {
$conn->close();
}
}
function disp_all($count = 10, $catid = null) {
displayContentByCatid($count, function($row) use ($catid) {
echo '
<a href="' . $row["url"]. '">
<img src="' .$row["thumb"]. '" alt="'.$row["url"].'">
<p>' .$row["title"]. '</p><p>' .$row["dexxxion"]. '</p>
</a>
';
}, $catid);
}使用方法：
////调用方法 // 显示默认数量（5条）的内容，不指定分类 //disp_all(); // 或者，显示10条内容，不指定分类 //disp_all(10); // 或者，显示特定分类的5条内容 //disp_all(5, 3); // 显示分类ID为3的5条内容 // 或者，显示特定分类的10条内容 //disp_all(10, 3); // 显示分类ID为3的10条内容
比如模板文件里：
{php include $_SERVER['DOCUMENT_ROOT']."/上面文件路径文件名.php";}
{php disp_all(5,3);}即可随机调用分类ID3的5条文章。
1年前
