John Davidson

Message:

I'm trying to create something like:

• When you make a POST request to the API, insert those values into the MYSQL database


• Delete them if they longer exist in the API (since the API only returns active values)

Inserting works, but deleting doesn't work quite as good.

Right now I think it's looping through the request and deleting every ID that isn't a match EACH time. So it ends up deleting everything by the end of the loop.

How do I fix this?

<?php

include 'import.php'; // Import from API

include 'connect.php'; // Connect to DB





// Add and Update on duplicate

foreach ($query as $val) {

    $fruitId = $val['fruitId'];



    $sql = "INSERT INTO basket (fruitId) 

        VALUES ('$fruitId') 

        ON DUPLICATE KEY UPDATE 

        fruitId = '$fruitId'";





    if ($conn->query($sql) === TRUE) {

        echo "ID has been added";

    } else {

        echo "Error updating ID";

    }   

}



// Delete

foreach ($query as $val) {



    $fruitId = $val['fruitId'];



    $sql = "DELETE FROM basket 

            WHERE fruitId NOT IN ('$fruitId')";



    if ($conn->query($sql) === TRUE) {

        echo "ID has been deleted";

    } else {

        echo "Error updating ID";

    }   

}

?>

Not sure if there's a better/more concise way to achieve this.

---

My Answer:
One way to fix this issue is to first fetch all the IDs from the API and store them in an array. Then, fetch all the IDs from the database and store them in another array. Finally, compare the two arrays and delete any rows from the database that do not exist in the API.

Here's an example code snippet to achieve this:

php
// Fetch IDs from the API
$api_ids = [1, 2, 3, 4, 5]; // Replace this with actual API call to fetch IDs

// Fetch IDs from the database
$db_ids = [];
$result = mysqli_query($conn, "SELECT id FROM your_table");
while ($row = mysqli_fetch_assoc($result)) {
$db_ids[] = $row['id'];
}

// Compare the two arrays and delete rows that do not exist in the API
$delete_ids = array_diff($db_ids, $api_ids);
if (!empty($delete_ids)) {
$delete_ids_str = implode(',', $delete_ids);
mysqli_query($conn, "DELETE FROM your_table WHERE id IN ($delete_ids_str)");
}


In this code snippet, replace `$conn` with your database connection object and `your_table` with the actual table name in your database. This code fetches IDs from the API and database, compares them, and deletes any rows from the database that do not exist in the API.