How do I change a value in a column in a table when I add another table?

Question

How do I change the row value of a column in a table when I add another table? How do you ask for help? I have two tables in the database The first table is called Drug It consists of three columns: Sample Table I

// TABLE Drug 
DROP TABLE IF EXISTS `Drug`;
CREATE TABLE IF NOT EXISTS `Drug` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`brId` text NOT NULL,
`nameDrug` text NOT NULL,
 PRIMARY KEY (`id`)
 )

The second table is named brand Sample Table II

       // TABLE brand
   DROP TABLE IF EXISTS `brand`;
   CREATE TABLE IF NOT EXISTS `brand` (
   `idBrand` int(11) NOT NULL AUTO_INCREMENT,
   `brandName` text NOT NULL,
  `theUse` text NOT NULL,
   PRIMARY KEY (`idBrand`)
   )

What I need is when you add a row in the brand table, brId is updated in the Drug table to the new idBrand by id in the drug table that was sent I've done the following code because it does not work

   <?php
    require_once('include/config.php');

    $id = $_POST['id'];

   $brandName = $_POST['brandName'];

   $theUse = $_POST['theUse'];

    $query = "INSERT INTO brand 
    (brandName,theUse)VALUES('".$brandName."','".$theUse."');";

     $insertBrand = mysqli_query($con,$query);           
                  if($insertBrand)
                    {
                $updatDrug = "UPDATE  `drug` SET  `brId` = new.idBrand WHERE `id` = '".$id."' ;";
                $resultEnd = mysqli_query($con,$updatDrug);
    if($resultEnd){
     $result = 'OK';
     echo json_encode($result);
     }else{
     $resultno = 'NO';
    echo json_encode($resultno);
       }
    }                                                   

mysqli_close($con);
?>

why is `brId` of type `text` while `idBrand` is `int`. Think you need `brId` of `int` type too. — danblack, Nov 29 '18 at 01:36
But the value of the id will be unknown when you run the trigger. I need to update the sender id — salem715, Nov 29 '18 at 01:36

danblack · Accepted Answer · 2018-11-29T02:21:07.170

0

After the INSERT, use mysqli_insert_id as the value for brId.

$br = mysqli_insert_id($con);
$updatDrug = "UPDATE drug SET brId = :brid WHERE id = :id";
$stmt = $con->prepare($updatDrug);
$stmt->bind_param('ii', $br, $id);
$stmt->execute();
$stmt->close();

And please avoid SQL INJECTION

edited Nov 29 '18 at 02:21

answered Nov 29 '18 at 01:34

danblack

12,130
2
22
41

do you mean this $br = mysqli_insert_id($con,$query); $updatDrug = "UPDATE `drug` SET `brId` = '".$br."' WHERE `id` = '".$id."' ;"; – salem715 Nov 29 '18 at 02:13
danblack Do not work with me Can you edit my code on the subject and send you a silent thank you – salem715 Nov 29 '18 at 02:36

score 0 · Answer 2 · answered Nov 29 '18 at 01:38

Try transaction commit, here is an example

<?php
$db = new mysqli("localhost","root","","test"); //连接数据库

$db->autocommit(false); //设置为非自动提交——事务处理

$sql1  = "INSERT INTO `test`.`test1` (`name` )VALUES ('1' )";

$result1 = $db->query($sql1);

$sql2  = "INSERT INTO `test`.`test2` (`a` )VALUES ('1')";

$result2 = $db->query($sql2);

if ($result1 && $result2) {

$db->commit();  //全部成功，提交执行结果

echo '提交';

} else {

$db->rollback(); //有任何错误发生，回滚并取消执行结果

echo '回滚';

}

$db->autocommit(true); 

$db->close();

?>

建议你将回答翻译成英语，这个社区活跃的用户绝大部分都以英语交流的，中文可能极少有人能看懂。另外你的答案最好以问题作为示例，这样提出问题的人理解起来更容易一些。这只是建议，你可以不接受 — Luna, Nov 29 '18 at 02:35

How do I change a value in a column in a table when I add another table?

2 Answers2