[RESOLVED] calculating related threads

IceRegent

Hello;

I have my own custom user forum that I made, and am working to update and modify the way it works. I have it split up into 6 distinct forums, with each forum having an unlimited number of threads to create, and any time a post is made to a thread, it gets noted as to which thread it belongs to, and which forum as well. The posting part and the display part work just wonderfully. However, I need to learn, and not need to get told to 'just go get a canned script', as I am learning how to program php myself, but I need to find out the best way, using the current display script I wrote, how to display how many posts are in each thread. Here is the current display code for the thread display:

This is the header and setup info:

<?php
session_start();
if ($_SESSION['Valid'] <> true) {
$USID="Visitor";
}
include "db_config.php";
mysql_select_db(iceregen_MStudio);
echo '<body bgcolor="#000000">';
$Forum2Display=$_GET['forum'];
$_SESSION['forum']=$Forum2Display;
$a=1;
echo '<table border="2" style="border: 2 solid #800000; padding: 1" width="100%" bgcolor="#CCCCCC">';
echo '    <tr>';
echo '        <td width="100%"><font face="Arial" color="#000000">Forum is : <font face="Arial" color="#FF0000">' . $_SESSION['forum'] . ' </td>';
echo '    </tr>';
echo '</table><br>'

;

This is the query and display portion:

$query  = "SELECT ThreadID, forum, thread, origthread FROM forums";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
	if ($row['forum'] == $_SESSION['forum'] AND $row['origthread'] == 'yes')
		{
		$a=$a+1;
		$passthread = urlencode($row['thread']);
		echo '<table width="100%" border="2" cellspacing="2" cellpadding="2" bgcolor="#84BBD9" bordercolor="#400000">';
		echo '    <tr>';
		echo '<td width="20%">' . $row['thread'] . ' <a href="/forum_thread.php?passthread=' . $passthread . '" target ="_self">View</a>&nbsp;&nbsp;'. $a . '</td>';
		echo '    </tr>';
		echo '</table>';
		}
}
mysql_close();

I was experimenting with the variable $a to see how I could utilize it to count, but thats when I got frustrated and decided to seek help.

The rest of the code is the options for the user to choose from after the forum thread list is displayed:

if ($_SESSION['Valid'] == true) {
echo '<a href="/thread_new.php" target="_self">Post New Thread</a>&nbsp;&nbsp;&nbsp;';
}
echo '<a href="/forum_select.php" target="_self">Back to Forums</a>&nbsp;&nbsp;&nbsp;';
?>

Now, the need I have, is, how do I best count up how many posts belong to each thread? My forum table is set up like this:

ThreadID --- AutoIncrement <----thread identifier
forum ------- varchar(50) <---forum thread belongs to
thread ------ varchar(200) <---thread title
origthread - char(3) <---Yes or No if it is an original thread
message --- text <---text of thread message
user --------- varchar(30) <---user who posted thread message
date --------- date <---date thread was posted

What I need to do, is somehow include in my current php code, a way to count up how many messages are in each original thread in the currently displayed forum. I would appreciate any help with how I can accomplish this please.

Thank you;
Ice

devinemke

are you storing all of this in a single table? if so then that is really bad data design. you are needlessly storing a lot of redundant information and limiting a lot of the felxibility that a relational database is useful for.

consider this: (PK = primary key, FK = foreign key)

users
user_id (INT) PK
email (VARCHAR)
password (VARCHAR)

forums
forum_id (INT) PK
name (VARCHAR)

threads
thread_id (INT) PK
forum_id (INT) FK from forums
starter_id (INT) FK from users
name (VARCHAR)

posts
post_id (INT) PK
date_time (DATETIME)
thread_id (INT) FK from threads
forum_id (INT) FK from forums
user_id (INT) FK from users
text (VARCHAR)

then to find out how many posts are in each thread you can do:

SELECT thread_id, COUNT(*) FROM posts GROUP BY thread_id

how many threads in each forum?

SELECT forum_id, COUNT(*) FROM threads GROUP BY forum_id

how many post by each user:

SELECT user_id, COUNT(*) FROM posts GROUP BY user_id

how many post by user #123?

SELECT COUNT(*) FROM posts WHERE user_id = 123

how many threads started by user #456

SELECT COUNT(*) FROM threads WHERE starter_id = 456

when was the last post by user #789?

SELECT MAX(date_time) FROM posts WHERE user_id = 789

make sense?

IceRegent

What you have to understand is this: I am not maintaining a simple forum website. I do have keys from other tables that I use, they are just not labled as foreign keys. I have multiple things happenning on the website. However, you might of given me the info I needed to count how many posts are in each thread. That is the only information that I requested to find out about - how to best find out how many posts are in each thread topic.

SELECT thread_id, COUNT(*) FROM posts GROUP BY thread_id

hopefully I can modify this to acheive the results I need. What some people dont seem to understand is, that the syntax that works for one person on thier site, needs to be modified for another person's code on thier site, sometimes. I find that fact interesting. Some code doesnt work without an `, while others work without any symbols at all. It is frustrating at times, as I will try to work with examples that others give, and it doesnt work, it doesnt work, then I find that by changing the syntax to something else, suddenly it works. Strange.

Anyway, I will try the above mentioned usage, and see what works for me. Thank you very much;
Ice

IceRegent

Unfortunately, the information that you provided to me does not help. I need to work with the current code that I have, and not do a complete rewrite of my website and my databases. Now, please, if anyone has any ideas of how I can utilize the select statement to work on my current code and database setup, I would appreciate the help.

Thank you;
Ice

IceRegent

Ok, I am not happy about having to rework the entire section, but begrudgingly, I am realizing that to be able to do as you suggest, devinemke, I am going to have to do a complete rewrite of my forums code and tables. I just have the problem of writing the php and sql code now to fit the new tables, and have to find a way to transfer the exhisiting information over to the new tables. I do not know how to link the foreign keys to the tables etc, but hopefully someone will let me know how to do that.

Thanks;
Ice

devinemke

IceRegent wrote:
I do not know how to link the foreign keys to the tables etc, but hopefully someone will let me know how to do that.

primary key/foreign key relationships are not really a function of the database engine but a function your own coding logic. the basic idea is that there is a specific field in one table that denotes a related field in another table. it's up to you as to how that relationship is created and used.

for example (using my data structure posted above): let's say we are logged in as John Smith (user #123). perhaps we are storing the user's data in a session, so our $_SESSION array might look like this:

Array
(
    [user_id] => 123
    [email] => john@smith.net
    [password] => 123456
    [valid_login] => 1
)

the "valid_login" index is a simple boolean (1 or 0) to indicate that we have successfully logged in. let's now say that John Smith clicks on a forum called "Car and Trucks" (which is forum #8). the link to that forum might look like:

<a href="forum.php?forum_id=8">Car and Trucks</a>

now John Smith wants to start a new thread called "Ford minivans". he fills out a form with the title of his new thread and the contents of the initial post. the form might look like:

<form action="new_thread.php" method="POST">
thread name: <input type="text" name="thread_name"><br>
post text: <textarea name="post_text"></textarea><br>
<input type="hidden" name="forum_id" value="8">
<input type="submit" name="submit" value="create new thread">
</form>

when the form is submitted (to “new_thread.php”) this is what might happen:

session_start();

$sql = "INSERT INTO threads SET 
forum_id = " . $_POST['forum_id'] . ",
starter_id = " . $_SESSION['user_id'] . ",
name = '" . $_POST['thread_name'] . '"';

$result = mysql_query($sql) or exit(mysql_error());
$thread_id = mysql_insert_id();

$sql = "INSERT INTO posts SET 
date_time = NOW(),
thread_id = " . $thread_id . ",
forum_id = " . $_POST['forum_id'] . ",
user_id = " . $_SESSION['user_id'] . ",
text = '" . $_POST['post_text'] . '"';

$result = mysql_query($sql) or exit(mysql_error());

echo 'your thread was successfully created';

as you can see we are creating the new thread first and then using the newly created thread_id as the foreign key in the new post. this is the link between the two tables. we know that this new thread “Ford minvans” is a member of the “Car and Trucks” forum and that it was started by John Smith.

this is obviously very simplified example with no form validation/error checking/ security checks.

IceRegent

I have re-arranged my tables in my database to reflect what you have suggested, and hopefully I can utilize the methods posted to create data to display such as quantity of threads in a forum etc. I reworked my php code to take the information from the new tables, and was able to get the old data transfered and translated over to the new tables. My last problem is getting the post display code to work correctly now, but I am currently working on that.

My next task to go with your more appropriate scheme, is to change the way my php coding for the form uploads are set into the data base. My original way of doing things was very simplified compared to the new way now, however, I find that, true, it was also very limited as to what can be doen with it. I am currently reowrking the coding, and will post back here as to what the results are and if I am having difficulties with it.

I really appreciate the help in pointing me in the right direction. And here, I thought I was finally pretty much doen with the website and could actually begin using it myself now. sigh so much work to do and so little time and understanding - although, I have come a heck of a long way in my learning of php and mysql since I started this project.

Thanks
Ice

IceRegent

Ok, I have revised my post display code. My first set, to select the forum, and pass on the forum selection variable, "$GET['forum'];", works and passes along the forum selection variable which I turn into a session variable called "$SESSION['forum']". I also run a query to setup the ForumID as a number variable for the 'if' comparison statement in the next script segment.

My next script, displays all the threads associated with that forum, and works successfully with my comparison code. I pass the variables thru with this method:

$query  = "SELECT ThreadID, ForumID, UserID, name FROM threads";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
if ($row['ForumID'] == $forum_number)
		{
		$passthread = urlencode($row['name']);
		echo '<table width="100%" border="2" cellspacing="2" cellpadding="2" bgcolor="#84BBD9" bordercolor="#400000">';
		echo '    <tr>';
		echo '<td width="20%">' . $row['name'] . ' <a href="/forum_thread.php?passthread=' . $passthread . '" target ="_self">View</a>&nbsp;&nbsp;'. $a . '</td>';
		echo '    </tr>';
		echo '</table>';
		}
}
mysql_close();

Now, I have found by writing the code peice by peice, that the correct parameters are passed on. I tested by printing out the results before I wrote the rest of the code. I am passing the thread thru to the next script via the url encode function, and 'get' ing the variable on the next script. Now, maybe there is a better way to do this, but I am not sure. Now, here is the complete code for the next script, which, seems to display every single post there is even though my comparison variables are correct. I am not sure why it is displaying every post even though the boolean equation is setup. And I know that I am passing the correct variables to the script, it is just that somehow the output loop is ignoring my boolean equation.

<?php
session_start();
include "db_config.php";
mysql_select_db(iceregen_MStudio);
echo '<body bgcolor="#000000">';
$Thread2Display=$_GET['passthread'];
$Forum2Display=$_SESSION['forum'];
echo '<font face="Arial" color="#FF0000">Forum is : ' . $Forum2Display . ' <br>';
echo '<table border="2" style="border: 2 solid #800000; padding: 1" width="100%" bgcolor="#CCCCCC">';
echo '    <tr>';
echo '        <td width="20%"><font face="Arial" color="#000000">Thread is : <font face="Arial" color="#FF0000"> ' . $Thread2Display . ' </td>';
echo '    </tr>';
echo '</table><br>';

$query1 = "SELECT ThreadID, ForumID, UserID, name FROM threads";
$result1 = mysql_query($query1);
while($row1 = mysql_fetch_array($result1, MYSQL_ASSOC))
{
	if($row1['name'] == $Thread2Display)
	{
	$thread_number=$row1['ThreadID'];
	}
}	

$query  = "SELECT PostID, date_time, ThreadID, ForumID, UserID, text FROM posts";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
	if($row['ForumID'] = $_SESSION['forum_ID'] AND $row['ThreadID'] = $thread_number)
	{
		echo '<table width="100%" border="2" cellspacing="2" cellpadding="2" bgcolor="#84BBD9" bordercolor="#400000">';
		echo '<tr>';
		echo '<td width="50%"><font face="Arial" color="#000000">' . $row['UserID'] . ' </td>';
		echo '<td width="50%"><font face="Arial" color="#000000">' . $row['date_time'] . ' </td>';
		echo '</tr>';
		echo '<tr>';
		echo '<td width="100%"><font face="Arial" color="#000000">' . $row['text'] . ' </td>';
		echo '</tr>';
		echo '</table><br>';
	}
}
mysql_close();
if ($_SESSION['Valid'] == true) {
echo ' <a href="/reply_thread.php?thread=' . $Thread2Display . '" target ="_self"><font face="Arial" color="#FFFF00">  Reply to Thread </a>&nbsp;&nbsp;&nbsp;';
}
echo '<font face="Arial" color="#FF0000"> Back to  <a href="/forum_display.php?forum=' . $Forum2Display . '" target ="_self"><font face="Arial" color="#FFFF00"> ' . $Forum2Display. ' </a>&nbsp;&nbsp;&nbsp;';
?>

The '$query1' and '$result1' set up my actual ThreadID number rather than a string for my comparison. The non-working boolean seems to be:

if($row['ForumID'] = $_SESSION['forum_ID'] AND $row['ThreadID'] = $thread_number)

Can you see where I could improve it, and/or where I am going wrong?

Thanks
Ice

devinemke

IceRegent wrote:
Can you see where I could improve it, and/or where I am going wrong?

yes, i can see syntactically was is wrong (use "==" instead of "=") however this whole logic is not very efficient. why bother needlessly pulling out data that you do not use. why pull all threads and then do a comparison on each one when you can simply pull only the ones you need. the forum_id is being passed as a GET var. then your query should be:

$sql = 'SELECT ThreadID, ForumID, UserID, name FROM threads WHERE ForumID = ' . $_GET['forum'];

this same logic should also apply to any page request where you only need certain forums/threads/posts. the point here is that you want to dynamically contruct your queries, not pull everything out do a bunch of comparisons in PHP.

IceRegent

However, I have tried to use the 'SELECT_FROM_WHERE' statement in the past, and I just got nowhere with it. Something in the syntax just wouldnt work for me. I spent days and days trying to get it to work, to no avail, and thats when I started doing the comparison loops that are so wasteful. I will try and get the selct staement working right. You are correct, it would save alot, but I have just never been able to get it working correctly for me.

I will work on that in a bit.

Thank you very much;
Ice

IceRegent

Ok, I had to work with it for a bit, But I got the code to work. This is how my one section is working now:

$query  = "SELECT PostID, date_time, ThreadID, ForumID, UserID, text FROM posts WHERE ThreadID = $thread_number";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo '<table width="100%" border="2" cellspacing="2" cellpadding="2" bgcolor="#84BBD9" bordercolor="#400000">';
		echo '<tr>';
		echo '<td width="50%"><font face="Arial" color="#000000">' . $row['UserID'] . ' </td>';
		echo '<td width="50%"><font face="Arial" color="#000000">' . $row['date_time'] . ' </td>';
		echo '</tr>';
		echo '<tr>';
		echo '<td width="100%"><font face="Arial" color="#000000">' . $row['text'] . ' </td>';
		echo '</tr>';
		echo '</table><br>';
}

They change the comparison syntax, from being a comparison of '==' to a comparison of '=' inside of the select statement. It is confusing at times, you have to reverse what you do outside of the select statement.

but now that I proved it to work here, then I can replace all of my other sets of code like that with the select_from_where statement.

Thank you devinemke;

Ice