Returning Multiple rows of Same Record

Duvalfan23 · July 7, 2008

I am building a small php forum for a family member and I am trying to retrieve topics for the specified forum by the URL parameter. Whenever I have more than 1 topic in the forum, the number of topics in that forum is correct from how many posts Ive put into that forum, but all of the rows are of the same topic. So if the first topic I insert is "Post1", afterwards, I make a new post called "Post2" there will be 2 rows in the forum, but both are called "Post2" instaed of 1 of each record. This continues to happen for each consecutive post. Here is the SQL I am using with MySQL 4.1.21-log:

$sql = "SELECT DISTINCT Topics.TopicID, LENGTH(Topics.Topic) AS TopicLength,SUBSTRING(Topics.Topic,1,35) AS Short,Topics.Topic,Topics.UserID,Topics.Views,Topics.Replies,Topics.PostDate,Forums.ForumID,Forums.ForumName,Users.UserID,Users.User_name FROM Topics,Forums,Users WHERE Topics.ForumID = " . $forum . " AND Topics.UserID = Users.UserID AND Forums.ForumID = " . $forum . " GROUP BY(Topics.TopicID)";

The PHP I'm using to display the data is:

<h2><?php $row = mysql_fetch_array($result);

echo $row["ForumName"]; ?></h2>

<a href="newpost.php?forum=<?php echo $_GET["forum"]; ?>" style="text-align:center; font-size:14px; margin:auto;">New Topic</a>

</span>

<tr>

<th width="60%">Topic</th>

<th width="20%">Posted By</th>

<th width="10%">Views</th>

<th width="10%">Replies</th>

</tr>

<?php

for($i=0;$i<$num_result;$i++) {

echo "<tr>";

echo "<td class='left'><a href='topics.php?topic=" . $row["TopicID"] . "&forum=" . $row["ForumID"] . "'>";

if($row["TopicLength"] > 35) {echo $row["Short"] . "...</a>";} else {echo $row["Topic"] . "</a>";}

echo "<td class='right'>" . $row["User_name"] . "</td>";

echo "<td class='right'>" . $row["Views"] . "</td>";

echo "<td class='right'>" . $row["Replies"] . "</td>";

echo "</tr>";

}

?>

</table>

I am using the LENGTH and SUBSTRING to shorten Topic Titles if they are over 35 characters long and add ... at the end. But this still happened before I put those in. So what is the deal with this query to where it doesn't only get a single instance of each record? Thanks!

fenway · July 7, 2008

Sounds like you're using a group by and retrieving non- group-by columns...

Duvalfan23 · July 7, 2008

What does that mean for me? This is the table data from the table "Topics":

25 1 Post1 24 2008-07-07 12:34:06 1 0

26 1 Post2 24 2008-07-07 12:34:35 0 0

and this is what is displayed on the page:

[link]Post1[link] [username] 3 0

Notice how it just displays the same info for the # of rows in the table, but does not display each record as its suppose to. If I were to have 10 records in the database table, it would display "Post1" 10 times.

fenway · July 7, 2008

In fact, I'm not sure why you have the group by there at all... and it looks like you're missing a join condition.

Duvalfan23 · July 7, 2008

Well I had to go learn SQL-92 syntax real quick to learn the INNER Joins, but this is what I have now, and its doing the same thing:

$sql = "SELECT DISTINCT(Topics.TopicID) AS TopicID, LENGTH(Topics.Topic) AS TopicLength,SUBSTRING(Topics.Topic,1,35) AS Short,Topics.Topic,Topics.UserID,Topics.Views,Topics.Replies,Topics.PostDate,Forums.ForumID,Forums.ForumName,Users.UserID,Users.User_name FROM Topics INNER JOIN Forums ON Forums.ForumID = Topics.ForumID AND Forums.ForumID = " . $forum . " INNER JOIN Users ON Topics.UserID = Users.UserID WHERE Topics.ForumID = " . $forum;

fenway · July 9, 2008

DISTINCT isn't a function... and since you're getting back IDs, it won't do anything. What do you want grouped?

Duvalfan23 · July 9, 2008

Well I just added some php and now it works fine. It must have been something the way I had the PHP code. It took me a day and a half to figure it out. But I do have one other question regarding the site. Whenever I am trying to send a reply to a topic or update forum info from the admin page, all it does is redirect me back to the start page without doing anything. Also, when I try to delete users, topics, or posts, I can delete all of them but the last one, and then it does the same as above. I even put a header(location) and a die function at the top of each of the action pages, but it doesnt even recognize the page I guess, because it still just goes straight to the main page. The code I have is the following:

Reply form:

<?php 

$sql1 = "SELECT Topics.TopicID,Topics.Topic,Forums.ForumID FROM Topics,Forums WHERE Topics.TopicID = " . $topic . " AND Forums.ForumID = " . $forum;



if(isset($_SESSION['Username'])) {
$error = "Logged In As: " . $_SESSION['Username'];
}
else if (isset($_SESSION['invalid'])) {
$error = "Invalid Login Please Try Again!";
}
else if (isset($_SESSION['notloggedin'])){
$error = "You Must Be Logged In!";
}
else {
$error = "";
}

?>



<?php

	$res1 = mysql_query($sql1,$con) or die("Could Not Retreive Data Requested!");
	$num_res1 = mysql_num_rows($res1);

	 for($i=0;$i<$num_res1;$i++) {
		 $row1 = mysql_fetch_array($res1);	

		echo    "<form action='actions/reply.php' method='post'><div class='topic' style='height:0px;'><h2 style='font-size:18px;'>";
		echo    "Reply To: <a href='topics.php?forum=" . $_GET['forum'] . "&topic=" . $_GET['topic'] . "' style='font-size:16px;color:#FFF;text-decoration:underline;'>" . $row1["Topic"] . "</h2>";
            echo	" <br /><table align='center' style='margin-top:10px;'><tr><td><textarea name='reply' rows='15' cols='40'></textarea></td></tr>";
		echo    "<br />";
		echo    "</table>";
			echo    "<table align='center'><tr><td><input type='reset' name='reset' value='Reset' /></td>";
		echo    "<td><input type='submit' name='reply' value='Post Reply' /></table>";
		echo    "<div style='clear:both;'> </div><input type='hidden' name='topicid' value='" . $_GET['topic'] . "' />";
		echo    "<input type='hidden' name='forumid' value='" . $_GET['forum'] . "' /></div></form>";
		}

		?>

Reply Action:

<?php

header("location: nopost.php");
session_start();
$serv = '[server]';
$user = '[username]';
$pass = '[username]';
$con = mysql_connect($serv,$user,$pass);
$db = "[database]";

mysql_select_db($db,$con) or die("Cannot connect to Database!");


$topic = $_POST['topicid'];
$forum = $_POST['forumid'];
$message = $_POST['reply'];
if (isset($_SESSION['UserID'])){
$user = $_SESSION['UserID'];
}
$date = date('M d, Y');
$URL = "topics.php?topic=" . $topic . "&forum=" . $forum;



$sql = "INSERT INTO Posts (TopicID,UserID,Message,PostDate,DateText) VALUES ('" . $topic . "','" . $user . "','" . $message . "',NOW(),'" . $date . "')";

$insertData = mysql_query($sql,$con) or die("Cannot Add Reply To Database!");

$update = "UPDATE Users SET Posts = Posts + 1 WHERE UserID = " . $user;
$act_update = mysql_query($update,$con) or die("Cannot Update User Posts!");


$addreply = "UPDATE Topics SET Replies = Replies + 1 WHERE TopicID = " . $topic;
$exereply = mysql_query($addreply,$con) or die("Cannot Add Reply to Topic!");

header("Location:" . $URL);

mysql_close($con);

?>

Delete User Form:

<?php
$serv = '[server]';
$user = '[username]';
$pass = '[****]';
$con = mysql_connect($serv,$user,$pass);
$db = "[database]";

mysql_select_db($db,$con) or die("Cannot connect to Database!");


$getUsers = "SELECT * FROM Users";

$action_users = mysql_query($getUsers,$con) or die("cannot Retrieve Users From Database!");
$num_users = mysql_num_rows($action_users);

?>


<?php

echo '
<table align="center" id="adminTable" cellspacing="0" width="100%" cellpadding="0">
		<tr><th width="40%">User</th><th width="30%">Delete</th><th width="30%">Lock</th></tr>

        ';
        	for($i=0;$i<$num_users;$i++) {
		 $users = mysql_fetch_array($action_users);	

		echo 
		'

    		<form action="actions/deleteuser.php" method="post">
            <tr>
            	<td>' . $users["User_name"] . '</td>
			<td><input type="submit" name="deleteuser" value="Delete User" onclick="return confirm(\'Are You Sure You Want To Delete This User?\');" /></td>
			<input type="hidden" name="userID" value="' . $users["UserID"] . '" /></form>
			<td style="padding-top:10px;">
			<form action="actions/lockuser.php" method="post">';
			if ($users["Locked"] == '1'){
			echo '<input type="submit" name="lockuser" value="Unlock User Account" onclick="return confirm(\'Are You Sure You Want To Unlock This User?\');" />';
			echo '<input type="hidden" name="unlock" value="1">';
			}
			else {
			echo '
			<input type="submit" name="lockuser" value="Lock User Account" onclick="return confirm(\'Are You Sure You Want To Lock This User?\');" />'; }
			echo '
			<input type="hidden" name="userIDD" value="' . $users["UserID"] . '" /></form></td>
            </tr>

		'; } ?>

Delete User Action:

<?php 

session_start();
$serv = '[server]';
$user = '[username]';
$pass = '[****]';
$con = mysql_connect($serv,$user,$pass);
$db = "[database]";


mysql_select_db($db,$con) or die("Cannot connect to Database!");

$user = $_POST['userID'];
$URL = "../admin.php?editusers";

$deleteT = "DELETE FROM Users WHERE UserID = " . $user;
$act_delete = mysql_query($deleteT) or die("Cannot Delete User!");


header("location:" . $URL);

mysql_close($con);

?>

Edit Forums Form: (which when posted, doesnt even read the action page)



<?php 
$serv = '[server]';
$user = '[username]';
$pass = '[****]';
$con = mysql_connect($serv,$user,$pass);
$db = "[database]";

mysql_select_db($db,$con) or die("Cannot connect to Database!");


$sql = "SELECT * FROM Forums";

$result = mysql_query($sql,$con) or die("Cannot Retrieve Forums From Database");
$num_result = mysql_num_rows($result);


if(isset($_SESSION['Roles']) && $_SESSION['Roles'] != '1999'){
$_SESSION['notloggedin'] = "true";
header("location: forums.php");
}
else if (!isset($_SESSION['UserID'])){
$_SESSION['notloggedin'] = "true";
header("location: forums.php");
}
else {}


?>






        <?php
        	for($i=0;$i<$num_result;$i++) {
		 $row = mysql_fetch_array($result);	

		echo 
		'<form action="actions/act_editforums.php" method="post" name="editforum' . $row["ForumID"] . '">

    	<table align="center" cellspacing="0" width="100%" cellpadding="5">
            <tr>
            	<td><strong>Forum Name:</strong></td><td><input type="text" name="forumname" value="' . $row["ForumName"] . '" /></td>
            </tr>
		<tr>
			<td><strong>Forum Description:</strong></td><td><input type="text" name="forumdesc" value="' . $row["ForumDesc"] . '" size="50" /></td>
			<input type="hidden" name="forumID" value="' . $row["ForumID"] . '">
		</tr>
		<td align="center"><input type="submit" name="submitchanges" value="Update Forum ' . $row["ForumID"] . '" /></td>
		<!---<td><form action="actions/act_deleteforum.php" method="post"><input type="submit" name="deleteforum" value="Delete Forum" onclick="return confirm(\'Are You Sure You Want To Delete This Forum?\')" /><input type="hidden" name="forumID" value="' . $row["ForumID"] . '"></td>---></tr>
		<tr><td> </td></tr>
		</table>
        </form>
			';

		}

		?>

Update Forums Action:

<?php
$serv = '[server]';

$user = '[username]';

$pass = '[****]';

$con = mysql_connect($serv,$user,$pass);

$db = "[database]";

$forum = $_POST['forumID'];

$forumname = $_POST['forumname'];

$forumdesc = $_POST['forumdesc'];

mysql_select_db($db,$con) or die("Cannot connect to Database!");

$sql = "UPDATE Forums SET ForumName = " . $forumname . " , ForumDesc = " . $forumdesc . " WHERE ForumID = " . $forum;

$exe = mysql_query($sql,$con) or die("Cannot Update Forum Info!")

header("location: admin.php");

mysql_close($con)

?>

Now thats all Im gonna post because Im sure that they are all experiencing the same problem somewhere. But if I could get some help on this, that would be great. Im pretty new to php so this is all kinda out there for me. Thanks!!

fenway · July 9, 2008

Please use code tags next time -- is this solved?

Duvalfan23 · July 9, 2008

The first part is. But Unless you want me to post a new topic, the last post i made that you modified isnt solved.

fenway · July 9, 2008

OK, then make it easier to digest for the rest of us.

Take a single script, find the sql query, echo it to the browser, check for error messages, and drop the redirect for testing.

Sign In

Returning Multiple rows of Same Record

Recommended Posts

Duvalfan23

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Duvalfan23

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Duvalfan23

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Duvalfan23

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Duvalfan23

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information