rgb192
asked on
insert new values do not update
users have a title and body in their social networking website
using php, pdo with mysql
if they change their title and/or body then
columns id,profile_id, timestamp, title, body. If body,title is different save a new title,body
I want historical values so if values are different do not update
I want insert
This may be a mysql query or it may be php pdo
using php, pdo with mysql
if they change their title and/or body then
columns id,profile_id, timestamp, title, body. If body,title is different save a new title,body
I want historical values so if values are different do not update
I want insert
This may be a mysql query or it may be php pdo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are a few assumptions needed to answer, but it's a very common design pattern. By the time a user is logged in to your site, you know the content of these fields (they are probably kept in an identity array in the PHP session):
id, profile_id, timestamp, title, body
The timestamp is a MySQL column of type TIMESTAMP ON UPDATE CURRENT TIMESTAMP.
The profile_id is a key to the user's row in the data base. The user cannot update id, profile_id or timestamp. The user has an HTML form allowing updates to title, body. This form is pre-populated with the current contents of the data base in the value= attributes of the form input controls.
When the form is submitted, Your script would copy the contents of the identity array into the SQL INSERT query values, then overwrite title and body with the data that the user put into the form. Then you run the INSERT query.
Whenever you want to get the user's data, your SELECT query says ORDER BY timestamp DESC LIMIT 1. This will always give you the most recent user record. This design will preserve all of the historical changes, and with the timestamp column, you will be able to see when the changes were made.
id, profile_id, timestamp, title, body
The timestamp is a MySQL column of type TIMESTAMP ON UPDATE CURRENT TIMESTAMP.
The profile_id is a key to the user's row in the data base. The user cannot update id, profile_id or timestamp. The user has an HTML form allowing updates to title, body. This form is pre-populated with the current contents of the data base in the value= attributes of the form input controls.
When the form is submitted, Your script would copy the contents of the identity array into the SQL INSERT query values, then overwrite title and body with the data that the user put into the form. Then you run the INSERT query.
Whenever you want to get the user's data, your SELECT query says ORDER BY timestamp DESC LIMIT 1. This will always give you the most recent user record. This design will preserve all of the historical changes, and with the timestamp column, you will be able to see when the changes were made.
ASKER
GaryC123: could you give an example of syntax
gwnet: I am having trouble with the trigger. I am using godaddy shared hosting with mysqlworkbench query editor
Ray_Paseur: if the website is myspace or friendster many users have not updated their title/body in many years so there is a waste of database space especially if I check once a day
gwnet: I am having trouble with the trigger. I am using godaddy shared hosting with mysqlworkbench query editor
Ray_Paseur: if the website is myspace or friendster many users have not updated their title/body in many years so there is a waste of database space especially if I check once a day
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think the trigger is the best answer so I will read the mysql.com documentation on how to create triggers.
I think pruning rows will create incorrect select statements sometimes if I want more than one row of select statements.
I think another question will be how to select rows without showing duplicate rows more than once.
Gary's answer may make work but I do not know how to implement yet.
I think pruning rows will create incorrect select statements sometimes if I want more than one row of select statements.
I think another question will be how to select rows without showing duplicate rows more than once.
Gary's answer may make work but I do not know how to implement yet.
Or store the originals in hidden fields and compare the two values when the page is submitted.