Confusion with MYSQL

[untitled]

Howcome when I go mysql_query i end up getting an output of "Resource id ##", i know mysql_free_result will free this up but it seems to work at random leaving my output at at the incorrect value . But I shouldn't be having this problem in the first place since the php manual says "memory will automatically be freed at the end of the script's execution". I'm really confused.

To be more specific, mysql_free_result isn't working when it's freeing up a unix timestamp stored in the database. Are their any conditions when mysql_free_result will not work?

[liljim]

You shouldn't really need to use mysql_free_result or mysql_close, as, like you say, resources are freed up on the termination of the script.

What query are you trying to perform? And how are you bringing the result set back? Can you paste the code that you're trying to use? - That would give us a few more clues as to what you are doing wrong.

[untitled]

At 8/11/01 07:03 AM, liljim wrote: You shouldn't really need to use mysql_free_result or mysql_close, as, like you say, resources are freed up on the termination of the script.

What query are you trying to perform? And how are you bringing the result set back? Can you paste the code that you're trying to use? - That would give us a few more clues as to what you are doing wrong.

Here's my code, without the mysql_free_result for $topics and $posts I just get "Resource id ##". For $lastpost all I need is one value but when I do mysql_free_result on the query I get a value of 1 back when the value is a unix timestamp, so instead I do mysql_fetch_array which gets the proper value. My script works the way it is, but it's bugging me that I got to use mysql_free_result, or fetch an entire row when all i need is one value from it.

$result=mysql_query("SELECT * FROM forum ORDER BY frm_order");

while($row=mysql_fetch_array($result)){

$topics=mysql_free_result(mysql_query("SELECT count(*) AS total FROM topic WHERE (tpc_frmid='".$row["frm_id"]."')"));

$posts=mysql_free_result(mysql_query("SELECT count(*) AS total FROM post WHERE (pst_frmid='".$row["frm_id"]."')"));

$lastpost=mysql_fetch_array(mysql_query("SELECT * FROM post WHERE (pst_frmid='".$row["frm_id"]."') ORDER BY pst_time DESC LIMIT 1"));

}

[liljim]

At 8/11/01 06:35 PM, untitled wrote: stuff.

Umm, this looks very strange to me.. What, exactly, are you trying to get back from your tables?

Here's a simple example as to something I might use:

$get = @mysql_query("SELECT one_column FROM a_table", $connection);

if (!$get) {
Some_sort_of_error_function();
} else {
while($row = mysql_fetch_array($get)) {
$one_column = $row['one_column'];
}
}

If I wanted to count the number of entries to something, I'd use something like:

$count = @mysql_query("SELECT count(whatever) FROM some_table", $connection);

if (!$count) {
do_some_error();
} else {
$do_count = mysql_result($count, 0, "count(whatever)");
}

I am curious as to why you would use all that erroneous stuff....... And I am not even going to go into table joins...

[untitled]

At 8/11/01 08:25 PM, liljim wrote:
I am curious as to why you would use all that erroneous stuff....... And I am not even going to go into table joins...

I'm trying to make a forum. I got 3 tables, one for the forums, one for the topics, and one for the posts. This script just needs to display the list of forums in the database and im getting the totatl posts and total topics from the other tables so i can display them on the forum listing. I don't know much about table joins but should I be doing that?

Message
Error - you have attempted to post illegal text. Please go back and try again.

[liljim]

Right, I gotya. Ok, well here's how I would do things (and this is by no means the right (or only) way), but it involves less work on your databases' part.

Here goes...

The main script that is going to cause you problems is the one that handles the form submissions, as you will have to get it to do some work with this method. You will also have to give a bi of consideration to the admin script.

I'd recommend that you added another two tables at least - one for members (who are allowed to post) and one for tracking the number of posts the user has made per hour (and thus preventing flooding of your board). So, the tables, and table structures might look something like this:

bbs_forums
ForumId (int, 11)
ForumName (varchar, 100)
ForumDescription (varchar, 255)
ForumTopics (int, 11)
ForumPosts (int, 11)
ForumLastPost (datetime)
ForumLastPoster (int, 11)

bbs_topics
TopicId (int, 11)
ForumId (int, 11 - foreign key)
TopicStarter (int, 11 - foreign key)
TopicStart (datetime)
TopicLastPost (datetime)
TopicLastPoster
Topic (varchar, 30)
TopicPosts (int, 11)

bbs_posts
PostId (int, 11)
TopicId (int, 11 - foreign key)
PostTime (datetime)
PosterId (int, 11 - foreign key)
Post

bbs_tracking
PosterId (int, 11)
ForumId (int, 11 - foreign key)
TopicId (int, 11 - foreign key)
Timestamp (varchar, 20)

members
MemberId (int, 11)
Username (varchar, 20)
Password (varchar, 20)
// Other stuff

Ok, some explanations... First, the forums table. Most of these fields should be self explanitory, except for perhaps the ForumLastPoster - this is the MemberId of whoever last posted to the bbs. So, your forum listing may use something like this:

$listing = @mysql_query("SELECT bbs_forums.ForumId, bbs_forums.ForumName, bbs_forums.ForumDescription, bbs_forums.ForumTopics, bbs_forums.ForumPosts, bbs_forums.ForumLastPost, members.Username FROM bbs_forums, members WHERE members.MemberId = ForumLastPoster", $connection);

if (!$listing) {
// do error
} else {

// Open a table up, with the appropriate headings

while($row = mysql_fetch_array($listing)) {
$ForumId = $row['ForumId'];
$ForumName = $row['ForumName'];
$ForumDescription = $row['ForumDescription'];
$ForumTopics = $row['ForumTopics'];
$ForumPosts = $row['ForumPosts'];
$ForumLastPost = $row['ForumLastPost'];
$ForumLastPoster = $row['Username'];

// Put each of the values into the table cells, close off the table row when done
// to get the href for the page, do forum.php?id=$ForumId etc

} // Close the while loop

} // Close the condition

Now, you are probably wondering where the number of posts in the forum listing comes from, and where all of the other numbers come from... This is where the form that processes the submission comes in. It's mildly complicated in that you have to have it something like this:

1/. Select the MemberId where the username and password match in the members table. If nothing matches issue an error else get the MemberId using the variable, $mid.

2/. if no $pid or $rid, issue an error

3/. if $rid is given, the message is a response to a thread that already exists. Check that the thread exists from the bbs_topics table, select the forum id from the table, and use it in the follow up with the variable $fid. If $pid is given, then check that the forum exists from the forum table

4/. Do a count from the bbs_tracking table: count from the table where

$timestamp = time() - 3600;
// for $pid "timestamp > $timestamp AND ForumId = '$pid'"
// for $rid, you will have selected the topic id from the bbs posts table and got the value
// $tid, so use "timestamp > $timestamp AND TopicId = '$tid'"

5/. if the post is a new post, insert the details into the topic table:

"INSERT INTO bbs_topics (TopicId, ForumId, TopicStarter, TopicStart, TopicLastPost, TopicLastPoster, Topic, TopicPosts) VALUES ('', '$pid', '$mid', NOW(), NOW(), '$mid', '$topic', '1')";

get the topic id to put into the bbs posts table using mysql_insert_id():
$tid = mysql_insert_id();

Insert the post into the bbs_posts table
"INSERT INTO bbs_posts (PostId, TopicId, PostTime, PosterId, Post) VALUES ('', '$tid', NOW(), '$mid', '$post')";

Insert the forum id and poster id into the tracking table. Remember, that this is a new post to a forum...
"INSERT INTO bbs_tracking (PosterId, ForumId, Timestamp) VALUES ('$mid', '$pid', '$timestamp')";

Update the forums table with details of the new post
"UPDATE bbs_forums SET ForumTopics = ForumTopics + 1, ForumLastPost = NOW(), ForumLastPoster = '$mid' WHERE ForumId = '$pid'";

If the post is a reply, you will have already gotten the topic id when you checked that the reply was a valid one. You have to change some slightly different values (like update the number of posts to the topic).

First, insert the post into the posts table:
"INSERT INTO bbs_posts (PostId, TopicId, PostTime, PosterId, Post) VALUES ('', '$tid', NOW(), '$mid', '$post')";

Then you need to insert the topic thread details into the tracking table, as opposed to the forum details, as this is a reply:
"INSERT INTO bbs_tracking (PosterId, TopicId, Timestamp) VALUES ('$mid', '$tid', '$timestamp')";

This time, instead of updating the bbs_forums table with the number of topics, you need to update with the number of posts (note that we are using $fid, which we got earlier):
"UPDATE bbs_forums SET ForumPosts = ForumPosts + 1, ForumLastPost = NOW(), ForumLastPoster = '$mid' WHERE ForumId = '$fid'";

Update the topics table with the number of posts to it:
"UPDATE bbs_topics SET TopicPosts = TopicPosts + 1";

DONE!

Of course, for the admin section you will need to check some stuff.... if you want to delete a topic, and that topic has, for example 33 posts to it, you need to do something like this:

$get_forum_id = @mysql_query("SELECT ForumId FROM bbs_topics WHERE TopicId = '$id'");
$row = mysql_fetch_array($get_forum_id);
$fid = $row['ForumId'];

$count_threads = @mysql_query("SELECT count(TopicId) FROM bbs_posts WHERE TopicId = '$id'");
$num_threads = mysql_result($count_threads, 0, "count(TopicId)");

$update_forum = @mysql_query("UPDATE bbs_forums SET ForumPosts = ForumPosts - $num_threads");

_________________________________________________________

[untitled]

At 8/12/01 11:06 AM, liljim wrote:

I have mine set up basically the same except i don't have topics, posts, and lastpost in the forums or posts table, I should probably do it that way so things will go faster. Thanks for the help.

[RichL]

Here goes...

Blimey jim! Never gave me that much help..;).

[liljim]

At 8/13/01 04:27 PM, RichL wrote: Blimey jim! Never gave me that much help..;).

Not at all... You gotta get out of this mentallity where "more text is more help". It's the concepts that matter, and they can be explained in a few lines of what I wrote (though it was Sunday afternoon, and I was as bored as hell ;)). Besides, looking back on it, I made quite a few errors.

[untitled]

At 8/13/01 05:57 PM, liljim wrote:

At 8/13/01 04:27 PM, RichL wrote: Blimey jim! Never gave me that much help..;).

Not at all... You gotta get out of this mentallity where "more text is more help". It's the concepts that matter, and they can be explained in a few lines of what I wrote (though it was Sunday afternoon, and I was as bored as hell ;)). Besides, looking back on it, I made quite a few errors.

RichL would probably copy all you wrote in a txt file and rename it a php file. Then bug you when it doesn't work.

[Pecos]

At 8/15/01 12:47 AM, untitled wrote: RichL would probably copy all you wrote in a txt file and rename it a php file. Then bug you when it doesn't work.

Right on! That's why I quit helping him.