| Author | Thread |
|
|
05/17/2010 03:05:27 PM · #1 |
Actually, I believe what I am trying to do in MySQL 101, but for the life of me, I have no clue how to accomplish it. At the core, I have two tables within one database. I need a script or something to automate removing entries from one table when they appear in another. This will be based on the primary key for each table matching.
Essentially, you could think of one of the tables as a "To Do" list, and the other table as a "Completed" list. What I need is a way of removing the item from the "To Do" list, when it is entered into the "Completed" list.
If anyone has any experience with this sort of thing, I would be very appreciative for some help!
Thanks in advance.
ETA: Navicat is a GUI option instead of using MyPHP which is what is native to my host. I started out with MyPHP, but I really got bogged down because I didn't understand the scripting side. Hence, I paid for Navicat which essentially logs into my db and provides me with a "windows" style interface
Message edited by author 2010-05-17 15:34:41. |
|
|
|
05/17/2010 03:23:11 PM · #2 |
Can you post some additional details? There's many ways to skin a cat... The "best" way is going to depend on how much access you have (both db access and code access). For example, are you maintaining the code which is actually doing the table population, or are you simply an "observer"? If the latter, are you polling the db on a regular basis to check for keys, or do you have a hook into the insert call so you can inject additional sql?
There are always ways you could enforce this on the db side itself, since you're looking at what essentially sounds like a unique primary key, but that has some subtle effects you might not want, and would require full db access to change the schemea.
In other words... please provide more input. :P
|
|
|
|
05/17/2010 03:30:52 PM · #3 |
I am (trying) to maintain the db myself using navicat. I believe it has full sql scripting capabilities (navicat that is). Essentially I get a list of things to do on Monday. I run an import wizard into the to do list. On Friday, I get a list of completed activities. I then run a wizard and import them into the completed database. The problem that I am running into is that I can run a query to see which entries match in both tables, but I have to manually, one by one, delete them out of the to do table. Some weeks this means upwards of 200 entries.
Does that help? I just checked and indeed navicat supports SQL scripting. I have NFC how to script in SQL though.....
Perhaps logging and seeing the beast for yourself would help? I dunno..... |
|
|
|
05/17/2010 03:37:39 PM · #4 |
Well, it depends.
One might argue that your application should delete the TODO entry upon creating the COMPLETED entry, thereby keeping itself in a consistent state.
Another implementation might setup a database trigger to automatically delete the TODO entry when a COMPLETED entry is created. This seems a bit lazy to me because your logic is split between your database and your application, but it would get the job done.
Either of the above would automatically keep your database in a consistent state. If you have need to manually go back and cleanup the tables, and if I'm understanding correctly that a given entry in TODO and COMPLETED will have the same id, it should be as simple as:
delete from TODO, COMPLETED where TODO.id=COMPLETED.id;
But be careful playing around with stuff like this. Do it in a test database first, because there's no undo. |
|
|
|
05/17/2010 03:44:16 PM · #5 |
I am already lost to be honest.....
I understand the concept completely, but my lack of coding knowledge is my downfall here. I have NFC how to write/implement/check something like that......
As far as the application is concerned, the two tables, although in the same db, are completely separate (and that is how the db is set up in MyPHP). The problem perhaps is I don't know how to link them???? So that the TODO table checks the COMPLETED table?
Does that sound like a fair assumption? |
|
|
|
05/17/2010 07:42:35 PM · #6 |
Originally posted by smurfguy:
delete from TODO, COMPLETED where TODO.id=COMPLETED.id; |
That's assuming the completed id matches the todo id. I would think it would be cleaner to consolidate the two tables into TASKS and use a status field of completed/not completed and then filter out completed tasks with a simple query.
Navicat also has a visual query designer, but yeah, you do have to know something about relationships.
Originally posted by smurfguy:
But be careful playing around with stuff like this. Do it in a test database first, because there's no undo. |
In Navicat, you can right click on the table and select "Duplicate Table" and it will create TODO_copy - do this before you run update or delete queries and then if something goes wrong, you can delete the TODO table and rename TODO_copy to TODO.
*I love Navicat. :) |
|
Home -
Challenges -
Community -
League -
Photos -
Cameras -
Lenses -
Learn -
Help -
Terms of Use -
Privacy -
Top ^
DPChallenge, and website content and design, Copyright © 2001-2025 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 08/23/2025 11:01:47 AM EDT.
