jhernst
Posts: 5
|
| Posted: 05/30/2005, 3:46 AM |
|
Hello everyone,
is there a possibility to determine, which data records were changed by a MySQL update, e.g. by expenditure of the respective primary key?
Greetings, Jari
|
 |
 |
Nicole
Posts: 586
|
| Posted: 05/30/2005, 4:33 AM |
|
Jari,
You can determine if field value is to be updated in Before Execute Update event. The old value that is currently stored in a table should be retrieved with CCDLookUp() function (http://docs.codecharge.com/studio/html/ProgrammingTechniques/HowTo/WorkingWithDatabases/LookupSingleDBValue.html?toc), the new value entered on a form by user could be obtained from
$Form_name->ds->field_name->GetValue()
_________________
Regards,
Nicole |
|
|
|
jhernst
Posts: 5
|
| Posted: 05/30/2005, 5:52 AM |
|
Thanks for your quick answer!
But maybe my description was not very good:
I am searching for a way to look up all database items which have been changed during the update process - not before the update is taking place.
Something like mysql_affected_rows() or mysql_insert_id() for an update action, that gives me the primary key of every changed db item.
Jari
|
|
|
|
Nicole
Posts: 586
|
| Posted: 05/30/2005, 6:33 AM |
|
Hmm.. but what prevents you from using mysql_affected_rows()?
_________________
Regards,
Nicole |
|
|
|
jhernst
Posts: 5
|
| Posted: 05/30/2005, 7:37 AM |
|
Because it only gives back the number of data rows that have been affected, not the exact id's.
Jari
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/30/2005, 10:39 AM |
|
This is rather a question to MySQL AB since they should know if their database supports such feature. I suspect that it doesn't, in which case you'd could implement this at the application level - for example set "datetime_updated" field in your database table to the current datetime. You can also store UserID in the table, and any other information that may help you identify the database changes. I think that MySQL can also create log files for all database updates, but I don't know if you can access that information via SQL or PHP.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
jhernst
Posts: 5
|
| Posted: 05/31/2005, 1:26 AM |
|
Maybe I should go a little deeper into my current problem:
I developed a menu structure, which is able to generate menus dynamically from a mysql database based on the "VerticalCSSMenu" example from the ExamplePack 2. This worked marvelously, however has the restriction that an assortment of the menu items is not intended in the example. I therefore inserted a sorter, which does not only contain the parents category, but also the accurate position within the menu structure.
I managed it to sort all menu items on every level depending on the parent id by using a selfcoded function which is called in "Custom Update" dialog of the form.
But with this solution I am only half way through the solution because there is the possibility of child or even sub-child items where the sorter has to be changed, too - according to the new sorter of the parent item.
I tried to use the "After Execute Update" action for these changes, but was not able to identify the changes made by the MySQL update .
Where should I put the code that selects all db items that have to be updated according to the changed parent sorter?
Thanks, Jari
|
|
|
|
Kevin A.
|
| Posted: 06/09/2005, 6:52 AM |
|
I use an AfterExecuteInsert() event:
//Custom Code @49-8E5B6D29
// -------------------------
global $company; //form name
global $DBdbc1; //database connector name
global $LastID;
$LastID = CCDLookUp('last_insert_id()', '', '', $DBdbc1);
You could change $LastID to an array then walk it when you wanted to look at what was inserted.
BUT: Your trying to find the UPDATED items. Maybe you should be looking at using arrays; read the menu from the database then sort and store the results in arrays, save the serialized arrays into the database session. Using this approach works with all databases and it takes less database processing time.
Note: mySQL "SHOW TABLE STATUS" to find any table in a given database that has been updated but it does not give you the ID of the records that have been updated. You could start mySQL with a log file then parse the log file for the ID of updated records but why?? If your creating a dynamic menu then use arrays, they are easier and faster to work with and you can use client side logic to handle sorts.
|
|
|
|
RonB
Posts: 228
|
| Posted: 06/17/2005, 9:58 PM |
|
You couold also use the temp table feature. Before the update you create a temptable based on the menu table. Do the update and compare the rows .
Asuming you created the menu table with an autoincrement field you should have a unique identifier for each row. No query the menu table and the temp table :
select menutabel.somefield, temptable.somefield
from menutable,temptable
where menutable.id=temptable.id
and menutable.somefield <>temptable.somefield
just an idea 
|
|
|
|
|
