sql UPDATES updates every column in the row that comes after the change column

Question

I'm using an UPDATE query to make changes to my database. The updates I make to a particular cell in a row get inserted into the database. What's happening though is any blank date spaces that come after that cell get entered as 0000-00-00.

For example, when I update the final review date this is what I should get

Before Update
Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03  

After Update
Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03        2015-08-05

Instead I get this:

Date Received   Final Review Date       Date Delivered         Date Accepted  
2015-03-03        2015-08-05              0000-00-00              0000-00-00

I've tried troubleshooting this but I'm fairly new to mysqli/php so I know I'm probably missing something simple. Any help resolving this would be appreciated.

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "oldga740_SeniorProject";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}

if (isset($_POST['update'])){
$UpdateQuery = "UPDATE Projects SET Project='$_POST[project]', Client='$_POST[client]', LastName='$_POST[lastname]', DateReceived='$_POST[datereceived]', FinalReviewDate='$_POST[finalreviewdate]', DateDelivered='$_POST[datedelivered]', DateAccepted='$_POST[dateaccepted]' WHERE Project='$_POST[hidden]'";
mysqli_query($conn, $UpdateQuery);
};

$sql = "SELECT * FROM Projects";
$result = $conn->query($sql);



echo "<table>

<tr>
<th>Project</th>
<th>Client</th>
<th>Last Name</th>
<th>Date Received</th>
<th>Final Review Date</th>
<th>Date Delivered</th>
<th>Date Accepted</th>
</tr>";

while($record = mysqli_fetch_array($result))
{
if ($result->num_rows > 0){

echo "<form action='mynewform.php' method='post'>";
echo "<tr>"; 
echo "<td>" . "<input type='text' name='project' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='text' name='client' value='" . $record['Client'] . "'/></td>";
echo "<td>" . "<input type='text' name='lastname' value='" . $record['LastName'] . "' /></td>";
echo "<td>" . "<input type='text' name='datereceived' value='" . $record['DateReceived'] . "' /></td>";
echo "<td>" . "<input type='text' name='finalreviewdate' value='" . $record['FinalReviewDate'] . "' /></td>";
echo "<td>" . "<input type='text' name='datedelivered' value='" . $record['DateDelivered'] . "' /></td>";
echo "<td>" . "<input type='text' name='dateaccepted' value='" . $record['DateAccepted'] . "' /></td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='submit' name='update' value='update' /></td>";
echo "<td>" . "<input type='submit' name='delete' value='delete' /></td>";
echo "</tr>";
echo "</form>";
}
}
echo "</table>";

?>



<?php
    $conn->close();
?>



</body>

</html>

Answer 1

Problem:

The updates I make to a particular cell in a row get inserted into the database. What's happening though is any blank date spaces that come after that cell get entered as 0000-00-00.

Solution:

From your comments,

The database already has those columns set to default as NULL. The only one that doesn't is the DateReceived column.

I'm assuming that the Project, Client, LastName and DateReceived have already been set. So the solution is to insert NULL values if the user doesn't specify any data to update, like this:

// your code

if (isset($_POST['update'])){
    $project = trim($_POST['project']);
    $client = trim($_POST['client']);
    $lastname = trim($_POST['lastname']);
    $datereceived = $_POST['datereceived'];

    $UpdateQuery = "UPDATE Projects SET Project='{$project}', Client='{$client}', LastName='{$lastname}', DateReceived='{$datereceived}'";

    if(empty($_POST['finalreviewdate'])){
        $UpdateQuery .= ", FinalReviewDate = NULL";
    }else{
        $UpdateQuery .= ", FinalReviewDate = '{$_POST['finalreviewdate']}'";
    }

    if(empty($_POST['datedelivered'])){
        $UpdateQuery .= ", DateDelivered = NULL";
    }else{
        $UpdateQuery .= ", DateDelivered = '{$_POST['datedelivered']}'";
    }

    if(empty($_POST['dateaccepted'])){
        $UpdateQuery .= ", DateAccepted = NULL";
    }else{
        $UpdateQuery .= ", DateAccepted = '{$_POST['dateaccepted']}'";
    }

    $UpdateQuery .= " WHERE Project='{$_POST['hidden']}'";

    mysqli_query($conn, $UpdateQuery);
};

// your code

Here's the complete code: (tested)

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "oldga740_SeniorProject";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}

if (isset($_POST['update'])){
    $project = trim($_POST['project']);
    $client = trim($_POST['client']);
    $lastname = trim($_POST['lastname']);
    $datereceived = $_POST['datereceived'];

    $UpdateQuery = "UPDATE Projects SET Project='{$project}', Client='{$client}', LastName='{$lastname}', DateReceived='{$datereceived}'";

    if(empty($_POST['finalreviewdate'])){
        $UpdateQuery .= ", FinalReviewDate = NULL";
    }else{
        $UpdateQuery .= ", FinalReviewDate = '{$_POST['finalreviewdate']}'";
    }

    if(empty($_POST['datedelivered'])){
        $UpdateQuery .= ", DateDelivered = NULL";
    }else{
        $UpdateQuery .= ", DateDelivered = '{$_POST['datedelivered']}'";
    }

    if(empty($_POST['dateaccepted'])){
        $UpdateQuery .= ", DateAccepted = NULL";
    }else{
        $UpdateQuery .= ", DateAccepted = '{$_POST['dateaccepted']}'";
    }

    $UpdateQuery .= " WHERE Project='{$_POST['hidden']}'";

    mysqli_query($conn, $UpdateQuery);
};

$sql = "SELECT * FROM Projects";
$result = $conn->query($sql);



echo "<table>

<tr>
<th>Project</th>
<th>Client</th>
<th>Last Name</th>
<th>Date Received</th>
<th>Final Review Date</th>
<th>Date Delivered</th>
<th>Date Accepted</th>
</tr>";

while($record = mysqli_fetch_array($result))
{
if ($result->num_rows > 0){

echo "<form action='process.php' method='post'>";
echo "<tr>"; 
echo "<td>" . "<input type='text' name='project' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='text' name='client' value='" . $record['Client'] . "'/></td>";
echo "<td>" . "<input type='text' name='lastname' value='" . $record['LastName'] . "' /></td>";
echo "<td>" . "<input type='text' name='datereceived' value='" . $record['DateReceived'] . "' /></td>";
echo "<td>" . "<input type='text' name='finalreviewdate' value='" . $record['FinalReviewDate'] . "' /></td>";
echo "<td>" . "<input type='text' name='datedelivered' value='" . $record['DateDelivered'] . "' /></td>";
echo "<td>" . "<input type='text' name='dateaccepted' value='" . $record['DateAccepted'] . "' /></td>";
echo "<td>" . "<input type='hidden' name='hidden' value='" . $record['Project'] . "' /></td>";
echo "<td>" . "<input type='submit' name='update' value='update' /></td>";
echo "<td>" . "<input type='submit' name='delete' value='delete' /></td>";
echo "</tr>";
echo "</form>";
}
}
echo "</table>";

?>



<?php
    $conn->close();
?>

</body>

</html>

Answer 2

This might be happening because while creating table you gave column type as DATE and inserting value as empty string for columns which doesn't have value. So while updating a value, other columns take value as 0000-00-00. DATE column cannot take empty string as default value. You can check default values based on data type here.

Best thing you can do is change columns to take default value as NULL and while inserting value to table first time give value as NULL for columns which doesn't have any value. So on update those column will not take value as 0000-00-00, they will have value as NULL.

For example consider table name is test and column names are date1, date2, date3 and date4

CREATE TABLE test (date1 DATE DEFAULT NULL, date2 DATE DEFAULT NULL, date3 DATE DEFAULT NULL, date4 DATE DEFAULT NULL)

You can also modify existing columns using below query

ALTER TABLE test MODIFY COLUMN date1 DATE DEFAULT NULL

and while inserting value first time when you have value only for first column, insert like this

INSERT INTO test VALUES ('2015-12-07', NULL, NULL, NULL)

So later when you update any other column, only that value will get updated and other columns will not take value as 0000-00-00. They will have value as NULL.

I tested this and it worked for me.

Answer 3

Change the data type for field which hold date. Then when you leave empty field while update then it will assign to '0000-00-00' if your database field is also empty else it will remain unchanged. Now to change database datatype ->oto "structure" tab on top ->find the row which we need to change data type and click on change action for that field. ->select data on type on drop down.

Answer 4

This is a small snippet from your code but is the portion responsible for entering the data. If the date fields are empty they get an assigned value of '' ( empty string ) which should hopefully prevent them being updated with 0000-00-00.

if( isset( $_POST['update'] ) ){

    /* For convenience, shorthand object notation for $_POST */
    $pd=(object)$_POST;

    /* Assign each parameter as a variable - using false or null as appropriate. There is some rudimentary filtering at least */
    $project            = isset( $pd->project ) && !empty( $pd->project ) ? strip_tags( filter_input( INPUT_POST, 'project', FILTER_SANITIZE_STRING ) ) : false;
    $client             = isset( $pd->client ) && !empty( $pd->client ) ? strip_tags( filter_input( INPUT_POST, 'client', FILTER_SANITIZE_STRING ) ) : false;
    $lastname           = isset( $pd->lastname ) && !empty( $pd->lastname ) ? strip_tags( filter_input( INPUT_POST, 'lastname', FILTER_SANITIZE_STRING ) ) : false;
    $datereceived       = isset( $pd->datereceived ) && !empty( $pd->datereceived ) ? strip_tags( filter_input( INPUT_POST, 'datereceived', FILTER_SANITIZE_STRING ) ) : false;
    $finalreviewdate    = isset( $pd->finalreviewdate ) && !empty( $pd->finalreviewdate ) ? strip_tags( filter_input( INPUT_POST, 'finalreviewdate', FILTER_SANITIZE_STRING ) ) : '';
    $datedelivered      = isset( $pd->datedelivered ) && !empty( $pd->datedelivered ) ? strip_tags( filter_input( INPUT_POST, 'datedelivered', FILTER_SANITIZE_STRING ) ) : '';
    $dateaccepted       = isset( $pd->dateaccepted ) && !empty( $pd->dateaccepted ) ? strip_tags( filter_input( INPUT_POST, 'dateaccepted', FILTER_SANITIZE_STRING ) ) : '';
    $hidden             = isset( $pd->hidden ) && !empty( $pd->hidden ) ? strip_tags( filter_input( INPUT_POST, 'hidden', FILTER_SANITIZE_STRING ) ) : false;

    /* The sql MUST have these to be processed */
    if( $project && $client && $hidden && $lastname && $datereceived ){

        /* The various date fields that were being updated with 0000-00-00 have a default value of empty string in the above vars */

        /* use the defined variables */
        $sql = "update `projects` set 
                `project`='{$project}', `client`='{$client}', `lastname`='{$lastname}', `datereceived`='{$datereceived}',
                `finalreviewdate`='{$finalreviewdate}', `datedelivered`='{$datedelivered}', `dateaccepted`='{$dateaccepted}' 
                where `project`='{$hidden}';";

        /* run the query */
        mysqli_query( $conn, $sql );
    }
};

Answer 5

If you dont get any data inputted into a datatime field it will default to 0000-00-00

If you want to change the default, you can in the tables settings.

or you can make sure that data will always enter into the database there by doing standard validation checks

To answer your question, check the tables and fields, check them for what type they are. I believe the ones that are blank are not datetime where as the ones that are by default setting to 0000-00-00 are set.

Just check and make sure they are all on the same setting.