php - 如何进行有效的sql查询？

Question

下表为:

表用户:

User_Id = 1,2,3;
User_Name: A,B,C;

表业务:

Business_Id = 1,2,3;
Business_Name: A,B,C;
Business_Detail: Details_A, Details_b, Details_c; 

表访问量:

Visit_Id = 1,2,3,4,5,6:
User_Id = 1,1,1,2,1,1;
Business_Id = 1,1,1,2,2,3;

我需要创建一个函数来返回访问列表以及有关用户访问的企业的信息。到目前为止，我已经获得了用户访问过的商店的列表，但不知道从那里去哪里。

 function visit_count($user_id=1){
     global $database;
     $sql = "SELECT * FROM visits WHERE user_id ='{$user_id}' LIMIT 0 , 30";
     $result_set = $database->query($sql);
     $visits = mysql_fetch_array($result_set);

     //Get the unique ids of the business

    //Run another query that has the business information

    //combing both queries.

 }

感谢大家的快速回复。这几乎就是我正在寻找的东西，我想我正在寻找返回对象的查询，如下所示:

Object:
  - Business:
     - Business_id;
     - Business_name;
     - Visit_counts;
  - Business:
     - Business_id;
     - Business_name;
     - Visit_counts;

所以基本上该对象将包含商家信息和用户访问商店的次数。

非常感谢大家的帮助

Answer 1

好吧，首先你应该查看 prepared statements ，这是当前在 PHP 中使用 sql 的最佳实践。

听起来您需要的是联接。

$sql = "SELECT * FROM visits v
        JOIN business b ON b.Business_Id = v.Business_Id
        JOIN user u ON u.Business_Id = v.Business_Id
        WHERE v.user_id ='{$user_id}' LIMIT 0 , 30";