Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

insert new values do not update

Posted on 2013-11-24
7
329 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:rgb192
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39673607
Then you would need to do a select of the original row and compare if either are different, if they are then insert new row.
Or store the originals in hidden fields and compare the two values when the page is submitted.
0
 
LVL 1

Accepted Solution

by:
gwnet earned 167 total points
ID: 39673888
Check this site :
http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html
http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

Add trigger BEFORE UPDATE.

CREATE TRIGGER trigger_name BEFORE UPDATE ON  [u]YOURTABLE[/u]
  FOR EACH ROW
BEGIN
     IF OLD.body != NEW.body OR OLD.title != NEW.title THEN
        INSERT INTO [u]YOURTABLE[/u];
    ELSE 
        // UPDATE  [u]YOURTABLE[/u];
    END IF;
END;

Open in new window



Adjust it to what you need ...
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39674447
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rgb192
ID: 39679142
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
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 39679169
a waste of database space
But I understood that you wanted historical data.  Every year on my birthday I do a survey of the cost of disk storage. comparing the cost of storage to the cost of toilet paper.  Last time I checked you could buy a gigabyte of storage for the cost of a few sheets of toilet paper, so maybe constrained disk storage is not something to worry about much.

Of course, you could prune the table.  Make a DELETE query for an identical match on title and body.  Like most things in computer programming there are many ways to approach the problem and when we only have a fragment of the design concept we are likely to be unable to give you the best advice.
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 167 total points
ID: 39679196
There is no syntax per se
When the user goes to edit the title or body place the current values in hidden fields.
When they click submit to save their changes just compare the hidden values to their posted (amended) values.
0
 

Author Closing Comment

by:rgb192
ID: 39685519
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question