Simple html form using php and mysql. How do l maintain NULL values for empty fields in the database

Question

I have built a simple html form that has input boxs and text areas, which are then inserted into a mysql database with the help of php script. Not all the fields have to be filling in and l want to be able to search the database for NULL fields. My problem is that when the form submits, even entries that have no information are having something inserted like an empty space which means l then can't search the DB for NULL fields as returns everything.

This is the php script that sends the MYSQL insert query to the DB

<?php
/*Av Tech Name*/
$techNam = $_POST['techNam'];
/* Building */
$building = $_POST['building'];
/* Room */ 
$roomNum = $_POST['roomNum'];
/* Day */
$day = $_POST['day'];
/*Month*/
$month = $_POST['month'];
/*Projector Power */
$projPower = $_POST['projPower'];
/* Screen decends and retracts */
$screenUpDown = $_POST['screenUpDown'];
/* Power to buton control */
$controlPower = $_POST['controlPower'];
/* Lamp working */
$projLamp = $_POST['projLamp'];
/* Rips and tears to the screen */
$rips = $_POST['rips'];
/* Control Functions */
$controlFunctions = $_POST['controlFunctions'];
/* Projector image */
$projImage = $_POST['projImage'];
/* Screen Notes */
$screenNotes = $_POST['screenNotes'];
/* Control Notes */
$controlNotes = $_POST['controlNotes'];
/* Monitor power */
$monitorPower = $_POST['monitorPower'];
/* Network connection */
$networkConnect = $_POST['networkConnect'];
/* PC Power */
$pcPower = $_POST['pcPower'];
/* PC image */
$pcImage = $_POST['pcImage'];
/* PC audio */
$pcAudio = $_POST['pcAudio'];
/* PC DVD playback */
$pcDvd = $_POST['pcDvd'];
/* BluRay Player power */
$BluRayPower = $_POST['BluRayPower'];
/* Laptop VGA connection */
$laptopVga = $_POST['laptopVga'];
/* Visualiser Power */
$visPower = $_POST['visPower'];
/* bluRay Play back */
$BluRayPlay = $_POST['BluRayPlay'];
/* Laptop audio */
$laptopAudio = $_POST['laptopAudio'];
/* Visualiaser lamp */
$visLamp = $_POST['visLamp'];
/* BluRay Audio */
$BluRayAudio = $_POST['BluRayAudio'];
/* laptop HDMI */
$laptopHdmi = $_POST['laptopHdmi'];
/* Visualiser image */
$visImage = $_POST['visImage'];
/* Laptop Network Connection */
$laptopNetwork = $_POST['laptopNetwork'];
/* Visualiser automatic and manual zoom */
$visZoom = $_POST['visZoom'];
/* Lectern mic working */
$micLec = $_POST['micLec'];
/* Video conference power */
$vcPower = $_POST['vcPower'];
/* Condition of lectern Condition */
$lecternCondition = $_POST['lecternCondition'];
/* Radio mics working */
$audioRadioMics = $_POST['audioRadioMics'];
/* VC unit dials and connects */
$vcConnect = $_POST['vcConnect'];
/* General condition of cables Good */
$cableCondition = $_POST['cableCondition'];
/* Visualiser audio */
$vcAudio = $_POST['vcAudio'];
/* Condition of wall connections */
$wall = $_POST['wall'];
/* Visualiser laptop connection */
$vcLaptop = $_POST['vcLaptop'];
/* White board condition */
$whiteB = $_POST['whiteB'];
/* Video conference notes */
$vcNotes = $_POST['vcNotes'];

$sql = "INSERT INTO avChecklist (techNam, building, roomNum, day, month, 
projPower, screenUpDown, controlPower, projLamp, rips, controlFunctions, 
projImage, screenNotes, controlNotes, monitorPower, networkConnect, 
pcPower, pcImage, pcAudio, pcDvd, BluRayPower, laptopVga, visPower, 
BluRayPlay, laptopAudio, visLamp, BluRayAudio, laptopHdmi, visImage, 
laptopNetwork, visZoom, micLec, vcPower, lecternCondition, 
audioRadioMics, vcConnect, cableCondition, vcAudio, wall, vcLaptop, 
whiteB, vcNotes) VALUES ('$techNam', '$building', '$roomNum', '$day', 
'$month', '$projPower', '$screenUpDown', '$controlPower', '$projLamp', 
'$rips', '$controlFunctions', '$projImage', '$screenNotes', 
'$controlNotes', '$monitorPower', '$networkConnect', '$pcPower', 
'$pcImage', '$pcAudio', '$pcDvd', '$BluRayPower', '$laptopVga', 
'$visPower', '$BluRayPlay', '$laptopAudio', '$visLamp', '$BluRayAudio', 
'$laptopHdmi', '$visImage', '$laptopNetwork', '$visZoom', '$micLec', 
'$vcPower', '$lecternCondition', '$audioRadioMics', '$vcConnect', 
'$cableCondition', '$vcAudio', '$wall', '$vcLaptop', '$whiteB', 
'$vcNotes')";

 if (!mysql_query($sql)) {
 die('Error: ' . mysql_error());
 }
echo 'Thanks '. $techNam .' your completed maintenance check for room '. 
$roomNum .'  has been added to the database.';
echo "<br /><br /><br />";
echo 'This page will redirect in 5 seconds';
mysql_close();
?>

Table Structure

Database a9404870_heAv
Table structure for table avChecklist
Field   Type    Null    Default
submitDate  timestamp   Yes CURRENT_TIMESTAMP
techNam varchar(255)    Yes NULL
building    varchar(255)    Yes NULL
roomNum varchar(255)    Yes NULL
day varchar(255)    Yes NULL
month   varchar(255)    Yes NULL
projPower   varchar(255)    Yes NULL
screenUpDown    varchar(255)    Yes NULL
controlPower    varchar(255)    Yes NULL
projLamp    varchar(255)    Yes NULL
rips    varchar(255)    Yes NULL
controlFunctions    varchar(255)    Yes NULL
projImage   varchar(255)    Yes NULL
screenNotes varchar(255)    Yes NULL
controlNotes    varchar(255)    Yes NULL
monitorPower    varchar(255)    Yes NULL
networkConnect  varchar(255)    Yes NULL
pcPower varchar(255)    Yes NULL
pcImage varchar(255)    Yes NULL
pcAudio varchar(255)    Yes NULL
pcDvd   varchar(255)    Yes NULL
BluRayPower varchar(255)    Yes NULL
laptopVga   varchar(255)    Yes NULL
visPower    varchar(255)    Yes NULL
BluRayPlay  varchar(255)    Yes NULL
laptopAudio varchar(255)    Yes NULL
visLamp varchar(255)    Yes NULL
BluRayAudio varchar(255)    Yes NULL
laptopHdmi  varchar(255)    Yes NULL
visImage    varchar(255)    Yes NULL
laptopNetwork   varchar(255)    Yes NULL
visZoom varchar(255)    Yes NULL
micLec  varchar(255)    Yes NULL
vcPower varchar(255)    Yes NULL
lecternCondition    varchar(255)    Yes NULL
audioRadioMics  varchar(255)    Yes NULL
vcConnect   varchar(255)    Yes NULL
cableCondition  varchar(255)    Yes NULL
vcAudio varchar(255)    Yes NULL
wall    varchar(255)    Yes NULL
vcLaptop    varchar(255)    Yes NULL
whiteB  varchar(255)    Yes NULL
vcNotes varchar(255)    Yes NULL

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Feb 05 '16 at 18:43
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 05 '16 at 18:43

Jay Blanchard · Answer 1 · 2016-02-05T18:50:11.570

1

In order to make sure something is set to NULL you have to do something like this:

isset($_POST['screenNotes'])? $screenNotes = $_POST['screenNotes'] : $screenNotes = NULL;

You have to do this for every field that you wish to insert NULL's if the field is left blank.

What I have done here is use a ternary condition - it is just a shortened if condition:

<if this is true> ? <do this> : <else do that>;

For more on ternary operatores see the docs.

edited Feb 05 '16 at 18:50

answered Feb 05 '16 at 18:45

Jay Blanchard

34,243
16
77
119

Jay many thanks for your advice. I have changed my script accordingly, but it is still adding empty field into the database. any ideas why this might be happening. – stuart Wroe Feb 08 '16 at 11:34
Can you post your table creation query @stuartWroe? You may have default field settings. – Jay Blanchard Feb 08 '16 at 12:44
I created the table manually through phpmyadmin so im not sure how l can show you the structure, but here is an example of an entry that all follow the same attributes except for the first one which is a timestamp. Field(techNam) Type(varChar(255)) Collation(latin1_general_ci) Null(NULL) Default (NULL). Thanks – stuart Wroe Feb 09 '16 at 14:00
You can typically [export your database setup through phpMyAdmin](http://superuser.com/questions/168595/how-to-export-mysql-schema-from-phpmyadmin). – Jay Blanchard Feb 09 '16 at 14:06
Table structure for table avChecklist Field Type Null Default submitDate timestamp Yes CURRENT_TIMESTAMP techNam varchar(255) Yes NULL building varchar(255) Yes NULL roomNum varchar(255) Yes NULL day varchar(255) Yes NULL month varchar(255) Yes NULL projPower varchar(255) Yes NULL screenUpDown varchar(255) Yes NULL controlPower varchar(255) Yes NULL projLamp varchar(255) Yes NULL rips varchar(255) Yes NULL controlFunctions varchar(255) Yes NULL projImage varchar(255) Yes NULL screenNotes varchar(255) Yes NULL controlNotes varchar(255) Yes NULL – stuart Wroe Feb 11 '16 at 07:25
monitorPower varchar(255) Yes NULL networkConnect varchar(255) Yes NULL pcPower varchar(255) Yes NULL pcImage varchar(255) Yes NULL pcAudio varchar(255) Yes NULL pcDvd varchar(255) Yes NULL BluRayPower varchar(255) Yes NULL laptopVga varchar(255) Yes NULL visPower varchar(255) Yes NULL BluRayPlay varchar(255) Yes NULL laptopAudio varchar(255) Yes NULL visLamp varchar(255) Yes NULL BluRayAudio varchar(255) Yes NULL laptopHdmi varchar(255) Yes NULL visImage varchar(255) Yes NULL laptopNetwork varchar(255) Yes NULL visZoom varchar(255) Yes NULL micLec varchar(255) Yes NULL – stuart Wroe Feb 11 '16 at 07:25
vcPower varchar(255) Yes NULL lecternCondition varchar(255) Yes NULL audioRadioMics varchar(255) Yes NULL vcConnect varchar(255) Yes NULL cableCondition varchar(255) Yes NULL vcAudio varchar(255) Yes NULL wall varchar(255) Yes NULL vcLaptop varchar(255) Yes NULL whiteB varchar(255) Yes NULL vcNotes varchar(255) Yes NULL – stuart Wroe Feb 11 '16 at 07:25
1

Please do not dump code in comments. Edit your OP to add the new information. – Jay Blanchard Feb 11 '16 at 12:43
Ok sorry about that l have added the table structure to the bottom of the original question. Many thanks for your help – stuart Wroe Feb 12 '16 at 13:17
@stuartWroe how do you know that an empty field is being added? It is possible that you have set it so that NULL values do not show - leading you to believe there are blanks. – Jay Blanchard Feb 12 '16 at 13:21

Simple html form using php and mysql. How do l maintain NULL values for empty fields in the database

1 Answers1