only showing first thread

gwbouge

trying to make a forum. the forum is divided into different sections, each section contains different threads, and each thread contains the original post and all it's replies, yadda yadda ... y'all know the drill.

I've got each section containing its own database, with each thread as a table.

when trying to view the section, it's only showing up with the first thread (i.e. the first table in the database).

$username = "username";
$password = "password";
$database = $_GET['which'];     // supplied when you click on the section on the main forum page

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
$result=mysql_list_tables($database);     // Get list of threads

while ($topicid=mysql_fetch_row($result)) {
	echo "",$topicid[0],"<br>";     // test.  Show me the thread id.
	$query = "SELECT * FROM $topicid[0] WHERE id='0'";     // Select the opening post to view
	$topicresult=mysql_query($query);
	$row=mysql_fetch_row($topicresult);
	echo "",$row[0],"<br>",$row[1],"<br>",$row[2],"<br>",$row[3],"<br>",$row[4],"<br>";     // test.  Show me the contents of the post.
	$topic = $row[1];
	$postwhen = explode(' ',$row[4]);
	$postwhendate = $postwhen[0];
	$postwhentime = $postwhen[1];
	$postbywho = $row[3];

echo "<tr>";
echo "<td width=450 height=40 valign=center align=left>";
if ($d == "TRUE") {     // If cookie is set, compare timestamps to generate new post feature.
	$threadquery = "SELECT MAX(id) FROM $topicid[0]";
	$threadresult=mysql_query($threadquery);
	$whichid=mysql_fetch_row($threadresult);
	$idquery = "SELECT * FROM $topicid[0] WHERE id='$whichid[0]'";
	$idresult=mysql_query($idquery);
	$row=mysql_fetch_row($idresult);
	$remove = array("-", ":", " ");
	$lastpostold = $row[4];
	$lastpost = str_replace($remove, "", $lastpostold);

	$name = $_COOKIE['srs_membername'];
	$forumdb = "forum";
	@mysql_select_db($forumdb) or die("Unable to select database");
	$whoquery = "SELECT * FROM members WHERE name='$name'";
	$whoresult=mysql_query($whoquery);
	$whorow=mysql_fetch_row($whoresult);
	$whotime = $whorow[4];
	$whotimecomp = str_replace($remove, "", $whotime);

	if ($whotimecomp < $lastpost) {
		echo "<font face=Tahoma size=-2 color=\"yellow\"><b>*NEW POST*</b><br></font>";
	}
}
echo "<a href=\"showpost.php?db=",$database,"&post=",$topicid[0],"\" class=\"threads\">",$topic,"</a>";
echo "</td>";
echo "<td width=160 height=40 valign=center align=right>";
echo "<font face=Verdana size=-2 color=\"#ffffff\">",$postwhendate,", ",$postwhentime,"<br>",$postbywho,"</font>";
echo "</td>";
echo "</tr>";
}

mysql_close();

when I echo $topicid, it shows up with the correct values for each table, but for some reason when I query and fetch_row, it will only return the values of the first thread. the second and third come back with nothing.

any ideas why?

P.S. I've granted all priviledges this time =o)

planetsim

Thats really poor Database Design if each thread gets its own table. Perhaps change the design.

Secondly why not see how many rows should be returned by use [man]mysql_num_rows[/man]

gwbouge

bah, I found it. There was only a problem when a user was logged in ... so after it selected the db with the member information, I never had it re-select the forum database.

I'm using mysql_num_rows for a few different things, but I didn't particularly care for it in this application.

But I was wondering ... why is it a really poor database design? How SHOULD I set it up?

This is my first time trying to build a forum (obviously) ... as well as my first project with php and mysql. I've used perl a time or two, but the bulk of that was using perl with tk, throwing together a couple small windows based programs. So I'm finding out the better and worse ways to do things, and the reasons to do (or not to do) them whatever way on the fly here. Any advice is greatly appreciated.

planetsim

Well that will depend on what you want however you shouldnt be creating different tables just for a thread. They should exist in one thread and use a foreign key from the threads table if you want relations to the threads table.

A quick example

Threads
threadid | title | firstpostid | lastpostid

threadid is a primary key however it will also be the foreign key to create a relationship, since threads usually have posts I show that. Also you can see we use firstpostid lastpostid these are also foreign keys on what will be postid in the posts table.

Posts
postid | title | message | timeposted

Now in threads firstpostid is just the postid from posts which is the first post that the thread has so they are related together. lastpostid is there to be used mainly to show when the last post for that particular thread happened.

Now like I said this is a quick example and it doesnt show SQL on how to relate the tables to one another (Look into JOIN), you should really read up on some Database Design Concepts and specifically in Database Normalization. There are tutorials, and articles on the matter most dont go in depth like a book will however they can be used as a guide to help you out.

gwbouge

hmm ... didn't think of doing it like that. Guess my first instinct was to keep everything organized in it's own little space. I'll certainly play around with that, already plenty of ideas on how to minipulate(sp?) the columns and information ...

but WHY is that a better way? memory to save a seperate table? cpu usage on the server? is it really better to search through hundreds, thousands of posts in the same table to find the ones regarding a certain thread than to just list the 1 or 10 rows in a single table?

no offense, I'm just ignorant in the matter, trying to learn as much as I can from someone who knows my intentions instead of a half-assed, generalized, tutorial. but no worries, it's just the why's that ya never get from the tutorials. I won't ask you how, lol.

TheDefender

Originally posted by gwbouge

but WHY is that a better way? memory to save a seperate table? cpu usage on the server? is it really better to search through hundreds, thousands of posts in the same table to find the ones regarding a certain thread than to just list the 1 or 10 rows in a single table?

If a table is properly normalized, then it can be properly indexed. If it is properly indexed, then tens and even hundreds of thousands of entries can very quickly be searched to drill down to even one entry. Stop and think big picture for a second... Can you imagine how much slower it would be to search through 10 DBs, and 500 tables in each for one single thread? Not to mention the load put onto a server eveytime a table is created for a new thread? If you had the kind of traffic our little forum home had, it would bring it pretty much to a grinding halt.