The first table accepts two values from users: their name and the city
they live in. The fields affected in that table are CITY and NAME.
Then each time a new entry is made to this table, another is made to
tblPopularNames with that city and the name that occurs most
frequently against that city in tblLocations. For example, if John is
the most popular name in NY, tblPopularNames gets updated with NY,
John. –
Okay, so let's break this up into a trigger. each time a new entry is made translates to AFTER INSERT ON tblLocations FOR EACH ROW; the name that occurs most frequently against that city in tblLocations means we run a SELECT NEW.insertedCity, old.insertedName FROM tblLocations AS old WHERE insertedCity = NEW.insertedCity GROUP BY insertedName ORDER BY COUNT(*) DESC LIMIT 1; and we may want to add something to that ORDER BY to avoid several names at equal frequency get extracted at random.
There's an additional requirement, that if the city already exists in tblPopularNames the entry be updated. We need a UNIQUE KEY on tblPopularNames.popularCity for that; it will allow us to use ON DUPLICATE KEY UPDATE.
And finally:
DELIMITER //
CREATE TRIGGER setPopularName
AFTER INSERT ON tblLocations
FOR EACH ROW BEGIN
INSERT INTO tblPopularNames
SELECT NEW.insertedCity, insertedName
FROM tblLocations
WHERE insertedCity = NEW.insertedCity
GROUP BY insertedName
ORDER BY COUNT(*) DESC, insertedName
LIMIT 1
ON DUPLICATE KEY
UPDATE popularName = VALUES(popularName)
;
END;//
DELIMITER ;
Test
mysql> INSERT INTO tblLocations VALUES ('Paris', 'Jean'), ('Paris', 'Pierre'), ('Paris', 'Jacques'), ('Paris', 'Jean'), ('Paris', 'Etienne');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tblPopularNames;
+-------------+-------------+
| popularCity | popularName |
+-------------+-------------+
| Paris | Jean |
+-------------+-------------+
1 row in set (0.00 sec)
mysql> INSERT INTO tblLocations VALUES ('Paris', 'Jacques'), ('Paris', 'Jacques'), ('Paris', 'Etienne'); Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tblPopularNames;
+-------------+-------------+
| popularCity | popularName |
+-------------+-------------+
| Paris | Jacques |
+-------------+-------------+
1 row in set (0.00 sec)
Triggers vs. code
There's no denying @Phil_1984's answer has lots and lots and lots of merit. Triggers have their uses but they aren't a silver bullet.
Moreover, at this stage it's possible that the design is still too early in its lifecycle for it to be worth the hassle of outsourcing the hard work to a trigger. What if, for example, you decide to go with the "counter" solution hinted at above? Or what if you decide to complicate the choice of the popularName?
There's little doubt that maintaining (which includes thoroughly field-testing) a trigger is much more expensive than the same thing done in code.
So what I'd really do is first to design a function or method with the purpose of receiving the insertedValues and doing some magic.
Then I'd emulate the trigger code with several queries in PHP, wrapped in a transaction. They would be the same queries that appear in the trigger, above.
Then I'd go on with the rest of the work, safe in the knowledge that this solution is working, if perhaps amenable to performance improvement.
If, much later, the design is convincing and gets committed, it will be very easy to modify the function to only run one INSERT query and avail itself of a trigger - that one, or the slightly modified one that has evolved in the meantime.
If the slightly modified has been taken over by creeping featurism and is not easily backported to a trigger, you need do nothing, and have lost nothing. Otherwise you've lost the time for the initial implementation (very little) and are now ready to profit.
So my answer would be: both :-)
Slightly different use case (per comments)
The thing is, the first query being performed by PHP is an
indefinitely large one with potentially hundreds of entries being
inserted at once. And I do need to update the second table every time
a new entry is made to the first because by its very nature, the most
popular name for a city can potentially change with every new entry,
right? That's why I was considering a trigger since otherwise PHP
would have to fire hundreds of queries simultaneously. What do you
think?
The thing is: what should it happen between the first and the last INSERT of that large batch?
Are you using the popular name in that cycle?
If yes, then you have little choice: you need to examine the popularity table after each insert (not really; there's a workaround, if you're interested...).
If no, then you can do all the calculations at the end.
I.e., you have a long list of
NY John
Berlin Gottfried
Roma Mario
Paris Jean
Berlin Lukas
NY Peter
Berlin Eckhart
You can retrieve all the popular names (or all the popular names with cities in the list you're inserting) together with their frequency, and have them in an array of arrays:
[
[ NY, John, 115 ],
[ NY, Alfred, 112 ],
...
]
Then from your list you "distill" the frequencies:
NY John 1
NY Peter 1
Berlin Gottfried 1
Roma Mario 1
Paris Jean 1
Berlin Lukas 1
Berlin Eckhart 1
and you add (you're still in PHP) the frequencies to the ones you retrieved. In this case for example NY,John would go from 115 to 116.
You can do both at the same time, by first getting the "distilled" frequency of the new inserts and then running a query:
while ($tuple = $exec->fetch()) {
// $tuple is [ NY, John, 115 ]
// Is there a [ NY, John ] in our distilled array?
$found = array_filter($distilled, function($item) use ($tuple) {
return (($item[0] === $tuple[0]) && ($item[1] === $tuple[1]));
}
if (empty($found)) {
// This is probably an error: the outer search returned Rome,
// yet there is no Rome in the distilled values. So how comes
// we included Rome in the outer search?
continue;
// But if the outer search had no WHERE, it's OK; just continue
}
$datum = array_pop($found);
// if (!empty($found)) { another error. Should be only one. }
// So we have New York with popular name John and frequency 115
$tuple[2] += $datum[2];
$newFrequency[] = $tuple;
}
You can then sort the array by city and frequency descending using e.g. uasort.
uasort($newFrequency, function($f1, $f2) {
if ($f1[0] < $f2[0]) return -1;
if ($f1[0] > $f2[0]) return 1;
return $f2[2] - $f1[2];
});
Then you loop through the array
$popularName = array();
$oldCity = null;
foreach ($newFrequency as $row) {
// $row = [ 'New York', 'John', 115 ]
if ($oldCity != $row[0]) {
// Given the sorting, this is the new maximum.
$popularNames[] = array( $row[0], $row[1] );
$oldCity = $row[0];
}
}
// Now popularNames[] holds the new cities with the new popular name.
// We can build a single query such as
INSERT INTO tblPopularNames VALUES
( city1, name1 ),
( city2, name2 ),
...
( city3, name3 )
ON DUPLICATE KEY
UPDATE popularName = VALUES(popularName);
This would insert those cities for which there's no entry, or update the popularNames for those cities where there is.
