combining mysql queries in a function php

Question

I am trying to create function that archives a record. I have a list of records with each record having a dropdown with options delete, edit, archive. When archive is clicked Id like that record tp be moved to a new table and then that original record to be removed. Can I have an Insert and DElete run in the same query? If not how else can I achieve this? here is what I've currently got:

public function archiveCampaign($campaign_id) {

        $this->db->query("INSERT INTO `" . DB_PREFIX . "campaigns_archive` SELECT * WHERE campaign_id = '" . (int)$campaign_id . "'");
        $this->db->query("DELETE FROM `" . DB_PREFIX . "campaigns` WHERE campaign_id = '" . (int)$campaign_id . "'");


        return $campaign_id;
    }

WARNING: This has some severe SQL injection bugs because user data is used inside the query. Whenever possible use prepared statements. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. NEVER put $_POST, $_GET or any user data directly in your query. — tadman
– tadman, Commented Sep 25, 2017 at 17:26
As far as I know you can't do both INSERT and DELETE with a single SQL statement. I think the closest you'll get is executing them sequentially in a transaction. — Don't Panic
– Don't Panic, Commented Sep 25, 2017 at 17:31
I found a similar query on Stack Exchange, hope this helps dba.stackexchange.com/questions/44215/… — user8610988
– user8610988, Commented Sep 25, 2017 at 17:35
@Don'tPanic why won't a multi-query not work? as I suggested. — Funk Forty Niner
– Funk Forty Niner, Commented Sep 25, 2017 at 17:41

Tony Chiboucas · Accepted Answer · 2017-09-25 18:19:42Z

You're already close

Sql doesn't allow multiple commands in a single statement, with the exception of subqueries. Only SELECT statements have an operable return value, thus they are the only commands that can be used in a subquery. That means you will have to run two commands very similar to what you already have written.

If I assume your db object's query method returns true/false, or something to evaluate (it probably does). Return success/fail, or throw an exception somewhere, so you don't wind up with corrupt data.

public function archiveCampaign($campaign_id) {

    $insertResult = $this->db->query("INSERT INTO `" . DB_PREFIX . "campaigns_archive` SELECT * FROM `" . DB_PREFIX . "campaigns` WHERE campaign_id = '" . (int)$campaign_id . "'");

    if (!$insertResult) {
        // DON'T DELETE THE DATA, THE INSERT FAILED!
        return false; // or an error object, or throw an exception
    }

    $deleteResult = $this->db->query("DELETE FROM `" . DB_PREFIX . "campaigns` WHERE campaign_id = '" . (int)$campaign_id . "'");

    if (!$deleteResult) {
        // DELETE FAILED, remove from archive.
        $this->db->query("DELETE FROM `" . DB_PREFIX . "campaigns_archive` WHERE campaign_id = '" . (int)$campaign_id . "'");
        return false; // or an error object, or throw an exception
    }

    return true;
}

PHP PDO

Please consider using PHP:PDO.

Collectives™ on Stack Overflow

combining mysql queries in a function php

1 Answer 1

You're already close

PHP PDO

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You're already close

PHP PDO

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related