Program stopped adding new entries at end of db; inserts them out of chrono order

I have a small program and a small database, both of which I constructed myself, using PHP and MySQL.

The database has two simple tables, one for user registration info ('registration') and one for user data entries ('temperatures').

The program was constructed in response to a need of mine, and I remain by far its most frequent user.

Both program and database were working well except that a spammer would sometimes succeed in adding spam entries. One day I went into PHPMyAdmin and deleted most of the spam entries. I did this by deleting rows in the 'temperatures' able.

The program has not worked properly since that time. Now, when I make a new entry, the new row is not added at the end of the database but is inserted 'up-table'. For example, a new entry made today, 1-22-2014, should be added at the end of the database but will instead be added between two old entries, e.g., between 6-17-12 and 6-30-12 or between 10-1-2013 and 10-9-2013. (I don't add an entry every day.) There does not seem to be any rhyme or reason to the location where the new entry is added -- it does not seem to be in any apparent order.

I'd like to go back to adding new entries in chronological order, i.e., at the end of the table. Can anyone help me understand what went wrong? and how to fix it?

Thank you so much for any help you can give.

Would you be able to tell us how your two tables are set up and what columns you use? Do you use auto increment for any of your columns? How do your two tables interact?

Depending on the unique column you have you can fix this with a small php script or by altering your table somewhat. Still, we will need to know more about the structure of the table you are having trouble with.

James438,

Thank you very much for your reply.

Here is my db design. Is that everything you need for the moment?

users table:

Field Type Null Default Auto_increment

user_id smallint(5) No

email varchar(40) No
password varchar(40) No
first_name varchar(15) No
last_name varchar(30) No
active char(32) Yes NULL
registration_date datetime No

- - - -

temperatures table:

Field Type Null Default

user_idm smallint(5) No
temp_rating char(2) No
notes text Yes NULL

time_entered datetime No
time_entered2 timestamp No
CURRENT_TIMESTAMP

A little more about the database:

a: The user_id column in the 'users' table *is* set to Auto_increment.

b: As near as I can tell, I do not have any Primary key - is that possible? (Should that be changed?)

c: When users are deleted from the 'users' table, their entries are not deleted from the "temperatures" table. Should they be? (and how would I do that?)


Finally, When I was trying to put this together, I had difficulty understanding how to connect the two tables together, or how to make them interact. I never did really understand what was happening, but I succeeded in doing something by using sessions, in addition to whatever is reflected in the db structure, above.

Do your tables look something like this:

users table:

Code:

---

Field                Type                Null        Default    Auto_increment

user_id        smallint(5)        No                        yes
email                varchar(40)        No   
password        varchar(40)        No   
first_name        varchar(15)        No   
last_name        varchar(30)        No   
active                char(32)        Yes
registration_date datetime        No

---

- - - -

temperatures table:

Code:

---

Field                Type                Null        Default 

user_idm        smallint(5)        No   
temp_rating        char(2)        No   
notes                text                Yes        NULL
time_entered        datetime        No   
time_entered2        timestamp        No
CURRENT_TIMESTAMP

---

I'm looking at your tables now. Which table is having the entries placed in an incorrect order? Normally this wouldn't matter because you can just choose to view the table and order by time_entered or registration_date. How are you viewing your information? Is the query Select * from 'temperatures' if so you may want to alter it to Select * from 'temperatures' order by 'time_entered' or something similar.

Yes, I believe that is exactly how they look.

Correction: In the second table, concerning the 'notes' column, it is marked NULL under 'Default.'

Oh, I think I even understand what you are saying!!! Let me check the code and come back. :-)

My post above has been updated.

Also, how is your content being displayed? I'm not sure that the tables need to be joined. Depending on how your tables are being used I am not so sure that there needs to be a unique or Primary ID in your table if you are using auto_increment.

Sorry to ask such an elementary question, but how do I post the code in proper format? Haven't done that for several years and just can't remember.