Dynamic value to display numbers of entries in second table

Question

I've got multiple entries in table A and would like to display the number of entries in a coloumn of table B. Is there a way to create a dynamic cell-content displaying the number of entries in a table?

I'm a beginner in MySQL and did not find a way to do it so far.

Example table A:

+----+------+------------+
| id | name | birthday   |
+----+------+------------+
|  1 | john | 1976-11-18 |
|  2 | bill | 1983-12-21 |
|  3 | abby | 1991-03-11 |
|  4 | lynn | 1969-08-02 |
|  5 | jake | 1989-07-29 |
+----+------+------------+

What I'd like in table B:

+----+------+----------+
| id | name | numusers |
|  1 | tblA |        5 |
+----+------+----------+

In my actual database there is no incrementing ID so just taking the last value would not work - if this would've been a solution.

If MySQL can't handle this the option would be to create some kind of cronjob on my server reading the number of rows and writing them into that cell. I know how to do this - just checking if there's another way.

I'm not looking for a command to run on the mysql-console. What I'm trying to figure out is if there's some option which dynamically changes the cell's value to what I've described above.

Possible duplicate of [Get record counts for all tables in MySQL database](http://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database) — trincot, May 15 '16 at 21:23
This would be roughly what my cronjob would look like. However my actual question is: Is this possible without typing anything into the console (or writing a script which does that for me)? E.g. putting that command as the cell's value or something. — chris137, May 15 '16 at 21:40
you could write a trigger after insert on table A to update the value in table B to the count(*) of table A — Tin Tran, May 15 '16 at 21:46
Isn't that what views are for? They look like tables. Not sure what you mean with *cell* -- that is not really database language. — trincot, May 15 '16 at 22:08

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

You can create a view that will give you this information. The SQL for this view is inspired by an answer to a similar question:

CREATE VIEW table_counts AS 
SELECT table_name, table_rows
  FROM information_schema.tables 
 WHERE table_schema = '{your_db}';

The view will have the cells you speak of. As you can see, it is just a filter on an already existing table, so you might consider that this table information_schema.tables is the answer to your question.

edited May 23 '17 at 10:29

Community

1
1

answered May 15 '16 at 22:14

trincot

317,000
35
244
286

This is the closest to what I was looking for. I guess noone understood what I actually wanted since it's total nonsense. Realized that after I slept over it. Thanks for your solution. Using it now! – chris137 May 16 '16 at 08:33

score 0 · Answer 2 · answered May 15 '16 at 21:49

You can do that directly with COUNT() for example SELECT COUNT(*) FROM TblA The you get all rows from that table. If you IDXs are ok then its very fast. If you write it to another table you have to make an request too to get the result of the second table. So i think your can do it directly.

If you have some performance problems there are some other possibilities like Triggers or Stored Procedures to calculate that result and save them in a memory table to get a better performance.

Dynamic value to display numbers of entries in second table

2 Answers2