php - 改进使用 PDO 的 SELECT 并添加 UPDATE 语句

Question

我使用以下代码从 MySQL 表中选择数据。有人可以告诉我如何改进它，因为它看起来有点乱吗？

此外，每次从数据库查询客户时，我都需要运行 UPDATE 语句来增加“ View ”列中的值。数据库中的每个客户行都有一个名为“views”的列。例如，假设 ABC Corp 有 100 次观看。如果我搜索 ABC Corp 并且数据库返回该记录，则该记录的“ View ”列应更新为 101。执行此操作的最佳方法是什么？

if ($search && ($group && $group !== "*")) {
  $sql = "SELECT * FROM customers WHERE description LIKE :description AND groupId LIKE :groupId";
  $result = $conn->prepare($sql);
  $result->bindValue(":description", "%" . $search . "%", PDO::PARAM_STR);
  $result->bindValue(":groupId", $groupId, PDO::PARAM_INT);
} else if ($search) {
  $sql = "SELECT * FROM customers WHERE description LIKE :description";
  $result = $conn->prepare($sql);
  $result->bindValue(":description", "%" . $search . "%", PDO::PARAM_STR);
} else if ($group !== "*") {
  $sql = "SELECT * FROM customers WHERE groupId LIKE :groupId";
  $result = $conn->prepare($sql);
  $result->bindValue(":groupId", $groupId, PDO::PARAM_INT);
} else {
  $sql = "SELECT * FROM customers";
  $result = $conn->prepare($sql);
}

Answer 1

像这样怎么样，

 $sql = "SELECT * FROM customers ";
 $and = $grp = FALSE;

 if($search || ($group && $group !== "*") {
 $sql .= " WHERE ";
 if ($search) {
  $sql .= " description LIKE :description ";
  $and = TRUE;
 } 

 if ($group && $group !== "*") {
   if( $and === TRUE )
     $sql .= " AND ";        
   $sql .= " groupId LIKE :groupId ";  
   $grp = TRUE;     
 }     
 } 

 $result = $conn->prepare($sql);
 if( $and === TRUE)
  $result->bindValue(":description", "%" . $search . "%", PDO::PARAM_STR);

 if( $grp === TRUE)
   $result->bindValue(":groupId", $groupId, PDO::PARAM_INT);

对于UPDATE语句，

   //say $cust_name is the requested customer to be searched
   $sql = "SELECT views from customers where customer_name = '" $cust_name."'";
   $res = $conn->query($sql);
   $views = $res->fetchColumn() + 1;
   //sets 'views' to num_of_customers/rows returned.
   $sql = "UPDATE customers SET VIEWS = " .$views." WHERE customer_name = '" $cust_name."'";
   $res = $conn->query($sql);