Normally, you add regular values to mySQL, from PHP like this:
function addValues[$val1, $val2] {
db_open[]; // just some code ot open the DB
$query = "INSERT INTO uradmonitor [db_value1, db_value2] VALUES ['$val1', '$val2']";
$result = mysql_query[$query];
db_close[]; // just some code to close the DB
}
When your values are empty/null [$val1=="" or $val1==NULL], and you want NULL to be added to SQL and not 0 or empty string, to the following:
function addValues[$val1, $val2] {
db_open[]; // just some code ot open the DB
$query = "INSERT INTO uradmonitor [db_value1, db_value2] VALUES [".
[[$val1=='']?"NULL":["'".$val1."'"]] . ", ".
[[$val2=='']?"NULL":["'".$val2."'"]] .
"]";
$result = mysql_query[$query];
db_close[]; // just some code to close the DB
}
Note that null must be added as "NULL" and not as "'NULL'" . The non-null values must be added as "'".$val1."'", etc.
Hope this helps, I just had to use this for some hardware data loggers, some of them collecting temperature and radiation, others only radiation. For those without the temperature sensor I needed NULL and not 0, for obvious reasons [ 0 is an accepted temperature value also].
How to Replace an Empty String With NULL in MySQL Insert Query?
Learn how to replace an empty string with NULL in MySQL/MariaDB
- 04 Sep, 2020
- 2 min read
In this aricle we are going to see how we can insert NULL
values in place of an empty string in MySQL/MariaDB. For the examples in this article, let's assume that:
- We have a
user
table with three columns;id
,name
andjob_title
; - We have a variable
$jobTitle
[in any programming language] that can potentially have an empty string as value.
We'll be using the following build schema to build our table:
CREATE TABLE `user` [ id INT NOT NULL AUTO_INCREMENT, name VARCHAR[25] NOT NULL, job_title VARCHAR[25], PRIMARY KEY [id] ];
Using the NULLIF[]
Function
The NULLIF[]
function returns NULL
if two expressions [for e.g. expr1
and expr2
] are equal in comparison. If the two expressions are not equal, the first expression is returned. It has the following syntax:
# Return NULL if expr1 = expr2 NULLIF[expr1, expr2]
For example to insert NULL
instead of an empty string
using NULLIF[]
, we could do the following:
INSERT INTO `user` [`name`, `job_title`] VALUES ['john', NULLIF['$jobTitle', '']];
This would insert NULL
in the job_title
column when the value of the variable "$jobTitle
" matches an empty string. And, when the two expressions don't match, the value of the $jobTitle
variable will be inserted.
Using the IF[]
Function
The IF[]
function returns the first expression if the condition is true
and the second expression if the condition is false
. It has the following syntax:
IF[condition, expr1, expr2]
We can simply have a condition to check if the value coming from a variable [e.g. "$jobTitle
"] is an empty string or not, and accordingly return NULL
if the condition is true
, or the string itself otherwise. For example:
INSERT INTO `user` [`name`, `job_title`] VALUES ['john', IF['$jobTitle' = '', NULL, '$jobTitle']];
Using the CASE
Operator
Using CASE
could be another option. However, it may not be the best choice when you only have one condition. The syntax for it is as follows:
CASE WHEN condition1 THEN result1 # ... WHEN conditionN THEN resultN ELSE result END;
The following example shows how, using CASE
, we can
insert NULL
when "$jobTitle
" is empty, and insert the value of the variable itself otherwise:
INSERT INTO `user` [`name`, `job_title`] VALUES [ 'john', [ CASE WHEN '$jobTitle' = '' THEN NULL ELSE '$jobTitle' END ] ];
Hope you found this post useful. It was published 04 Sep, 2020. Please show your love and support by sharing this post.
- Web Development
- Backend
- MySQL
- MariaDB
- SQL
- Database
Inserting NULL values
The SQL INSERT statement can also be used to insert NULL value for a column.
Example:
Sample table: agents
To add values'A001','Jodi','London','.12','NULL' for a single row into the table 'agents' then, the following SQL statement can be used:
SQL Code:
INSERT INTO agents
VALUES ["A001","Jodi","London",.12,NULL];
SQL insert values in specific columns
The SQL INSERT INTO statement can also be used to insert one or more specific columns for a row. It is required to mention the column[s] name in the SQL query.
Example:
Sample table: agents
To add values'A001','Jodi', and ', 12' against the columns 'agent_code', 'agent_name' and 'commission' into the table 'agents', the following SQL statement can be used:
SQL Code:
INSERT INTO agents
[agent_code,agent_name,commission]
VALUES ["A001","Jodi",.12]
See our Model Database
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL JOINS [29 Exercises]
- SQL JOINS on HR Database [27 Exercises]
- SQL SUBQUERIES
- SQL SUBQUERIES [39 Exercises]
- SQL SUBQUERIES on HR Database [55 Exercises]
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- JOINS queries on soccer Database [61 Exercises]
- Hospital Database
- Introduction
- BASIC, SUBQUERIES, and JOINS [39 Exercises]
- Employee Database
- BASIC queries on employee Database [115 Exercises]
- SUBQUERIES on employee Database [77 Exercises]
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Insert
statement
Next: Inserting the result of a query in another table
SQL: Tips of the Day
Select random rows:
SELECT * FROM [ SELECT DISTINCT 1 + trunc[random[] * 5100000]::integer AS id FROM generate_series[1, 1100] g ] r JOIN big USING [id] LIMIT 1000;
Database: PostgreSQL
Ref: //bit.ly/3SwH0rN