Insert as last!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bjugis
Forum Newbie
Posts: 6
Joined: Fri Nov 29, 2002 5:08 am

Insert as last!

Post 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 ?
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post 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?
bjugis
Forum Newbie
Posts: 6
Joined: Fri Nov 29, 2002 5:08 am

Post 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 ?
Last edited by bjugis on Sat Nov 30, 2002 5:11 pm, edited 1 time in total.
bjugis
Forum Newbie
Posts: 6
Joined: Fri Nov 29, 2002 5:08 am

Post by bjugis »

The fields are not configured optimal I know, but it's just temporary.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

try adding an auto increment field in there.
Bitmaster
Forum Newbie
Posts: 20
Joined: Thu Nov 21, 2002 8:42 am

Table Structure

Post 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).
User avatar
BigE
Site Admin
Posts: 139
Joined: Fri Apr 19, 2002 9:49 am
Location: Missouri, USA
Contact:

Post 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/
bjugis
Forum Newbie
Posts: 6
Joined: Fri Nov 29, 2002 5:08 am

Post by bjugis »

I got it. Tnx :D
Post Reply