MySQL - Execute Select and Delete in different queries?

Question

I was wondering how would go about executing two different queries like this:

    if ($uresult->num_rows >0) {


 while($urow = $uresult->fetch_assoc()) {


 $rresult = mysqli_query($con,"SELECT * FROM allid WHERE postid='$oldid' AND spaceid='$newid'");
 $rresult = mysqli_query($con,"DELETE FROM allid WHERE postid AND spaceid IS NULL");
 $lrow = mysqli_fetch_assoc($rresult);
 $tem = $lrow['postid'];

 $ujson = json_encode($tem);
 echo $ujson;
 }

} else {
}

I know mysqli_fetch can't hold no more than one query and there are similar questions, but I can't seem to understand the answers from the other questions. If there is a question that solves this question, I apologize and will delete this one.

you use $rresult for both queries, just change one .. solved (you dont even need to assign the delete one to a var — user9487972
– user9487972, Commented May 28, 2018 at 23:13

RiggsFolly · Accepted Answer · 2018-05-28 23:21:10Z

1

all other things being equal, simple dont overwrite the $rresult from select with delete:

if ($uresult->num_rows >0) {

    while($urow = $uresult->fetch_assoc()) {
        $rresult = mysqli_query($con,"SELECT * 
                                      FROM allid 
                                      WHERE postid='$oldid' 
                                      AND spaceid='$newid'");
        mysqli_query($con,"DELETE FROM allid 
                           WHERE postid AND spaceid IS NULL");
        $lrow = mysqli_fetch_assoc($rresult);
        $tem = $lrow['postid'];

        $ujson = json_encode($tem);
        echo $ujson;
    }

} else {
}

edited May 28, 2018 at 23:21

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered May 28, 2018 at 23:15

user9487972

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

16 Comments

Laney Williams Over a year ago

Ok, I deleted the variable and the second query is not deleting the null from the results. Does placement matter? @smith

user9487972 Over a year ago

@LaneyWilliams did you mean to write WHERE postid IS NULL AND spaceid IS NULL ?

Laney Williams Over a year ago

I believe so and I just tested that. Here is what I am getting: "10"nullnullnullnullnullnull"202"

user9487972 Over a year ago

you are deleting after you select, did you mean to do it the other way around?

Laney Williams Over a year ago

Oh no. I just want to remove those null from the results above. I looked up other answers and they said to use IS NULL.

|

SpacePhoenix · Accepted Answer · 2018-05-28 23:16:46Z

0

If you have multiple queries that either rely on a result of a previous query or need to be run in order and each successfully run, you could use transactions. With transactions if one of the queries fails, you can roll back the transaction.

answered May 28, 2018 at 23:16

SpacePhoenix

6052 gold badges5 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL - Execute Select and Delete in different queries?

2 Answers 2

16 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related