how to write sql for a last comment in forum page

Question

geneh23 0 Junior Poster

12 Years Ago

Hey everyone,

So I'm doing a project just for the heck of it. However, I'm stuck on an issue. I want to query the database to select the last comment in the answers table so I can display it on the main forum page under the specified topic. How do I do this? if it helps I can display the database data for the table field:

CREATE TABLE IF NOT EXISTS `answer` (
  `question_id` int(4) NOT NULL DEFAULT '0',
  `a_id` int(4) NOT NULL DEFAULT '0',
  `a_name` varchar(65) NOT NULL DEFAULT '',
  `a_email` varchar(65) NOT NULL DEFAULT '',
  `a_answer` longtext NOT NULL,
  `a_datetime` varchar(25) NOT NULL DEFAULT '',
  KEY `a_id` (`a_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I know this is probably really simple for a lot of you all but I am seriously stuck and can't seem to figure it out. Can anyone help me in the right direction? Thanks in advance!

-Geneh23

php sql

2 Contributors
11 Replies
205 Views
21 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by LastMitch

LastMitch

12 Years Ago

@geneh23

I want to query the database to select the last comment in the answers table so I can display it on the main forum page under the specified topic.

You know it's getting late. But thank goodness there's Daylight Saving Time. I'm getting an extra hour of sleep.

To answer your question here is a query:

$sql = "SELECT a_answer FROM answer ORDER BY a_answer DESC LIMIT 1";

PrimePackster commented: To Rectify what some retard did to LastMitch +0

LastMitch

12 Years Ago

@geneh23

Yes, you can have as many query you want as long as those query don't connected to each other. Another word if you have 2 same query echoing out the same data and that won't look good. The query that you post is right. I don't see any issue with it.

Edited 12 Years Ago by LastMitch because: gammera

LastMitch

12 Years Ago

@geneh23

what is the best way to display the info from the database for the last comment posted? I'm not sure how to do it with the other information that is included in the while loop..

Put this on line 90:

$sql = "SELECT a_answer FROM answer ORDER BY a_answer DESC LIMIT 1";

also, my reply field on the main forum page, seems to automatically update by 1 even if there aren't any replys..why is this?

There is an issue with your query:

//reply field
$sql6="SELECT reply FROM $tbl_name WHERE id='$id'";
$result6=mysql_query($sql6);
$rows=mysql_fetch_array($result6);
$reply=$rows['reply'];
if(empty($reply)){
$reply=1;
$sql7="INSERT INTO $tbl_name(reply) VALUES('$reply') WHERE id='$id'";
$result7=mysql_query($sql7);
}
// count more replies
$addreply=$reply+1;
$sql8="UPDATE $tbl_name set reply='$addreply' WHERE id='$id'";
$result8=mysql_query($sql8);

Fix the query and everything should be fine

Edited 12 Years Ago by LastMitch because: grammer

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

geneh23 0 Junior Poster · Answer 1 · 2012-11-04T07:03:35+00:00

I have one more question that relates to this topic. Before I go any further..I know it's bad practice to have the connection to the database in the same file but is it possible to have another sql query with another sql query that has a while loop? for example. I have an sql query that does the following:

<?php

$host = "********"; // Host name 
$username = "******"; // Mysql username 
$password = ""; // Mysql password 
$db_name = "forum"; // Database name 
$tbl_name = "question"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";

$result=mysql_query($sql);
?>
<html>
<body>
<h1>My Forum</h1>
<table width="90%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="53%" align="center" bgcolor="#E6E6E6"><strong>Topic</strong></td>
<td width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Replies</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Last Comment</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Date/Time</strong></td>
</tr>

<?php

// Start looping table row
while($rows = mysql_fetch_array($result)){
?>
<tr>
<td bgcolor="#FFFFFF"><a href="view_topic.php?id=<?php echo $rows['id']; ?>"><?php echo $rows['topic']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><?php echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><?php echo $rows['reply']; ?></td>
<td align="center" bgcolor="#FFFFFF">0</td>
<td align="center" bgcolor="#FFFFFF"><?php echo $rows['datetime']; ?></td>
</tr>

<?php
// Exit looping and close connection
}
mysql_close();
?>

geneh23 0 Junior Poster · Answer 2 · 2012-11-05T01:52:03+00:00

what is the best way to display the info from the database for the last comment posted? I'm not sure how to do it with the other information that is included in the while loop..

also, my reply field on the main forum page, seems to automatically update by 1 even if there aren't any replys..why is this?

geneh23 0 Junior Poster · Answer 3 · 2012-11-05T01:52:47+00:00

<?php

$host="*******"; // Host name 
$username="*******"; // Mysql username 
$password=""; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="question"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar 
$id=$_GET['id'];
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>
<html>
<body>
<p><b><center>Topics:</center></b></p>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong><?php echo $rows['topic']; ?></strong></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><?php echo $rows['detail']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>By :</strong> <?php echo $rows['name']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Email : </strong><?php echo $rows['email'];?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Date/time : </strong><?php echo $rows['datetime']; ?></td>
</tr>
</table></td>
</tr>
</table>
<BR>
<p><center>Go back to the <a href="main_forum.php">Forum</a></center><p>
<p><center><b>Comments:</b></center></p>
<?php

$tbl_name2="answer"; // Switch to table "answer"
$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$id'";
$result2=mysql_query($sql2);
while($rows=mysql_fetch_array($result2)){
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>ID</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><?php echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Email</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_email']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?php
}

// select view from table
$sql3="SELECT view FROM $tbl_name WHERE id='$id'";
$result3=mysql_query($sql3);
$rows=mysql_fetch_array($result3);
$view=$rows['view'];

// if have no counter value set counter = 1
if(empty($view)){
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'";
$result4=mysql_query($sql4);
}

// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE id='$id'";
$result5=mysql_query($sql5);

//reply field
$sql6="SELECT reply FROM $tbl_name WHERE id='$id'";
$result6=mysql_query($sql6);
$rows=mysql_fetch_array($result6);
$reply=$rows['reply'];

if(empty($reply)){
    $reply=1;
    $sql7="INSERT INTO $tbl_name(reply) VALUES('$reply') WHERE id='$id'";
    $result7=mysql_query($sql7);
}

// count more replies
$addreply=$reply+1;
$sql8="UPDATE $tbl_name set reply='$addreply' WHERE id='$id'";
$result8=mysql_query($sql8);
mysql_close();
?>

<BR>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="add_answer.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td width="18%"><strong>Name</strong></td>
<td width="3%">:</td>
<td width="79%"><input name="a_name" type="text" id="a_name" size="45"></td>
</tr>
<tr>
<td><strong>Email</strong></td>
<td>:</td>
<td><input name="a_email" type="text" id="a_email" size="45"></td>
</tr>
<tr>
<td valign="top"><strong>Answer</strong></td>
<td valign="top">:</td>
<td><textarea name="a_answer" cols="45" rows="3" id="a_answer"></textarea></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="id" type="hidden" value="<?php echo $id; ?>"></td>
<td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>
</body>
</html>

geneh23 0 Junior Poster · Answer 4 · 2012-11-05T02:40:20+00:00

I'm sorry, I'm confused..do you want me to put the sql query in the code I put in the comments..the one containing the connection to the server that I posted 41 minutes ago? and if I'm correct, it's the third query that needs changing since that's the one that adds the reply. Am I correct?

LastMitch · Answer 5 · 2012-11-05T02:50:22+00:00

@geneh23

I'm sorry, I'm confused..do you want me to put the sql query in the code I put in the comments..the one containing the connection to the server that I posted 41 minutes ago? and if I'm correct, it's the third query that needs changing since that's the one that adds the reply. Am I correct?

i'm not sure what you are asking? You ask me to where to put the query. I told you it it between line 90 and line 91? What is the issue?

my reply field on the main forum page, seems to automatically update by 1 even if there aren't any replys..why is this?

You said that it automatically update by 1 so base on your code it's in the reply since it said reply.

So it's here:

//reply field
$sql6="SELECT reply FROM $tbl_name WHERE id='$id'";
$result6=mysql_query($sql6);
$rows=mysql_fetch_array($result6);
$reply=$rows['reply'];

if(empty($reply)){
$reply=1;
$sql7="INSERT INTO $tbl_name(reply) VALUES('$reply') WHERE id='$id'";
$result7=mysql_query($sql7);
}

// count more replies
$addreply=$reply+1;
$sql8="UPDATE $tbl_name set reply='$addreply' WHERE id='$id'";
$result8=mysql_query($sql8);
mysql_close();

If you think there's nothing wrong with the query then there's nothing wrong. I'm not going to create a db and a table to test this out.

geneh23 0 Junior Poster · Answer 6 · 2012-11-05T02:59:25+00:00

the issue is I don't know where to put it because I had to put both of the tables in separate files and include them onto the main page. I created another table for a "rules" about the forum and I had to take both of those tables out and include them so both of the tables plus the info..so it would show up...so that's why I asked where you wanted me to put the line of code..also I have two sets of code (pages) in this topic..that's why I asked which one..becuase I posted another set of code in the comments..

geneh23 0 Junior Poster · Answer 7 · 2012-11-05T03:00:39+00:00

I'm not asking you to create a db to test out anything..it should all be in the query..but I just didn't know which one to alter since there are three queries with that field..

LastMitch · Answer 8 · 2012-11-05T03:05:51+00:00

@geneh23

the issue is I don't know where to put it because I had to put both of the tables in separate files and include them onto the main page. I created another table for a "rules" about the forum and I had to take both of those tables out and include them so both of the tables plus the info..so it would show up...so that's why I asked where you wanted me to put the line of code..also I have two sets of code (pages) in this topic..that's why I asked which one..becuase I posted another set of code in the comments..

I'll be honest with you. I got no idea what you want to do. This thread was about a simple query but now it's more than just a query. You want me to tell you how to put this code altogether in order to work? You are asking a lot. I hope you realized what is happening now.