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

[Anne Arbor]

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.

[james438]

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.

[Anne Arbor]

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

[Anne Arbor]

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.

[james438]

Do your tables look something like this:

users table:

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:

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.

[Anne Arbor]

Yes, I believe that is exactly how they look.

[Anne Arbor]

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

[Anne Arbor]

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

[james438]

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.

[Anne Arbor]

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.