php - 如何正确绑定(bind) PHP 准备和执行语句？

Question

我有一个函数可以在数据库中插入或更新一个值。

我正在使用准备和执行语句，我想匹配我的函数的 ELSE 子句以匹配准备好的语句。

在我的 ELSE 子句中，我还有一个值(即 $id)，所以我不确定是否可以在执行数组中分配它。

function insert_value($item_name, $description, $supplier_code, $cost, $sell_price,$num_on_hand, $reorder_point, $back_order, $id=0)
{

    $connection = db_connect();
    if($id==0)
    {
        $sql = 'INSERT INTO inventory (itemName, description, supplierCode, cost, price, onHand, reorderPoint, backOrder) 
                VALUES(:itemName, :description, :supplierCode, :cost, :price, :onHand, :reorderPoint, :backOrder);';
    }
    else
    {
//NEED TO CHANGE THIS PART
        $sql = "UPDATE inventory SET itemName='$item_name', description='$description', supplierCode='$supplier_code', 
            cost='$cost', price='$sell_price', onHand='$num_on_hand',  reorderPoint='$reorder_point', backOrder='$back_order' 
            WHERE id='$id'";
    }

    $prepare = $connection->prepare($sql); 
    $prepare->execute(array(                  // AND THIS PART
        ":itemName" => $item_name,
        ":description" => $description,
        ":supplierCode" => $supplier_code,
        ":cost" => $cost,
        ":price" => $sell_price,
        ":onHand" => $num_on_hand,
        ":reorderPoint" => $reorder_point,
        ":backOrder" => $back_order,
        ));
}

Answer 1

类似于:

function insert_value($item_name, $description, $supplier_code, $cost, $sell_price,$num_on_hand, $reorder_point, $back_order, $id=0){
   $connection = db_connect();
    $arr = array(":itemName" => $item_name,
    ":description" => $description,
    ":supplierCode" => $supplier_code,
    ":cost" => $cost,
    ":price" => $sell_price,
    ":onHand" => $num_on_hand,
    ":reorderPoint" => $reorder_point,
    ":backOrder" => $back_order);
    if($id==0){
        $sql = 'INSERT INTO inventory (itemName, description, supplierCode, cost, price, onHand, reorderPoint, backOrder) 
            VALUES(:itemName, :description, :supplierCode, :cost, :price, :onHand, :reorderPoint, :backOrder)';
    }else{
        $sql = "UPDATE inventory SET itemName=:itemName, description=:description, supplierCode=:supplierCode, 
        cost=:cost, price=:price, onHand=:onHand, reorderPoint=:reorderPoint, backOrder=:backOrder
        WHERE id=:id";
        $arr[":id"] = $id;
    }

    $prepare = $connection->prepare($sql); 
    $prepare->execute($arr);
}

此外，您可能还想检查(如果您还没有这样做)以查看 $connection 是否有效，否则在准备/执行时您可能会遇到错误。