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.

[james438]

No worries. I wish it were part of the default options, but that does not appear to be the case any longer. For more options when editing a post choose the Go Advanced button on the lower right of the page and there will be more formatting options. Alternately you can put your code in
tags.

For information on normalized tables I have found that this is a good place to start:

http://mikehillyer.com/articles/an-introduction-to-database-normalization/

[Anne Arbor]

Here is the relevant code, including the formatting info. I realize that the mysql queries should be changed to mysqli. And that other aspects of this are probably embarrassing. I really have only a slight grasp of what I am doing much of the time.





$query = "SELECT * FROM temps WHERE user_idm = $_SESSION[user_id]";

$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Temperature</th> <th>Notes</th> <th>When entered</th></tr>";

// Print the results, row by row, into a table; using a while loop:

// Use a while loop, to print the results, row by row:


while ($row = mysql_fetch_array($result )) {

// Print out the contents of each row into the table

echo "<tr><td>";
echo $row['temp'];
echo "</td><td>";
echo $row['notes'];
echo "</td><td>";
echo $row['time_entered2'];
echo "</td></tr>";
}

echo "</table>";

[james438]

Have you tried changing:

$query = "SELECT * FROM temps WHERE user_idm = $_SESSION[user_id]";
to

$query = "SELECT * FROM temps WHERE user_idm = $_SESSION[user_id] ORDER by time_entered2";

[Anne Arbor]

No, I haven't tried that yet. It was all I could do today to open my cPanel and then open PHPMyAdmin, and then do some fiddling with the database -- really, all these kinds of things make me break out into a light sweat from anxiety.

Now I have to use ftp again for the first time in more than a year. More sweating! ;-)

As long as I'm going to try that, wherever 'mysql' appears, should I change that to 'mysqli' ??

[Anne Arbor]

James,

I've made the change you suggested in the query, and it seems to have done the trick. (I didn't change anything else because I was concerned about 'unintended consequences.')

While making this change, I learned something else that should be a big help in the future -- it is possible to edit code while in cPanel. That will certainly be far more convenient in future than ftp'ing every little change.

This was great, James. Thank you for your patience and all your help. I'm truly grateful for both. :-)

[james438]

No problem. Happy to help :)

As for changing mysql to mysqli I only just found out about it a little over a year ago.

change

$result = mysql_query($query) or die(mysql_error());
to

$result = mysqli_query($conn,$query) or die(mysql_error());

For more info I recommend looking at this thread (http://www.dynamicdrive.com/forums/showthread.php?71716-mysql_*-is-being-deprecated-!&). It should give you a short and sweet discussion about it.