Page 1 of 1

Insert as last!

Posted: Sat Nov 30, 2002 4:35 pm
by bjugis
I'm making a forum and it have a mayor bug. Most of the times the posts is registered on the bottom of the table but sometimes it just register befor other rows. and when I list the postes it's all messed up. Someone know wat's the problem could be ?

Posted: Sat Nov 30, 2002 4:40 pm
by mydimension
it may have something to do with the way you have your table structure set up. then again it could be you insert query is quirky. and it could also be that you select query is quirky as well. could you shows us the table structure and you insert and select statements?

Posted: Sat Nov 30, 2002 4:54 pm
by bjugis
It's not the select sentence because when I use mysql front it shows the newest post is placed wrong.

+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| subject | varchar(30) | YES | | NULL | |
| reply | blob | YES | | NULL | |
| date | varchar(19) | YES | | NULL | |
| id | varchar(12) | YES | | NULL | |
| topic | varchar(30) | YES | | NULL | |
| topicid | varchar(12) | YES | | NULL | |
| topicdate | varchar(12) | YES | | NULL | |
| user | varchar(20) | YES | | NULL | |
| last_post | varchar(20) | YES | | NULL | |
| edited | varchar(60) | YES | | NULL | |
| last_post_date | varchar(19) | YES | | NULL | |
| number_posts_topic | varchar(20) | YES | | NULL | |
+--------------------+-------------+------+-----+---------+-------+
12 rows in set (0.00 sec)


$insert = "INSERT INTO table1 (subject, reply, date, id, topicid, user) values
('$repsub','$reptext','$date','$id','$topicid','$user')";
if ($insert) {
echo "You successfully added a post and will be redirected in 2 seconds.";
}

$result = mysql_query($insert) or die ("Could not connect to server with Query.\n Please try
later!");


This you mean ?

Posted: Sat Nov 30, 2002 5:04 pm
by bjugis
The fields are not configured optimal I know, but it's just temporary.

Posted: Sat Nov 30, 2002 5:44 pm
by mydimension
try adding an auto increment field in there.

Table Structure

Posted: Mon Dec 02, 2002 4:41 am
by Bitmaster
It looks like your problem is caused by the table structure. I'm not familiar with mySQL but i noticed there's no primary key definition in your table, so the server isn't required to store and retrieve records in any specific order.
First, you should consider adding a primary key to your table. A primary key forces the server to ensure that no two records have the same value on that column, and it also forces a default sort order when retrieving records. The first thing that comes to mind is a Timestamp (Date and Time) field(s) that will serve as your primary key. All SELECTS on that table will return records in ascending order based on this timestamp. You can also issue a SELECT like this: SELECT * FROM ... WHERE ... ORDER BY TSTAMP DESCENDING, and it will return rows in descending timestamp order (ie newest records first).
Another approach would be to add an autoincrement column to your table (say, MSG_ID) as a primary key, and then SELECT * FROM ... WHERE ... ORDER BY MSG_ID DESCENDING, and you'll get the same result as above, newest records first. This is called a Surrogate primary key (not made up of specific record attributes).

Posted: Mon Dec 02, 2002 10:18 am
by BigE
Right, its all in the Keys. What you need to do is define a Primary Key and hopefully other Indexes for the table. I suggest that the timestamp be an Index for the table. If you want to learn more about Keys and Indexes, please goto http://www.mysql.com/doc/

Posted: Wed Dec 04, 2002 3:01 pm
by bjugis
I got it. Tnx :D