John Davidson

php - Date is being sent as 0000-00-00 and returned as 11/30/-0001 when I use SQL update statement

0 comments
Message:


For some reason when I edit the the date it's being sent as 0000-00-00 to the database. The wrest of the update is working as expected. I have the db rows for the dates set on type: date, default: none, and null: no.


Also tried to remove the values for the date inputs, that didn't do anything either. Please help, thank you!




<?php 
include 'db.php';
$WAGE = 14;
$TAX = 0.01;
$SS = 0.062;
$MEDI = 0.0145;
$GOAL = 0.75;

if($_SERVER['REQUEST_METHOD'] === "POST") {
// Variables
$id = htmlspecialchars($_POST['id']);
$begDate = htmlspecialchars($_POST['date1']);
$endDate = htmlspecialchars($_POST['date2']);
$hours = htmlspecialchars($_POST['hours']);
$total = number_format($hours * $WAGE, 2, '.', '');
$taxDeducted = number_format($total * $TAX, 2,'.', '');
$SSDeducted = number_format($total * $SS, '2', '.', '');
$MEDIDeducted = number_format($total * $MEDI, '2', '.', '');
$deducted = number_format($taxDeducted + $MEDIDeducted + $SSDeducted, 2, '.', '');
$total -= number_format($deducted, 2, '.', '');
$save = number_format($GOAL * $total, 2, '.', '');
$date = $_POST['date1'];
// SQL
$sql = "UPDATE hours SET begDate=$date, endDate=$endDate, hours=$hours, deducted=$deducted, save=$save, total=$total WHERE id=$id;";

// EXECUTE
$stmt = $conn->prepare($sql);
$stmt->execute();
}

?>

<?php 
include 'header.php';
include 'db.php';
if(isset($_GET['id'])) :
$id = $_GET['id'];
$sql = "SELECT * FROM hours WHERE id=$id;";
$stmt = $conn->prepare($sql);

$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $res) :
$begDate = $res['begDate'];
$endDate = $res['endDate'];
$hours = $res['hours'];
endforeach;
?>
<div class="container form bg-light p-5">
<h1 class="text-center my-5">Add a week</h1>
<form action="editForm.php" method="post">
<input type="hidden" name="id" value="<?= $id; ?>" id="id">
<div class="row">
<div class="form-group col-md-5">
<input type="date" name="date1" id="date1" value="<?=$begDate;?>" class="form-control" required>
</div>
<div class="col-md-2 text-center">
<h3>TO</h3>
</div>
<div class="col-md-5 form-group">
<input type="date" name="date2" id="date2" value="<?=$endDate;?>" class="form-control" required>
</div>
</div>

<div class="row">
<div class="col-md-3 form-group mx-auto mt-5">
<input class="form-control" value="<?= $hours;?>" type="float" name="hours" id="hours" placeholder="Hours this period" required>
</div>
</div>

<div class="row text-center mt-5">
<div class="form-group col-md-3 mx-auto">
<input type="submit" value="SUBMIT" name="submit" class="btn btn-secondary">
</div>
</div>
</form>
</div>
<?php endif; ?>

<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-ygbV9kiqUc6oa4msXn9868pTtWMgiQaeYH7/t7LECLbyPA2x65Kgf80OJFdroafW" crossorigin="anonymous"></script>
<script>
$('form').on('submit', function(e) {
e.preventDefault();
$.ajax({
url: 'editForm.php',
method: 'post',
data: $('form').serialize()
})
.done(function(res, status) {
if(status == 'success') {
window.location.href = 'index.php';
}
})
.fail(function(res, status) {
if(status == 'error') {
console.log('error');
}
});
})

</script>
<?php include 'footer.php'; ?>


My Answer:
It sounds like there may be an issue with how the date is being formatted or handled in your PHP code before it is sent to the database.

One thing you can try is to explicitly set the date format before sending it to the database. You can use the `date()` function in PHP to format the date in the desired format before inserting it into the database.

For example, you can do something like this:

php
$date = $_POST['date']; // Assuming date is coming from a form input

// Format the date in the desired format
$formattedDate = date('Y-m-d', strtotime($date));

// Use the formatted date in your SQL update statement
$sql = "UPDATE your_table SET date_column = '$formattedDate' WHERE id = $id";

// Execute the SQL statement
// Make sure to sanitize and validate user input to prevent SQL injection


By formatting the date in the 'Y-m-d' format before inserting it into the database, you can ensure that it is stored correctly as a date value.

Additionally, make sure that the date input field in your form is correctly formatted and that it is being passed to the PHP script in the correct format.

If you continue to experience issues, you may want to check the data type of the date column in your database table to ensure it is set to accept date values.

I hope this helps! Let me know if you have any other questions.

Rate this post

3 of 5 based on 6718 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us