Delete Row Result SQL Query

Question

I am new in PHP. I am android developer and does not know PHP enough. I have developed one function which provide me numbers from MySQL database. I want delete that numbers instant as soon as it pass me. I am currently doing it like below

function getAllNumbers() {
    require_once("includes/conf.php");
    global $conn;
    $sql = "SELECT number from number_list WHERE server=1";
    $result = $conn->query($sql);
    $data = array();
    if($result) {
        while($row = $result->fetch_row()) {
        array_push($data, $row[0]);
        $delete = "DELETE number from number_list WHERE server=1";
        $result = $conn->query($delete);

        }
    }
    $response["data"] = $data;
    return $response;
}

My Table Structure is like below

id  int(11) NO  PRI NULL    auto_increment  
name    varchar(50) NO      NULL        
number  varchar(50) NO      NULL        
server  int(10) NO      0       
status  int(1)  NO      -1      
last_act    timestamp   NO      CURRENT_TIMESTAMP       
user_id int(11) NO  MUL NULL        
created_at  timestamp   NO      CURRENT_TIMESTAMP       
disable int(11) NO      0       
notify  int(1)  NO      1       
fcm varchar(500)    NO      NULL

But I am feeling that if there any new number arrive in database between number select query and delete query it will delete it without select number in first query. So I am looking to delete only rows which get selected in first query. Let me know if there anything I need to change in my codes.

Thanks a lot :)

@RiggsFolly I have added table structure. Please check it. Thanks — Rina Patel
– Rina Patel, Commented Jul 24, 2018 at 17:34
first if u select the query result and after delete the result you want means server=1 so it will deleted, please check it. — Mohit Kumar
– Mohit Kumar, Commented Jul 24, 2018 at 17:36
You cannot DELETE column, but you can update it and set it to null. Delete will remove whole row. — Laurynas
– Laurynas, Commented Jul 24, 2018 at 17:36

RiggsFolly · Accepted Answer · 2018-07-24 17:39:33Z

1

So if you select the id in the first query as that is the unique key you can use that to delete just this row

function getAllNumbers() {
    require_once("includes/conf.php");
    global $conn;
    $sql = "SELECT number,id from number_list WHERE server=1";
    $result = $conn->query($sql);
    $data = array();
    if($result) {
        while($row = $result->fetch_row()) {
            $data[] = $row[0];
            $id = $row[1];
            $delete = "DELETE number from number_list WHERE id = $id";
            $result = $conn->query($delete);
        }
    }
    $response["data"] = $data;
    return $response;
}

answered Jul 24, 2018 at 17:39

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laurynas Over a year ago

Why would you delete them one by one?

Rina Patel Over a year ago

Thanks a lot sir! I think this is the perfect way to do it.

Rina Patel Over a year ago

@LaurynasGerbutavicius its bad practice as per You? Thanks

Collectives™ on Stack Overflow

Delete Row Result SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related