Question 221413 in PHP Question posted in
Official manual can be found here.

Im new to PHP and i try to make a button for purging data on PHP older than 7 days

My code doesn’t work , i do some searching and try to wrap the code together

<?php 
$del = mysqli_query($con,"DELETE FROM tb_absen_mingguan WHERE tanggal=$_GET[tanggal] <= CURRENT_DATE - INTERVAL 7 DAY;");
if ($del) {
        echo " <script>
        alert('Data telah dihapus !');
        window.location='?page=rekap&act=mingguan';
        </script>"; 
}

    ?>

and got the error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your
SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near ” at line 1

what should i do ?

i try to change the tanggal and stuff but it doesnt work , and im pretty confused where the error at

Tags:

2

Answers


  1. 0

    No date needs to be passed to the query, the table’s tanggal date value can simply be the reference.

    Not sure what is trying to be achieved by that DELETE query in the WHERE clause, but what I believe what you are trying to do is:

    DELETE FROM tb_absen_mingguan WHERE tanggal < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

    … and on the plus side, this query does not have any SQL injection issues.

    Try this dbfiddle example

    The before/after example using the query looks like this:

    enter image description here

    Login or Signup to reply.
  2. 0

    Here’s the updated and safer version of the code:

    <?php
// Assuming you have established the database connection in $con

if (isset($_GET['tanggal'])) {
    // Sanitize the input to prevent SQL injection
    $tanggal = mysqli_real_escape_string($con, $_GET['tanggal']);

    // Construct the SQL query with proper date comparison
    $sql = "DELETE FROM tb_absen_mingguan WHERE tanggal <= (CURRENT_DATE - INTERVAL 7 DAY) AND tanggal = '$tanggal';";

    $del = mysqli_query($con, $sql);

    if ($del) {
        echo "<script>
            alert('Data telah dihapus!');
            window.location='?page=rekap&act=mingguan';
            </script>";
    } else {
        echo "<script>
            alert('Failed to delete data. Error: " . mysqli_error($con) . "');
            window.location='?page=rekap&act=mingguan';
            </script>";
    }
}
?>

    In the updated code, I’ve made the following changes:

    1. Sanitized the $_GET['tanggal'] input using mysqli_real_escape_string to prevent SQL injection attacks.

    2. Changed the SQL query to use proper date comparison. We need to specify both conditions in the WHERE clause: tanggal <= (CURRENT_DATE - INTERVAL 7 DAY) to ensure that the "tanggal" is older than 7 days from the current date and tanggal = '$tanggal' to ensure that we are deleting records with a specific date.

    3. Added an error handling message in case the deletion operation fails.

    Always ensure you sanitize and validate user inputs before using them in SQL queries to prevent potential security vulnerabilities. Additionally, consider using prepared statements with parameter binding instead of manually escaping user inputs to further enhance security.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search