php - 为了解决下面的数据库问题，要写什么查询语句？

Question

我在数据库中有以下 3 个表。

1. Programs_Table
   Program_ID(主键)
   开始日期
   结束日期
   已完成
   IsGoalsMet
   Program_type_ID

2. Programs_Type_Table(不同类型的节目，支持表单中的下拉列表)
   Program_type_ID(主键)
   程序名称
   程序_描述

3. Client_Program_Table
   Client_ID(主键)
   Program_ID(主键)

找出特定计划(计划类型)中有多少客户的最佳方法是什么？

下面的 SQL 语句是否是最好的方式，甚至是合理的？

SELECT Client_ID FROM Client_Program_Table
INNER JOIN Programs_Table 
ON Client_Program_Table.Program_ID = Programs_Table.Program_ID
WHERE Programs_Table.Program_type_ID = "x"

其中“x”是我们感兴趣的特定程序的 Program_type_ID。

或者以下是更好的方法吗？

$result = mysql_query("SELECT Program_ID FROM Programs_Table 
WHERE Program_type_ID = 'x'");
$row = mysql_fetch_assoc($result);
$ProgramID = $row['Program_ID'];
$result = mysql_query("SELECT * FROM Client_Program_Table
WHERE Program_ID = '$ProgramID'");
mysql_num_rows($result) // returns how many rows of clients we pulled. 

在此先感谢您，请原谅我的经验不足以及我所犯的任何错误。

Answer 1

这里是你如何做到的:

<?php
// always initialize a variable
$number_of_clients = 0;

// escape the string which will go in an SQL query 
// to protect yourself from SQL injection
$program_type_id = mysql_real_escape_string('x');

// build a query, which will count how many clients 
// belong to that program and put the value on the temporary colum "num_clients"
$query =   "SELECT COUNT(*) `num_clients` FROM `Client_Program_Table` `cpt`
            INNER JOIN `Programs_Table` `pt`
            ON `cpt`.`Program_ID` = `pt`.`Program_ID`
            AND `pt`.`Program_type_ID` = '$program_type_id'";
// execute the query
$result = mysql_query($query);

// check if the query executed correctly 
// and returned at least a record
if(is_resource($result) && mysql_num_rows($result) > 0){
    // turn the query result into an associative array
    $row = mysql_fetch_assoc($result);

    // get the value of the "num_clients" temporary created column
    // and typecast it to an intiger so you can always be safe to use it later on
    $number_of_clients = (int) $row['num_clients'];
} else{

    // query did not return a record, so we have no clients on that program
    $number_of_clients = 0;
}
?>