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>

What is the value of $_POST[datedelivered] and $_POST[dateaccepted]? — momouu, Dec 09 '15 at 15:04
I did already, but the insert returns an error sayng the record can't be added. It has to do with how the form is set up. The initial values for the oher columns are entered via the form. — Tony, Dec 09 '15 at 21:50
Sorry, I misread your answer. The INSERT query only has the fields project, client, last name and date received to load the project into the database. — Tony, Dec 10 '15 at 00:15
@Tony That's perfect. I assumed the same. Did you test your application with the code I suggested? It should work fine. — Rajdeep Paul, Dec 10 '15 at 00:43
I did, thanks. It worked great. I tried a similar approach but I had the multiple if/else loops set up wrong. After two days of trying to figure it out I finally decided to post the bounty for some extra help. — Tony, Dec 10 '15 at 00:50

Rajdeep Paul · Accepted Answer · 2015-12-10T00:52:11.583

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>

Your code works perfectly. I tried something along these lines but I didn't have it formatted correctly and kept hitting errors. I appreciate all of your help with this. — Tony, Dec 10 '15 at 00:45

score 5 · Answer 2 · answered Dec 07 '15 at 14:05

5

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.

answered Dec 07 '15 at 14:05

Tarun Jain

53
4

The database already has those columns set to default as NULL. The only one that doesn't is the datereceived column. – Tony Dec 07 '15 at 14:28
@Tony than while inserting value first time to row, insert value as NULL for columns which doesn't have value. Then it will not take 0000-00-00 on update. – Tarun Jain Dec 08 '15 at 06:56
The initial entry is via a form that only has the first four fields, Project, Client, Date Received and Last Name. Are you suggesting I add the extra columns to my INSERT form yet set their values to NULL? – Tony Dec 08 '15 at 20:09
Yes but rather than inserting in form you can just add in insert query, no need to add in form. While inserting value to database table if there is value only for these fields **Project**, **Client**, **Last Name**, **Date Received** then in insert query along with inserting value for these columns also insert value for columns **Final Review Date**, **Date Delivered**, **Date Accepted** as **NULL**. So next time when you update any other column they will not get updated as 0000-00-00. Try that once. – Tarun Jain Dec 09 '15 at 06:28
The values for the other fields are being set in the form though. How do I set those other date values to NULL if they're not being inserted via the form? Just adding them to the INSERT query returns a result "record can not be entered. – Tony Dec 09 '15 at 16:41
While preparing query from form data you can check if value for that field is empty and if it is empty set it to NULL and insert same value in database. – Tarun Jain Dec 10 '15 at 08:11

score 5 · Answer 3 · answered Dec 08 '15 at 04:14

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.

score 2 · Answer 4 · answered Dec 02 '15 at 17:32

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 );
    }
};

I updated my code to try your suggestions but I'm receiving this error: Undefined variable: Project in on line 68. Any ideas? — Tony, Dec 03 '15 at 17:17

score 1 · Answer 5 · answered Dec 02 '15 at 17:10

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.

The database fields are all set up to be date fields instead of date time fields. I have validation on my INSERT queries that error out if something isn't formatted properly. I didn't add any to this table yet because I was more focused on getting it to work first. — Tony, Dec 02 '15 at 17:20

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

5 Answers5