function getAuthor($user_id){
$sql = "SELECT `username` FROM `users` WHERE `id` = '".$user_id."'";
$res = sqlCount($sql, false);
$row = mysql_fetch_array($res);
return $row['username'];
}
function LastSiteNews($col){
if($col == 0 || $col < 0){
return 'News disabled';
} else {
$sql = "SELECT `id`,`title`,`content`,`date`,`time`,`views` FROM `news` WHERE `category` = '1' ORDER BY `id` DESC LIMIT ".$col;
$res = sqlCount($sql, false);
for($i=0; $i<$col; $i++){
$row = mysql_fetch_array($res);
if($row){
$date = DATEfilter($row['date']);
$temp = array(
'{TITLE}' => $row['title'],
'{ID}' => $row['id'],
'{CONTENT}' => $row['content'],
'{DATE}' => $date,
'{TIME}' => $row['time'],
'{COMMENTS}' => commentsCount($row['id']),
'{VIEWS}' => $row['views'],
'{AUTHOR}' => getAuthor($row['id']),
);
$temp['{CONTENT}'] = BBfilter($temp['{CONTENT}']);
$ret .= toTemplate('tpl/news/main.tpl', $temp);
}
}
return $ret;
}
}
SELECT users.id, users.username, news.id, news.title, news.author_id, news.content, news.category, news.views, news.date, news.time
FROM news LEFT JOIN users ON users.id = news.author_id WHERE category = '1'
ORDER BY news.id DESC LIMIT 5
Zver писал(а):Вот как теперь мне подсчитать кол-во комментариев к новости этим же запросом?
SELECT users.id,
users.username,
news.id,
news.title,
news.content,
news.author_id,
news.category,
news.views,
news.date,
news.time,
news_comments.news_id
FROM news LEFT JOIN users ON users.id = news.author_id
LEFT JOIN news_comments ON news_comments.news_id = news.id
WHERE news.category = '1'
ORDER BY news.id DESC LIMIT 5
Zver писал(а):Добавил, он мне бред выводит:
- Код: Выделить всё • Развернуть
SELECT users.id,
users.username,
news.id,
news.title,
news.content,
news.author_id,
news.category,
news.views,
news.date,
news.time,
news_comments.news_id
FROM news LEFT JOIN users ON users.id = news.author_id
LEFT JOIN news_comments ON news_comments.news_id = news.id
WHERE news.category = '1'
ORDER BY news.id DESC LIMIT
Новости дублируются, каким то путём. Смотрел через print_r();, появляется два одинаковых массива...
SELECT users.id,
users.username,
news.id,
news.title,
news.content,
news.author_id,
news.category,
news.views,
news.date,
news.time,
count(news_comments.news_id)
FROM news LEFT JOIN users ON users.id = news.author_id
LEFT JOIN news_comments ON news_comments.news_id = news.id
WHERE news.category = '1'
GROUP BY news.id DESC LIMIT
$row['count(news_comments.news_id)'];
SELECT users.id,
users.username,
news.id,
news.title,
news.content,
news.author_id,
news.category,
news.views,
news.date,
news.time,
count(news_comments.news_id) AS count_comments
FROM news LEFT JOIN users ON users.id = news.author_id
LEFT JOIN news_comments ON news_comments.news_id = news.id
WHERE news.category = '1'
GROUP BY news.id DESC LIMIT
$row['count_comments'];
Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 4