MySQL - How to insert record if not exists to avoid data duplication

While coding an application with MySQL as database, you might come into this situation:

You want to insert a new record into a table, if and only if there is no existing record in the table with certain matching fields.

• The matching field(s) need not has a unique constrain in your table structure design.
• Your insert statement does not include value definition for the table's autoincremented primary key (the "id"). As usual, you just let the database engine to determine the value of the autoincremented field for you upon successful insert action.
• You only want to check for duplication in certain field(s). The rest of the fields can be the same or different. Meanings, as long as all those fields you want to check are completely matching, it is considered a duplicated record, regardless the rest of the fields are matching or not.
• In the event you have found a duplication, you just want to do nothing and skip the insert. This is different from another situation whereby you want to update the existing record with current one.
• You don't want to mess up with the primary key (the "id") sequence. Meanings, if there is a match, you just abort the insert action without touching the table. The "id" sequence can continue with next insert, without jumping autoincrement number.

and here is an example of the SQL statement, with value fed by PHP variables:

INSERT INTO `payment` (`year`,`month`,`item`,`category`,`type`,`account_no`,`date_due`,`value`,`payment_mode`,`paid_by`,`status`,`owner`,`payer`) SELECT $year,$month,'$item',$category,'$type','$account_no','$date_due',$value,$payment_mode,$payment_option,'$status',$owner,$payer FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payment` WHERE `year`=$year AND `month`=$month AND `item`='$item' AND `type`='$type' AND `date_due`='$date_due' LIMIT 1)

The table structure for payment is as below:

In the example above, you match on fields year, month, item, type and date_due in the table payment. Your insert statement will be executed if the select statement return no result, i.e. there is no matching record(s) fulfilling the search criteria.

My Huawei Scale 3 body analyzer scale

I've purchased the Huawei Scale 3 body analyzer scale at the price of RM99 directly from Huawei Online Store during their recent online promotion. The RRP of this scale is RM149 during normal days.

This scale is operated by 4 AAA size batteries. When used by a single person daily, the batteries can last for about a year. When used by the whole family, they can probably last for about 6 months.

Beside body weight, this scale can also measure body mass based on bioelectrical impedance analysis. It can also measure our heart rate (a.k.a. pulse rate).

 

In order to get accurate measurements, you need to step your feet on top of its 4 ITO electrodes, and wait for the 3 readings showing on its surface by turn respectively.

Before using the scale, you need to pair it with the Huawei Health app in your smartphone, which you can download from either Huawei AppGallery, Google Play Store or iTunes AppStore. It is the same Huawei Health app that connect with Huawei smart watch, smart band, heart rate monitor, and other supported wearable devices by Huawei or Honor.

You need to use Bluetooth to do the initial pairing. The scale should be able to autodetected by the latest version of Huawei Health. After that, you can add in WiFi pairing.

With WiFi pairing, your scale can actually send the measurement data to the cloud, and then sync to your handphone that is connected to the Internet, regardless of the location of your handphone.

You can share your scale with up to 10 smartphones, each installed with Huawei Health respectively.

The scale can be shared with up to 10 users, such as your family members, each will have individual historical records, and the scale is intelligent enough to identify the right person based on past record readings.

In the event 2 or more persons are having similar weight, body mass index (BMI) and body fat rate readings, the app will ask you to identify the right person for the measuring records.

There is also a guest measurement feature to let anyone to use the scale and get the measuring and analysis result with your handphone, without saving the data as historical record. In this way, you can share your scale with your guest visitors too.

The app will need you to provide your correct gender, age (calculated from date of birth) and height information, in order for its Trufit body composition analysis algorithm that is based on big data and A.I. to provide you with more than 10 body indicators.

The indicators available are:

• Weight
• BMI
• Body fat rate
• Skeletal muscle mass
• Visceral fat
• Basal metabolic rate
• Body water percentage
• Bone mineral content
• Protein percentage
• Fat-free mass
• Heart rate

Each of the indicators has own analysis and explanation screen to explain what are them in layman terms.

The app can also show you whether your weight, BMI and heart rate are respectively low, normal, slightly high or high.

Under the Body Composition section, it shows you the composition of your weight in kilograms, breakdown by water content, protein, fat, and bone mineral content.

Note that Huawei TruFit function is currently only available for adults between the age of 18 to 65. If you are under 18 or over 65, you can only get 3 results from the scale, which are your weight, BMI and your heart rate.

You can also set a goal in the app to achieve your desired weight. The app will inform you how far is your weight from your goal, and congratulate you once you've achieved your goal.

My Jumper JPD-500E fingertip pulse oximeter with OLED display

According to a study led by University of Washington at Seattle researchers, a blood oxygen saturation level below 92% and fast, shallow breathing were associated with significantly elevated death rates in a study of hospitalized COVID-19 patients. (Click here to read the original source of the research paper)

Healthcare experts also mentioned that situation of happy hypoxia, or silent pneumonia, or happy hypoxemia, is known to be a complication of COVID-19, whereby the infected patient will have no obvious symptoms such as fever or cough. Measuring the blood oxygen saturation level is a method to reveal sign of COVID-19 infection for patient with such "silent" situation.

Recently, Singapore Temasek Foundation has announced to give one pulse oximeter to every household in Singapore, so that residents can monitor their blood oxygen levels regularly. The device checks the oxygen level in the blood and can help detect early signs of a deterioration in health. (Click here to read the original source of information)

Although my Huawei Watch GT2 is able to measure blood oxygen saturation (SpO2) level, its measuring process takes some time, which is a little bit inconvenient.

During the recent online e-commerce shopping spree, I decided to buy a pulse oximeter with the following reasons:

• Unlike my Huawei Watch GT2, the pulse oximeter can provide instant reading.
• It provides "second opinion" reading, so that I can counter-check and confirm the accuracy of the readings from both devices.
• It can be easily shared among family members.
• It is very easy to use, fool-proof, and simple for elderly people like my parents to use.

My criteria to choose a genuine medical-grade pulse oximeter:

• It must be accurate. Although there are many cheap oximeters available in the online stores, I have read article that some of such oximeters come out with reading despite you trick it by putting a pencil into it instead of your finger (which it supposedly should not come out with any reading).
• At least have a brand, and better be manufactured by a medical equipment manufacturer.
• Certified as a medical device by CE and/or FDA.
• With one year manufacturer warranty.
• Budget below RM100, as I am pretty sure I should be able to get one with that price range.

and I had chosen the Jumper brand JPD-500E fingertip pulse oximeter with OLED display.

This pulse oximeter features an OLED colour screen display with 6 display modes in 4 directions. It is powered by 2 AAA batteries, which come together in the box. It also comes with printed user manual, a pouch, and a hanging stripe.

On its screen, it is able to show:

• SpO2 reading (range: 35-100, accuracy: ±2%)
• Pulse Rate (PR, range: 25-250 bpm, accuracy: ±2 bpm)
• Perfusion Index (PI, a measurement of how "strong" is the pulse)
• Photoplethysmogram (PPG, showing heart pump cardiac cycles)
• Pulse bar (showing the strength of pulse)
• Battery level

The pulse oximeter will start working once your clip your finger into it, and press on its one-and-only button. The readings shown are in real-time. It will switch off by itself after you remove your finger from it.

According to a research paper, if you are a right-handed person, you can get the most accurate value with your right hand middle finger; if you are a left-handed person, then you should use your left hand middle finger instead.

One feature I like about this Jumper JPD-500E pulse oximeter is that it is able to give an alert sound when its reading is detected to be out of normal range. This makes it really fool-proof, as you don't need to understand the readings shown on its screen. As long as it has reading and it doesn't give alert sound, you are quite OK. If it comes out with alert sound, then your reading is not OK, and you should take action on that, for example, to seek medical advice from your doctor.

The Jumper JPD-500E pulse oximeter is IPX 22 rated. It is not so waterproof and should be avoided to get contact with water. Anyhow, it is advisable to clean both your finger and the device with medical alcohol before use, for hygiene purpose, as well as to obtain better accuracy.

Regarding its accuracy, I find its SpO2 and Pulse Rate reading quite consistent with my Huawei Watch GT2.

Below is a photo of using both the devices together. You can see that the SpO2 reading is 1% different at that particular moment, as the reading will have a slight change of 1% from time to time during the measuring period. The Pulse Rate readings are both the same at 80 bpm.