davidannis Posted June 14, 2014 Share Posted June 14, 2014 I have a table leitner_vcard_boxes. Each row represents a flashcard. I want to be able to efficiently sort the cards by the frequency with which the user got them correct. Each row has a column right_count and wrong_count and a percent_correct which I calculate in php as right_count/(right_count+wrong_count) I want to replace the manual calculation with a trigger since I believe that would be more efficient than reading the row, incrementing the right or wrong counter, calculating the new percentage correct and updating the row. The trigger I plan to use is: CREATE TRIGGER upd_right_percent AFTER UPDATE ON leitner_vcard_boxes FOR EACH ROW BEGIN UPDATE leitner_vcard_boxes SET right_percent=right_count/(right_count+wrong_count); END; My questions are: 1. Is the trigger going to give me the desired outcome? 2. If I export the leitner_vcard_boxes table from my development server's phpMyAdmin and replace (import) the table on the test server will the trigger move with it? Do I need to move the trigger separately? 3. How do I list the triggers in the database? Can I see them in phpMyAdmin someplace and I am just missing it? 4. What else should somebody who has not used triggers before consider? Link to comment https://forums.phpfreaks.com/topic/289150-creating-triggers-in-phpmyadmin-beginner-questions/ Share on other sites More sharing options...
DavidAM Posted June 25, 2014 Share Posted June 25, 2014 Assuming we are talking about MYSQL: 1. No - Unless something has changed recently, you can NOT update a table in the table's trigger. You could use a BEFORE UPDATE trigger to SET NEW.right_percent = NEW.right_count/(NEW.right_count + NEW.wrong_count);However, see Caveat below 2. Triggers are not exported with the data unless you explicitly ask to have them exported. I don't use phpMyAdmin much, I generally use the command line. You have to explicitly tell mysqldump to include the triggers. 3. I don't think phpMyAdmin provides a way to see them.. From the mysql command line, the command is SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]You could run that command in a (phpMyAdmin) query window. Caveat You really should NOT store a calculated field in the database. Calculate the percentage when you SELECT the data. SELECT ... right_count, wrong_count, right_count + wrong_count AS Total_Count, right_count / (right_count + wrong_count) AS Right_Percent ...Also, be sure your updates are autonomous. That is, don't do this: # BAD CODE # Select from table and store in local variables $right = $row['right_count']; $wrong = $row['wrong_count']; # Update local variables if ($user_is_right) $right += 1; else $wrong += 1; # Update the database UPDATE table SET right_count = $right, wrong_count = $wrong ... This would overwrite any changes by another user between the SELECT and UPDATE by this user.Instead: $add_right = 0; $add_wrong = 0; if ($user_is_right) $add_right = 1; else $add_wrong = 1; UPDATE table SET right_count = right_count + $add_right, wrong_count = wrong_count + $add_wrong ... This would increase whatever value is NOW in the database, regardless of how many users updated since this user started. Link to comment https://forums.phpfreaks.com/topic/289150-creating-triggers-in-phpmyadmin-beginner-questions/#findComment-1483207 Share on other sites More sharing options...
davidannis Posted June 25, 2014 Author Share Posted June 25, 2014 Thank you. That answer is extremely helpful. Link to comment https://forums.phpfreaks.com/topic/289150-creating-triggers-in-phpmyadmin-beginner-questions/#findComment-1483216 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.