Checking array data in sql condition PHP

Question

I would like to UPDATE the seat_status to active by checking two conditions.

1st condition = bus_id

2nd condition = seat_title

I'm using this code in function cancelbook.

function cancelbook($conn,$id,$busid)
{
    $stmtgetseats = $conn->prepare("SELECT seat_no from tbl_seats WHERE bus_id=:bus_id");
    $stmtgetseats->bindParam(':bus_id',$busid);
    $stmtgetseats->execute();
    $seat_no=$stmtgetseats->fetchAll();
    for($i=0;$i<count($seat_no);$i++)
    {
        $stmtactive = $conn->prepare("UPDATE tbl_busseats SET seat_status='active' WHERE bus_id=:bus_id AND seat_title=:seat_title");
        $stmtactive->bindParam('bus_id',$busid);
        $stmtactive->bindParam('seat_title',$seat_no[$i]);
    }
    if ($stmtactive->execute()) {
        exit();
        return true;
    }
    return false;
}

im getting this error Notice: Array to string conversion

the last if statement doesn't look right !, exit() will terminate script execution, did you mean if ($stmtactive->execute()) return true; return false; — xanadev
– xanadev, Commented Jun 10, 2018 at 13:25
which means that $seat_no[$i] is not a string but an array, can you do a var_dump($seat_no); — xanadev
– xanadev, Commented Jun 10, 2018 at 13:35

xanadev · Accepted Answer · 2018-06-10 14:47:01Z

1

The way you did the loop will only update one row, to update every row you should execute your statement for each iteration.

function cancelbook($conn,$id,$busid)
{
    $stmtgetseats = $conn->prepare("SELECT seat_no from tbl_seats 
                                    WHERE bus_id=:bus_id");
    $stmtgetseats->bindParam(':bus_id',$busid);
    $stmtgetseats->execute();
    $seat_no=$stmtgetseats->fetchAll();
    foreach($seat_no as $seat) {
        $stmtactive = $conn->prepare("UPDATE tbl_busseats SET seat_status='active' 
                                      WHERE bus_id=:bus_id 
                                      AND seat_title=:seat_title");
        $stmtactive->bindParam('bus_id',$busid);
        $stmtactive->bindParam('seat_title',$seat['seat_no']);
        $stmtactive->execute();
    }
}

answered Jun 10, 2018 at 14:47

xanadev

8261 gold badge10 silver badges27 bronze badges

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

3 Comments

xanadev Over a year ago

if you need to keep track of the number of successful updates and failures, i'll update the answer accordingly.

Yuseferi Over a year ago

what is difference between your solution and mine?

xanadev Over a year ago

read the OP's question and my answer, i'm sure you'll figure it out :)

Yuseferi · Accepted Answer · 2018-06-10 16:41:33Z

0

it seems $seat_no[$i] is not string and it's an array , (I suggest var_dump($seat_no[$i]); it before), my quess is it should be something the following snippet:

use

$stmtactive->bindParam('seat_title',$seat_no[$i]['seat_no']);

instead of

$stmtactive->bindParam('seat_title',$seat_no[$i]);

in your code will resolved your problem.

of course, it's better instead of using for learn to use foreach

But another solution with better performance is using just one update instead of serveral updates!!!

function cancelbook($conn,$id,$busid)
{
    $stmtgetseats = $conn->prepare("SELECT seat_no from tbl_seats 
                                    WHERE bus_id=:bus_id");
    $stmtgetseats->bindParam(':bus_id',$busid);
    $stmtgetseats->execute();
    $seat_no=$stmtgetseats->fetchAll();
    $seat_numbers = array_values($seat_no);
    $stmtactive = $conn->prepare("UPDATE tbl_busseats SET seat_status='active' 
                                      WHERE bus_id=:bus_id 
                                      AND seat_title IN (:seat_title"));
    $stmtactive->bindParam('bus_id',$busid);
    $stmtactive->bindParam('seat_title',implode(",",$seat_numbers));
    $stmtactive->execute();
}

Also, remove Exit in your code, it's a bad mistake in your code

edited Jun 10, 2018 at 16:41

answered Jun 10, 2018 at 13:30

Yuseferi

8,83011 gold badges77 silver badges106 bronze badges

2 Comments

Soonang Rai Over a year ago

i need to check value from that array to string

Yuseferi Over a year ago

@SoonangRai I mean you just need this line, why I should rewrite the whole code of you? I answered this question before another answer but you mark that as answer!!!! why? the solutions is the same

Collectives™ on Stack Overflow

Checking array data in sql condition PHP

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related