Insert as last!
Moderator: General Moderators
Insert as last!
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 ?
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
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 ?
+--------------------+-------------+------+-----+---------+-------+
| 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 ?
Last edited by bjugis on Sat Nov 30, 2002 5:11 pm, edited 1 time in total.
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
Table Structure
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).
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).
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/