How to submit only non empty var to sql database from PHP?

Question

I'm a beginner. I have an update field where I have only 2 fields as mandatory. But when i update all other fields are updated as blank in sql database. Is there a way to only submit non empty fields in SQL or do i have to verify each field and write a if(!empty()) case and different sql statement for every field?

This is my query after correcting

<?php
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "testingtimelines");


$connect = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);
if(isset($_POST['submit'])){

$TA = $_POST['tharea'];
$Brand = $_POST['brand'];
$JobType = $_POST['jobtype'];
$DevLead = $_POST['devlead'];
$OnsiteDPM = $_POST['OnsiteDPM'];
$ISDAY0 = $_POST['isday0'];
$VeevaNo  = $_POST['VeevaNo'];
$SiteName = $_POST['sitename'];
$SiteType = $_POST['sitetype'];
$AuthorURL = $_POST['authorurl'];
$BFWURL = $_POST['bfwurl'];
$Target = $_POST['target'];
$Complexity = $_POST['complexity'];
$Developer = $_POST['Developer'];
$PlannedStartDate = $_POST['plandate'];
$PlannedActivity = $_POST['plannedactivity'];
$TR = $_POST['ingredients'];
$TestingResource = implode(", ",$TR);
$ActualStartDate = $_POST['actualdate'];
$ActivityStatus = $_POST['activitystatus'];
$ActualClosureDate = $_POST['closedate'];
$Comments = $_POST['Comments'];
$PlannedEndDate = $_POST['enddate'];
$sql = $connect->prepare("UPDATE  `testingdashboard` SET `TA`=IF(`TA`='',?,`TA`),
            `Brand`=IF(`Brand`='',?,`Brand`),
            `JobType`=IF(`JobType`='',?,`JobType`),
            `DevLead`=IF(`DevLead`='',?,`DevLead`),
            `OnsiteDPM`=IF(`OnsiteDPM`='',?,`OnsiteDPM`),
            `ISDAY0`=IF(`ISDAY0`='',?,`ISDAY0`),
            `SiteName`=IF(`SiteName`='',?,`SiteName`),
            `SiteType`=IF(`SiteType`='',?,`SiteType`),
            `AuthorURL`=IF(`AuthorURL`='',?,`AuthorURL`),
            `BFWURL`=IF(`BFWURL`='',?,`BFWURL`),
            `Target`=IF(`Target`='',?,`Target`),
            `Complexity`=IF(`Complexity`='',?,`Complexity`),
            `Developer`=IF(`Developer`='',?,`Developer`),
            `PlannedStartDate`=IF(`PlannedStartDate`='',?,`PlannedStartDate`),
            `TestingResource`=IF(`TestingResource`='',?,`TestingResource`),
            `ActualStartDate`=IF(`ActualStartDate`='',?,`ActualStartDate`),
            `ActivityStatus`=IF(`ActivityStatus`='',?,`ActivityStatus`),
            `ActualClosureDate`=IF(`ActualClosureDate`='',?,`ActualClosureDate`),
            `Comments`=IF(`Comments`='',?,`Comments`),
            `PlannedEndDate`=IF(`PlannedEndDate`='',?,`PlannedEndDate`),
            WHERE `VeevaNo`='$VeevaNo' AND `PlannedActivity`='$PlannedActivity'");
            $sql->bind_param("ssssssssssssssssssss", $TA, $Brand, $JobType, $DevLead, $OnsiteDPM, $ISDAY0, $SiteName, $SiteType, $AuthorURL, $BFWURL, $Target, $Complexity, $Developer, $PlannedStartDate, $TestingResource, $ActualStartDate, $ActivityStatus, $ActualClosureDate, $Comments, $PlannedEndDate);


$sql->execute();
            if(mysqli_query($connect,$sql)){
            //header("localhost/testing/list.php");
            echo "success";
            $sql->close();
            header('location:new.php');
            }
?>

But now am getting "Undefined index" error and "Fatal error: Uncaught Error: Call to a member function bind_param()"

Thank You

Are you adding all new data to your db table or are you really updating old data? — S4NDM4N, Sep 27 '17 at 08:38
updating old data. for example i've already filled all the mandatory fields and lest the remaining blank and now i'm trying to update those remaining fields — Andruraj, Sep 27 '17 at 09:40

Script47 · Answer 1 · 2017-09-27T08:47:47.473

This seems more like a design issue, assuming the user is filling in a form you can retrieve the row values from the database and have the form pre-filled, then when they submit the values you'd just be overwriting the existing data rather than having a long winded SQL statement check if values are empty.

Secondly, always validate and sanitize you user input, never trust it. Not only will this prevent errors (Undefined index[...]) but it will also prevent those with malicious intent to attack your site.

Thirdly, seeing as you are using mysqli_*, why don't you just make use of prepared statements to prevent SQL Injection.

Note: Your constructor for mysqli_* is in the OOP form but you are using procedural functions further on. For the sake of consistency, please choose procedural or OO.

Reading Material

How can I prevent SQL injection in PHP?

score 0 · Answer 2 · answered Sep 27 '17 at 08:30

0

You can check it via empty() in PHP as you said or try to take advantage of SQL and use:

UPDATE mytable t
   SET t.col = IF(foo='',t.col,foo) 
WHERE ...

EDIT: Also I would suggest to use PDO prepared statements. Because now you are vulnerable to SQLi injection.

answered Sep 27 '17 at 08:30

Vastlik

120
1
9

`mysqli_*` has prepared statements too. – Script47 Sep 27 '17 at 08:36
You have to check it in parameter if that variable is empty `\`JobType\`=IF(?='',?,\`JobType\`)` – Vastlik Sep 27 '17 at 13:26

How to submit only non empty var to sql database from PHP?

2 Answers2