How do I know if a record has been added to the database?

Asked

Viewed 758 times

3

Hello, I am working on a warning system, when a data larger than normal is registered in the database an alert has to appear on the page where the user is browsing. The problem is that I don’t know how to check if an element has been added to the bank.

I am automatically taking my data from a PCD (weather station), if any of these data pass the registered limit by ADM, an alert will be shown to the user.

I have the following tables in my bank:

CREATE TABLE PARAMETROS_DE_ALERTAS (
    PRA_ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    PRA_VALOR_MAXIMO INTEGER(5) NOT NULL,
    PRA_VALOR_MINIMO INTEGER NOT NULL,
    PRA_COR_MINIMA VARCHAR(30) NOT NULL,
    PRA_COR_MAXIMA VARCHAR(30) NOT NULL,
    SEN_ID INTEGER NOT NULL
);

This table is the parameter for alerts, where the minimum and maximum measurement coming from the PCD will be registered to be an alert.

CREATE TABLE MEDICOES (
    MED_ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    MED_DADO VARCHAR(10) NOT NULL,
    MED_DATA_HORA_MEDICAO DATETIME NOT NULL,
    API_ID INTEGER,
    SEN_ID INTEGER NOT NULL
);

This is the measurement table, where you will store the data from the PCD.

CREATE TABLE ALERTAS_CRITICOS (
    ALC_DATA_HORA_ALERTA DATETIME NOT NULL,
    ALC_ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    ALC_VALOR_MEDICAO FLOAT NOT NULL,
    SEN_ID INTEGER NOT NULL
);

The system must add a record of this table if the measurement data is greater or less than the parameters. After this critical alert is registered, the system must show an alert to the user who is browsing any page of my site.

The problem is that I don’t know how to check if a record has been added to the bank.

php html mysql

  • What kind of element you refer to ?

  • A table even, for example, I will get my data from a PCD (a weather station), which will send me data such as the water level of a river, if the level exceeds the limit registered by ADM, should show an alert to the user.

  • Put an example in your question, this section that defines the limit.

  • What language you are using, in the application ?

  • PHP to connect to the database and ajax to control php functions.

  • As you are receiving this data, you can put a snippet of php code?

  • I still haven’t gotten to the part of integrating my project to PCD, so far I’m adding the records directly by SQL code.

  • So I guess you have to make a comparison when inserting into the if(x > y || x < z){echo "has been added";} something like this... is this what you’re looking for? But can make queries sql oara check...

  • Yes, this part will add a critical alert record to the database, but I would like an alert to appear to the user who is browsing on any page of my website, So I guess I’d have to use a method that would check if a critical alert record was added to the bank. Or when the system adds the critical alert send something that fires a Istener on the pages that will show the alert.

  • Type, if you will have a script that will be updating the PDC information,, that is to say this script will have to be executed automatically, from time to time, and the moment the return is within the parameters it alerts to any user on any page, that’s it?

  • 1

    This, one way I thought of performing this action was to create a script that would check whether a critical alert was added to the database from time to time. I believe that if I store in a var what was the last id entered to the critical alerts table, and check from time to time if the last record inserted is > to the variable I saved, then it would trigger the alert. You think that would work?

  • I think so, there are many ways to achieve what you want. I think a block diagram might help you decide the best time to do the check. It can be at the time of consultation in the PDC, at the time of insertion in the bank, or through periodic consultations. The most important (I think) is to plan these steps well and build according to your goal.

  • Yes, thank you very much Magichat, now I have more or less the idea of how I will do this formulated!

  • This may help or complicate, it is not my intention to complicate. https://dev.mysql.com/doc/refman/5.5/en/signal.html

Show 9 more comments

3 answers

1

You can use a database function to give that input. Inside this Voce function makes the INSERT necessary and can give a SELECT right away return true or false to the application to know if it was successful.

  • Depending on the system he’s making, a trigger when inserting data into the table he wants to solve.

1

Nicolas, from what I understand your need, do not exste a method that will do everything you need, so I will suggest what I would do in your situation:

  1. Alert insert in the database
  2. Read in a json file saved in your application
  3. Generate an array, or add to the existing alert array
  4. Create a middleware for your entire application that reads this JSON and displays the alerts in the page load and or a js on the page with an infinite looping that reads through the json url
  5. Delete alerts (if part of your rule) and save this file after editing

The advantages of using json are, you can easily edit the content (using the json_encode and json_decode functions) and the reading is extremely fast as it does not need to query the database and is not a large volume of data.

0

You need to create an api that accesses the bank and can call it via ajax. This api queries the database and brings back the data and in the get of your ajax when receiving the callback of this call will be able to implement the alert

  • Yes, but there is a method that checks whether a record has been added to the database?

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.