Colleagues, good afternoon, I apologize in advance for confusion, I am more of a front-line soldier, but work also forces me to write in the backend.

I ask to help with the problem or give directions, Ajax and Comet does not suit me, there is no possibility to load the server, I intuitively feel that the solution should be simple, thank you in advance.

So the problem is:

On the page, for example, page.php there is a standard SQL counting of the term term in Table No. 1 with simultaneous recording of the total of the counting in Table No. 2, now when in Table No. 1 the number of lines sent from everything from another page opened by the client increases, counting and writing to Table 2 without updating the page.php page does not occur. So, how to register on the client side or in some other way update page.php if the client does not include it and does not enter or does it have other solutions? I just wanted the code in page.php to be on the server side and not load on the client side.

  • I will clarify. There are two bases. you want to denormalize. So that the current value of the number of rows in DB1 is always stored in BD2? Bases different or base one, but two schemes? - Ninazu
  • @Ninazu, Thanks for clarifying. I have incorrectly stated, sorry, the entire database is one, and there are two tables. Roughly speaking, the actions of customers (for example, a click) are recorded in one table No. 1; the sum of all these clicks is recorded in another table No. 2. This whirl is due to the creation of charts using Google charts. The customer asks for a beautiful analytics. The SQL code for the sum of the lines is written on the page.php page to which the client never gets and will not fall. And according to this, this page cannot be updated accordingly, the changes in the table №1 SQL code does not see, as a result, the diagram is incorrect. - Vika Smirnova

1 answer 1

Cited common ways to solve your problem, but here everything depends on the architecture used by you and the requirements for the relevance of the data.

Let me give you an analogy, there is a store that sells goods and sold goods are recorded in the database, but there is a manager who wants to know for what amount the goods were sold, for this he has several solutions:

  • Run counting every n-time (it doesn't matter to him that this minute is not quite accurate)
  • Receive a message about the goods sold and independently run the count of goods sold
  • When adding a product, immediately aggregate the statistics automatically through various ways:
    1. Send data or tasks to a remote server to run automatic counting.
    2. Aggregation on the side of the program that performs the addition (with large data it will slow down if it is not just an atomic operation)

Counting all lines in the database for output

In your case, probably the best solution and a simple solution is to run the counting script (page.php) every 1-5 minutes by CRON, if you don’t need to know the exact number of added lines every second.

The second solution, which allows you to accurately count the number of lines, is on the side of the script, which adds data to the database (if it is a single point of addition), an increase in the value of a cache on the number of added rows and then output this value . Here you need to think a bit about the architecture, because lines can still be deleted, etc. You can also hang a trigger on the table, which, when adding a record, would perform some kind of atomic operation to increase the value in another table.

Also, a script can receive a message from another script through a queue and, upon receipt of a message, perform actions related to row counting - this is already demonization.

Counting lines for a specific client

Further, if your problem consists precisely in counting not all the rows, but counting the rows for the client, here it is already a bit more complicated. If the base is small, then counting every minute for everyone is not difficult, but if the base is huge and you need to count for all clients with grouping, this query can be executed longer and every 1-5 it can simply not be executed, it is possible to use counting here on request.

The client accesses the script continuously through the site, but the script saves the data only 1 time, after which it stores the value in the cache for 5 minutes, if the client has added some data, they can be disabled in the cache.

  • I also dug out this solution: keep a record in the database (execution date) ... if the date is not today, then update the record, execute the script. How do you like this solution? - Vika Smirnova
  • @KBMira You can add as an additional condition to the script, but using CRON in your case, I think this is the most optimal solution for time and speed. Run at the interval at which data accuracy is important. - Firepro
  • Thanks for answers. Yesterday I experimented and that's what happened: I added include_once'page.php 'to the user’s index.php page, but first I manually increased the number of rows in the admin panel, updated index.php and ... wonder ... page.php all added up and updated the record in table 2. Apparently, it is necessary to learn the basics of php - Vika Smirnova