สอบถามเรื่องการทำ เว็บบอร์ด ให้เรียงลำดับกระทุ้ จาก ผู้แสดงความคิดเห็นล่าสุด หน่อยครับ
Code (SQL)
SELECT
topic.t_id,
topic.topic,
topic.t_date,
topic.t_username,
topic.`view`,
topic.reply,
reply.r_username,
reply.r_date
FROM topic
LEFT JOIN reply ON reply.t_id = topic.t_id
ORDER BY reply.t_id DESC
ได้บ่
Date :
2015-01-23 23:58:20
By :
yamcrocodile
ขอดู code ได้บ่ ครับ
Date :
2015-01-24 15:20:06
By :
yamcrocodile
เพิ่มอีก 1 ฟิลที่ตาราง topic ใช้เก็บวันเวลาที่มีการแสดงความคิดเห็น เวลาทำการ insert ความคิดเห็นก็ให้อัพเดทเวลาที่ topic ด้วย แล้วก็ Order by ชื่อฟิวที่เก็บเวลา(ฟิลที่เพิ่มใหม่) DESC
น่าจะเป็นวิธีที่เข้าใจง่ายสุดล่ะ
แนะนำฟิวที่เพิ่มใหม่ให้ใช้ type เป็น datetime ครับ
Date :
2015-01-24 17:09:12
By :
pgm
ตอบความคิดเห็นที่ : 3 เขียนโดย : yamcrocodile เมื่อวันที่ 2015-01-24 15:20:06
รายละเอียดของการตอบ ::
อันนี้โค้ดครับ
Code (PHP)
<?php require_once('Connections/Project.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_Project, $Project);
$query_topic = "SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ORDER BY reply.t_id DESC";
$topic = mysql_query($query_topic, $Project) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<table width="98%" border="0" align="center" cellpadding="5" cellspacing="0" class="table">
<tr class="font_white">
<td width="8%" align="center" bgcolor="#0000FF"><strong>รหัส</strong></td>
<td width="52%" align="center" bgcolor="#0000FF"><strong>หัวข้อ</strong></td>
<td width="12%" align="center" bgcolor="#0000FF"><strong>ตอบโดย</strong></td>
<td width="10%" align="center" bgcolor="#0000FF"><strong>ตอบล่าสุด</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>อ่าน</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>ตอบ</strong></td>
</tr>
<?php do { ?>
<tr>
<td align="center"><?php echo $row_topic['t_id']; ?></td>
<td><a href="#" class="blue"><?php echo $row_topic['topic']; ?></a><br />
<span style="font-size:12px">โดย : <?php echo $row_topic['username']; ?> เมื่อ : <?php echo $row_topic['t_date']; ?></span></td>
<td><?php
if($row_topic['r_username']!=""){
echo $row_topic['r_username'];
}else{
echo "ยังไม่มีผู้ตอบ";
}
?></td>
<td align="center"><?php
if($row_topic['r_date']!=""){
echo $row_topic['r_date'];
}else{
echo "-";
}?></td>
<td align="center"><?php echo $row_topic['view']; ?></td>
<td align="center"><?php echo $row_topic['reply']; ?></td>
</tr>
<?php } while ($row_topic = mysql_fetch_assoc($topic)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($topic);
?>
ประวัติการแก้ไข 2015-01-24 19:35:22
Date :
2015-01-24 17:33:40
By :
team000000
ตอบความคิดเห็นที่ : 5 เขียนโดย : meannerss เมื่อวันที่ 2015-01-24 17:21:56
รายละเอียดของการตอบ ::
ผมลองทำตามที่บอกดูแล้วนะครับ ไม่ได้อะครับ
Code (SQL)
SELECT *
FROM (SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.t_username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ) AS inv
ORDER BY reply.t_id DESC
มันขึ้น Unknown column 'reply.t_id' in 'order clause'
Code (PHP)
<?php require_once('Connections/Project.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_Project, $Project);
$query_topic = "SELECT * FROM (SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.t_username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ) AS inv ORDER BY reply.t_id DESC";
$topic = mysql_query($query_topic, $Project) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);mysql_select_db($database_Project, $Project);
$query_topic = "SELECT * FROM (SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.t_username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ) AS inv ORDER BY reply.t_id DESC";
$topic = mysql_query($query_topic, $Project) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);
$query_topic = "SELECT * FROM (SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ) AS inv ORDER BY reply.t_id DESC";
$topic = mysql_query($query_topic, $Project) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<table width="98%" border="0" align="center" cellpadding="5" cellspacing="0" class="table">
<tr class="font_white">
<td width="8%" align="center" bgcolor="#0000FF"><strong>รหัส</strong></td>
<td width="52%" align="center" bgcolor="#0000FF"><strong>หัวข้อ</strong></td>
<td width="12%" align="center" bgcolor="#0000FF"><strong>ตอบโดย</strong></td>
<td width="10%" align="center" bgcolor="#0000FF"><strong>ตอบล่าสุด</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>อ่าน</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>ตอบ</strong></td>
</tr>
<?php do { ?>
<tr>
<td align="center"><?php echo $row_topic['t_id']; ?></td>
<td><a href="#" class="blue"><?php echo $row_topic['topic']; ?></a><br />
<span style="font-size:12px">โดย : <?php echo $row_topic['t_username']; ?> เมื่อ : <?php echo $row_topic['t_date']; ?></span></td>
<td><?php
if($row_topic['r_username']!=""){
echo $row_topic['r_username'];
}else{
echo "ยังไม่มีผู้ตอบ";
}
?></td>
<td align="center"><?php
if($row_topic['r_date']!=""){
echo $row_topic['r_date'];
}else{
echo "-";
}?></td>
<td align="center"><?php echo $row_topic['view']; ?></td>
<td align="center"><?php echo $row_topic['reply']; ?></td>
</tr>
<?php } while ($row_topic = mysql_fetch_assoc($topic)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($topic);
?>
ประวัติการแก้ไข 2015-01-24 19:52:05
Date :
2015-01-24 17:34:42
By :
team000000
ลองเปลี่ยนจาก do while เป็น
Code (PHP)
while()
{
}
do while = แสดงผลก่อนแล้วค่อยเช็ค
while = เช็กก่อนแล้ว แสดงผล
Date :
2015-01-24 20:16:41
By :
yamcrocodile
ตอบความคิดเห็นที่ : 9 เขียนโดย : yamcrocodile เมื่อวันที่ 2015-01-24 20:16:41
รายละเอียดของการตอบ ::
ไม่ได้ครับ เรียงเป็นแบบนี้
Code (PHP)
<?php require_once('Connections/Project.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_Project, $Project);
$query_topic = "SELECT DISTINCT (topic.t_id), topic.topic, topic.t_date, topic.t_username, topic.`view`, topic.reply, reply.r_username, reply.r_date FROM topic LEFT JOIN reply ON reply.t_id = topic.t_id ORDER BY reply.t_id DESC ";
$topic = mysql_query($query_topic, $Project) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<table width="98%" border="0" align="center" cellpadding="5" cellspacing="0" class="table">
<tr class="font_white">
<td width="8%" align="center" bgcolor="#0000FF"><strong>รหัส</strong></td>
<td width="52%" align="center" bgcolor="#0000FF"><strong>หัวข้อ</strong></td>
<td width="12%" align="center" bgcolor="#0000FF"><strong>ตอบโดย</strong></td>
<td width="10%" align="center" bgcolor="#0000FF"><strong>ตอบล่าสุด</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>อ่าน</strong></td>
<td width="9%" align="center" bgcolor="#0000FF"><strong>ตอบ</strong></td>
</tr>
<?php while ($row_topic = mysql_fetch_assoc($topic)){?>
<tr>
<td align="center"><?php echo $row_topic['t_id']; ?></td>
<td><a href="#" class="blue"><?php echo $row_topic['topic']; ?></a><br />
<span style="font-size:12px">โดย : <?php echo $row_topic['t_username']; ?> เมื่อ : <?php echo $row_topic['t_date']; ?></span></td>
<td><?php
if($row_topic['r_username']!=""){
echo $row_topic['r_username'];
}else{
echo "ยังไม่มีผู้ตอบ";
}
?></td>
<td align="center"><?php
if($row_topic['r_date']!=""){
echo $row_topic['r_date'];
}else{
echo "-";
}?></td>
<td align="center"><?php echo $row_topic['view']; ?></td>
<td align="center"><?php echo $row_topic['reply']; ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
<?php
mysql_free_result($topic);
?>
ประวัติการแก้ไข 2015-01-24 20:39:04
Date :
2015-01-24 20:35:13
By :
team000000
Line ID: yamq
ถ้ารีบก็ลายมาหาผมครับ
Date :
2015-01-24 20:39:39
By :
yamcrocodile
Load balance : Server 04