Insert multiple rows in one column

Question

I want to assign multiple vehicles in one reservation if i click the add button. Here is my code;

if(isset($_REQUEST['cid']))
{
    $cid=intval($_GET['cid']);
    $tid=intval($_GET['tid']);

    $sql = "UPDATE tblbooking SET VehicleId=:cid WHERE  id=:tid";

    $query = $dbh->prepare($sql);
    $query -> bindParam(':cid',$cid, PDO::PARAM_STR);
    $query -> bindParam(':tid',$tid, PDO::PARAM_STR);
    $query -> execute(); 

    $msg ="Vehicle Successfully Assigned. ";


}

I tried INSERT INTO but I am not going anywhere.Does anyone know how to revise this code? I want my output to be like this;

|booking_id|VehicleId|
----------------------
|1         |1        |
|1         |2        |
----------------------

Your `booking_id` in second row should be `1` as in first row. It's N-M relation, so your `booking` entity will have multiple `vehicles` and it's all joined via third table `tblbooking` — Justinas, Apr 27 '20 at 08:48
Also your binding fails, because you should use `:cid` and `:tid` in query instead of direct `$cid` and `$tid`. E.g. `UPDATE tblbooking SET VehicleId=:cid WHERE id=:tid` — Justinas, Apr 27 '20 at 08:49
So what's wrong then? You should execute 2 queries for both vehicles — Justinas, Apr 27 '20 at 08:52
See these examples https://phpdelusions.net/pdo_examples/update — , Apr 27 '20 at 08:56
It's just updating the *VehicleId* column and not adding the new assigned :cid — AZURE KNIGHT, Apr 27 '20 at 08:57

score 0 · Answer 1 · answered Apr 27 '20 at 08:59

0

UPDATE statement with named placeholders adding both values into 1 column

if(isset($_REQUEST['cid']))
{
    $cid=intval($_GET['cid']);
    $tid=intval($_GET['tid']);

    $bothValues = $tid.','.$cid; //adding both values in to 1 column

    $sql = "UPDATE tblbooking SET VehicleId = :VehicleId WHERE  id=:id";

    $query = $dbh->prepare($sql);
    $query -> bindParam(':VehicleId',$bothValues, PDO::PARAM_STR);
    $query -> bindParam(':id',$tid, PDO::PARAM_INT);
    $query -> execute(); 

    $msg ="Vehicle Successfully Assigned. ";
}

answered Apr 27 '20 at 08:59

It is shows the success message but assigning blank value – AZURE KNIGHT Apr 27 '20 at 09:05
Do if statement to see if you get values from html form `if(empty($cid) || empty($tid)){ echo "Empty values"; }` – Apr 27 '20 at 09:05
I added $lastInsertId = $dbh ->lastInsertId(); if($lastInsertId) { $msg="Vehicle Successfully Added."; } else { $error="Something went Wrong!"; } } but it shows error message – AZURE KNIGHT Apr 27 '20 at 09:08
Sorry but your question was for "Insert multiple rows in one column", Check this : PDO get the last ID inserted https://stackoverflow.com/a/10680965/12232340 – Apr 27 '20 at 09:11

Insert multiple rows in one column

1 Answers1