Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default 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.
    Last edited by Anne Arbor; 01-23-2014 at 12:25 AM.

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

    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
    Last edited by Anne Arbor; 01-22-2014 at 10:25 PM.

  4. #4
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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.
    Last edited by james438; 01-22-2014 at 11:19 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

  6. #6
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

    Yes, I believe that is exactly how they look.

  7. #7
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

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

  8. #8
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

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

  9. #9
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  10. #10
    Join Date
    Jul 2010
    Location
    Near Albany, NY
    Posts
    56
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Default

    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.

Similar Threads

  1. xml/rss entries are too long???
    By cocidom in forum Dynamic Drive scripts help
    Replies: 2
    Last Post: 03-22-2010, 10:30 AM
  2. Replies: 1
    Last Post: 09-12-2007, 06:29 AM
  3. how to edit timestamp entries
    By blwow in forum PHP
    Replies: 5
    Last Post: 05-06-2007, 08:30 AM
  4. Code in one order, appearance in other order
    By motormichael12 in forum PHP
    Replies: 1
    Last Post: 11-13-2006, 05:44 PM
  5. text rollovers stopped working after adding DD slide show script
    By john5 in forum Dynamic Drive scripts help
    Replies: 1
    Last Post: 09-24-2004, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •