Delete data present in db and not present in current for loop

up vote
0
down vote

favorite

foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id=$value5;

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}

I'm inserting some ids to the database. If the id already exists in the table the status will be updated else it will be created.

I need to delete the ids which are already present in the table and not present in the current FOR loop.

Which is the best way to delete the ids?

Thanks in advance

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

This will likely be easier to handle at the application level. Get all of the IDs in the database, then remove from that new set any IDs in your $data variable. From there, delete that set from the database. Logically this isn't an ideal design imo, as you scale to larger amounts of records this will get slower.
– Rogue
Nov 21 at 14:37

1

In that case would it not be easier to just empty the table and then add all the new ones that are in this foreach loop
– RiggsFolly
Nov 21 at 14:38

Why is $id only set when $key5 === 'ID'?
– Nigel Ren
Nov 21 at 14:39

add a comment |

up vote
0
down vote

favorite

foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id=$value5;

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}

I'm inserting some ids to the database. If the id already exists in the table the status will be updated else it will be created.

I need to delete the ids which are already present in the table and not present in the current FOR loop.

Which is the best way to delete the ids?

Thanks in advance

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

This will likely be easier to handle at the application level. Get all of the IDs in the database, then remove from that new set any IDs in your $data variable. From there, delete that set from the database. Logically this isn't an ideal design imo, as you scale to larger amounts of records this will get slower.
– Rogue
Nov 21 at 14:37

1

In that case would it not be easier to just empty the table and then add all the new ones that are in this foreach loop
– RiggsFolly
Nov 21 at 14:38

Why is $id only set when $key5 === 'ID'?
– Nigel Ren
Nov 21 at 14:39

add a comment |

up vote
0
down vote

favorite

foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id=$value5;

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}

I'm inserting some ids to the database. If the id already exists in the table the status will be updated else it will be created.

I need to delete the ids which are already present in the table and not present in the current FOR loop.

Which is the best way to delete the ids?

Thanks in advance

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id=$value5;

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}

I'm inserting some ids to the database. If the id already exists in the table the status will be updated else it will be created.

I need to delete the ids which are already present in the table and not present in the current FOR loop.

Which is the best way to delete the ids?

Thanks in advance

php mysql sql-delete

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

edited Nov 21 at 14:37

David.J

517521

edited Nov 21 at 14:37

David.J

517521

edited Nov 21 at 14:37

David.J

517521

asked Nov 21 at 14:32

user8429002

235

asked Nov 21 at 14:32

user8429002

235

asked Nov 21 at 14:32

user8429002

235

This will likely be easier to handle at the application level. Get all of the IDs in the database, then remove from that new set any IDs in your $data variable. From there, delete that set from the database. Logically this isn't an ideal design imo, as you scale to larger amounts of records this will get slower.
– Rogue
Nov 21 at 14:37

1

In that case would it not be easier to just empty the table and then add all the new ones that are in this foreach loop
– RiggsFolly
Nov 21 at 14:38

Why is $id only set when $key5 === 'ID'?
– Nigel Ren
Nov 21 at 14:39

add a comment |

This will likely be easier to handle at the application level. Get all of the IDs in the database, then remove from that new set any IDs in your $data variable. From there, delete that set from the database. Logically this isn't an ideal design imo, as you scale to larger amounts of records this will get slower.
– Rogue
Nov 21 at 14:37

1

In that case would it not be easier to just empty the table and then add all the new ones that are in this foreach loop
– RiggsFolly
Nov 21 at 14:38

Why is $id only set when $key5 === 'ID'?
– Nigel Ren
Nov 21 at 14:39

This will likely be easier to handle at the application level. Get all of the IDs in the database, then remove from that new set any IDs in your $data variable. From there, delete that set from the database. Logically this isn't an ideal design imo, as you scale to larger amounts of records this will get slower.
– Rogue
Nov 21 at 14:37

In that case would it not be easier to just empty the table and then add all the new ones that are in this foreach loop
– RiggsFolly
Nov 21 at 14:38

Why is $id only set when $key5 === 'ID'?
– Nigel Ren
Nov 21 at 14:39

add a comment |

2 Answers
2

active

oldest

votes

up vote
0
down vote

You can save all the IDs in an array and at the end of your foreach you can use a query to delete all elements that are NOT IN your array:

$ids = array();

foreach($data as $key5 => $value5){

   if($key5 === 'ID') {

       $id=$value5;

       $ids = $id;

   }



   $sql = "INSERT INTO offers 

                (id,status)

        VALUES ('$id','$status') 

        ON DUPLICATE KEY 

            UPDATE status = 'updated'";

}



$delete_sql = "DELETE FROM offers WHERE id NOT IN ('".implode("','", $ids)."')";

answered Nov 21 at 14:42

Mojo Allmighty

645316

add a comment |

up vote
0
down vote

There are many ways in which this could be done. Try this one...

$ids = array();   # to store existing ids



foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id = $value5;

        $ids = $id;    # collect all existing ids

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}



$deleteSql = "DELETE FROM `offers`

              WHERE `id` NOT IN (" . implode(',' $ids) . ')';

// then run your $deleteSql query ...

answered Nov 21 at 14:42

marvinIsSacul

35516

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414356%2fdelete-data-present-in-db-and-not-present-in-current-for-loop%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
0
down vote

You can save all the IDs in an array and at the end of your foreach you can use a query to delete all elements that are NOT IN your array:

$ids = array();

foreach($data as $key5 => $value5){

   if($key5 === 'ID') {

       $id=$value5;

       $ids = $id;

   }



   $sql = "INSERT INTO offers 

                (id,status)

        VALUES ('$id','$status') 

        ON DUPLICATE KEY 

            UPDATE status = 'updated'";

}



$delete_sql = "DELETE FROM offers WHERE id NOT IN ('".implode("','", $ids)."')";

answered Nov 21 at 14:42

Mojo Allmighty

645316

add a comment |

up vote
0
down vote

You can save all the IDs in an array and at the end of your foreach you can use a query to delete all elements that are NOT IN your array:

$ids = array();

foreach($data as $key5 => $value5){

   if($key5 === 'ID') {

       $id=$value5;

       $ids = $id;

   }



   $sql = "INSERT INTO offers 

                (id,status)

        VALUES ('$id','$status') 

        ON DUPLICATE KEY 

            UPDATE status = 'updated'";

}



$delete_sql = "DELETE FROM offers WHERE id NOT IN ('".implode("','", $ids)."')";

answered Nov 21 at 14:42

Mojo Allmighty

645316

add a comment |

up vote
0
down vote

You can save all the IDs in an array and at the end of your foreach you can use a query to delete all elements that are NOT IN your array:

$ids = array();

foreach($data as $key5 => $value5){

   if($key5 === 'ID') {

       $id=$value5;

       $ids = $id;

   }



   $sql = "INSERT INTO offers 

                (id,status)

        VALUES ('$id','$status') 

        ON DUPLICATE KEY 

            UPDATE status = 'updated'";

}



$delete_sql = "DELETE FROM offers WHERE id NOT IN ('".implode("','", $ids)."')";

answered Nov 21 at 14:42

Mojo Allmighty

645316

You can save all the IDs in an array and at the end of your foreach you can use a query to delete all elements that are NOT IN your array:

$ids = array();

foreach($data as $key5 => $value5){

   if($key5 === 'ID') {

       $id=$value5;

       $ids = $id;

   }



   $sql = "INSERT INTO offers 

                (id,status)

        VALUES ('$id','$status') 

        ON DUPLICATE KEY 

            UPDATE status = 'updated'";

}



$delete_sql = "DELETE FROM offers WHERE id NOT IN ('".implode("','", $ids)."')";

answered Nov 21 at 14:42

Mojo Allmighty

645316

answered Nov 21 at 14:42

Mojo Allmighty

645316

answered Nov 21 at 14:42

Mojo Allmighty

645316

answered Nov 21 at 14:42

Mojo Allmighty

645316

add a comment |

up vote
0
down vote

There are many ways in which this could be done. Try this one...

$ids = array();   # to store existing ids



foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id = $value5;

        $ids = $id;    # collect all existing ids

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}



$deleteSql = "DELETE FROM `offers`

              WHERE `id` NOT IN (" . implode(',' $ids) . ')';

// then run your $deleteSql query ...

answered Nov 21 at 14:42

marvinIsSacul

35516

add a comment |

up vote
0
down vote

There are many ways in which this could be done. Try this one...

$ids = array();   # to store existing ids



foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id = $value5;

        $ids = $id;    # collect all existing ids

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}



$deleteSql = "DELETE FROM `offers`

              WHERE `id` NOT IN (" . implode(',' $ids) . ')';

// then run your $deleteSql query ...

answered Nov 21 at 14:42

marvinIsSacul

35516

add a comment |

up vote
0
down vote

There are many ways in which this could be done. Try this one...

$ids = array();   # to store existing ids



foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id = $value5;

        $ids = $id;    # collect all existing ids

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}



$deleteSql = "DELETE FROM `offers`

              WHERE `id` NOT IN (" . implode(',' $ids) . ')';

// then run your $deleteSql query ...

answered Nov 21 at 14:42

marvinIsSacul

35516

There are many ways in which this could be done. Try this one...

$ids = array();   # to store existing ids



foreach($data as $key5 => $value5){

    if($key5 === 'ID') {

        $id = $value5;

        $ids = $id;    # collect all existing ids

    }



    $sql = "INSERT INTO offers 

                    (id,status)

            VALUES ('$id','$status') 

            ON DUPLICATE KEY 

                UPDATE status = 'updated'";

}



$deleteSql = "DELETE FROM `offers`

              WHERE `id` NOT IN (" . implode(',' $ids) . ')';

// then run your $deleteSql query ...

answered Nov 21 at 14:42

marvinIsSacul

35516

answered Nov 21 at 14:42

marvinIsSacul

35516

answered Nov 21 at 14:42

marvinIsSacul

35516

answered Nov 21 at 14:42

marvinIsSacul

35516

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl