Mysql Auto-Name a Table HELP!

meta4m

Hello all!

I am having trouble with a mysql problem.

I'm trying to build a forum like WebX. It's sort of like the Prattle model, but I've got a scheme where I need to auto-create a table for each topic's posts in the database.

I've got a naming scheme in mind...each topic will get a table for their posts with a name like "zposts1". The problem I'm having is coming up with a way to create a table with this scheme and "auto-increment" the name so that the next table created will be named "zposts2".

I'm starting them with "z" so that these posts will be at the bottom of my list of tables in myphpadmin.

I'm sort of a newbie still. Any help from the gurus in this forum would be greatly appreciated.

Thanks in Advance.

Rich

amcgrath

This doesn't really sound like a MySQL problem, rather a problem trying to figure out a way to uniquely name tables. If that's the case, y not create a sequence that you can select from? ie:

create sequence forum_id_seq;

before creating the table, "select nextval('forum_id_seq')" (works in Postgres) and append it to the table name you create?

Anon

There is probably a more elegant way of doing this, but here is a possible solution:

Assuming you are only creating the z tables in the database and all other tables are going to stay the same, you could do the following:

$misctables = 5;
$sql = "show tables";
$result = mysql_query($result, $db);
$num_tables = mysql_num_rows($result);
$val = $num_tables - $misctables;

Basically $misctables will be a number of tables that will not be used for your calculation ... You will run a query against the MySQL database to return a list of tables, afterwards, you will count the number of tables, subtract out the unused tables and end up with $val ... Then you can do something like:

$newtable = "zposts".$val;

btw -- What is the reasoning behind creating a ton of similar tables? Why not have all posts in one table?

[deleted]

"I need to auto-create a table for each topic's posts in the database"

No you don't. This is most definately NOT they way to do this.

Instead, make one table per FORUM, and store ALL the posts for that forum in the same table.

Think about it. What if you have 45000 posts? MySQL doesnt' allow more than 32000 tables per database. And how on earth are you going to print a list of 'latest' posts? by querying all 32000 tables? that's either a join stamement of 45 pages, or 32000 queries. Either way it's not good.

amcgrath

Vince, I came this close (holds fingers together) to saying the same thing, but thought I'd give the guy the benefit of the doubt. But,a fter reading your response, I think oyur right, he intends to put each response in a table.... now that's funny. Meta4m, please read and re-read Vince's post... please.

meta4m

Guys, thanks for the responses.

I'm not planning on putting each response its own table, though I could see why you could think that.

I need a way to show only the posts you haven't read, like a hiwater mark.

I used to have one post table that held all the posts, no matter what topic you they "belonged" to. This worked, but, since all of the posts are thrown into a single database, I couldn't figure out a way to get at the previous posts in chunks of 10. I could get them all, but I couldn't figure out how to get the 10 before the most recent one you have read, and so on, without each topic's posts being in the same table. I played with timestamps, post ID, and so on, but since the postIDs are non-sequential, I couldn't figure out how to get the previous 10 using the IDs.

This is how Steve Y. (I think) does it in Prattle, which is sort of what I'm trying to do. Any help is appreciated!

[deleted]

Getting the 'most recent 10 before date X'
is quite simple.

SELECT *
FROM table
WHERE post_date<last_read_date
ORDER BY post_date DESC
LIMIT 10

meta4m

Vince,

I tried that. It worked for the first set of previous 10, but I couldn't figure out a way to get the next chunk of 10 before that, and so on.

It was driving me crazy, so I tried this new approach.

I am totally willing to go back and try again, but I couldn't figure out a way to make it work. I had a "tracker" table where I was putting the userID, most recent post, and topicID. When the user clicked on a "previous" link, I would grab that info, and use it to display the posts before the most recent post.

It should have worked, but I was having trouble with it because...well, I'm not sure why.

I can post code I was using if you're willing to read it. It's kind of newbie code, so bear with me. : )

Thanks!

[deleted]

Getting the next ten before that is simply doing the same query, but with
LIMIT 10,10
and the next ten:
LIMIT 20,10
and the next
LIMIT 30,10

Post your code and we can all have a good laugh.. ehm I mean a good look at it :-)

amcgrath

Wouldn't that give him a group of 10, then a group 20, then 30, instead of 1st 10, 2nd 10, 3rd 10 etc?

[deleted]

Not according to my manual:
[LIMIT [offset,] rows]

Anon

From the MySQL Documentation:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):