在一个页面上检索客户端/项目 MySql 数据

这可能是很多，但希望我能正确地理解所有内容。

我购买了这个时间跟踪器：http://codecanyon.net/item/the-simple-clientproject-tracker/48930

我试图做的是在一页上列出所有客户及其相关项目。 在构建时，您只能在侧边栏中将它们作为单独的列表单独查看，或者当您单击客户端时，您将看到该单个客户的项目。 但是，包含所有项目的所有客户的主列表会更好，这就是我试图在单个页面上实现的目标。

---

看起来有 3 张表相互通信，[ 项目、客户端project_clients ]

项目列： [ project_id、名称、unique_id ]

客户端列： [ client_id， 名称 ]

Project_clients列： [ project_id， client_id ]

---

以下是现有函数：

/*
This returns the list of clients that are created with a link to view a list of their projects
*/
function getClientList(){
global $db,$secret;
$return = '';
$return = '<ul class="client_list">';
$query = "SELECT * FROM clients";
$res = $db->query($query,'assoc');
if(!empty($res)){
foreach($res as $row):
$return .= '<li><a href="client.php?client='.$row['client_id'].'" class="clientname" title="This client has '.tot_projects($row['client_id']).' projects associated to them">'.stripslashes($row['name']).'</a></li>';
endforeach;
}else{
$return .= '<li>No Clients Yet</li>';
}
$return .= '</ul>';
return $return;
}

--

/*
This will grab the each time a track was created and display it
@param MD5 $unique_id
*/
function getClientProjectTable($client_id){
global $db;
$clientLog = '';
//Get project_id
$query = "SELECT client_id FROM clients WHERE client_id = ".$db->prep($client_id);
$res = $db->query($query,'assoc');

$query = "SELECT project_id FROM project_clients WHERE client_id =".$db->prep($res[0]['client_id'])." ";
$resT = $db->query($query,'assoc');
//set vars
$thead = '<table id="timeLog" colspacing="0" colpadding="0" border="0">';
$thead .= '<thead><tr><th>Name</th><th>Hours</th><th></th></tr>
<tbody>';
$count_row = 0;
$alt = '';
$date = '';
$prevDate = '';
if($resT != false){
foreach($resT as $row):
$alt = ($count_row%2 == 0) ?'odd': '';
//Grab each track and process how much time has passed for it
$query = "SELECT name,unique_id FROM project WHERE project_id = ".$db->prep($row['project_id']);
$resProject = $db->query($query,'assoc');
$actions = '<a href="invoicebook.php?project_id='.urlencode($resProject[0]['unique_id']).'"><img src="images/invoice.png" alt="Invoice" style="vertical-align:middle;"/></a>';
//setup any actions to the row
$actions .= '<form name="projectDeleteAssign" action="process.php" method="post" style="display:inline; margin:0;" onsubmit="return confirmClientDelete();">
<input type="hidden" name="hiddenProjectID" value="'.$row['project_id'].'"  />
<input type="hidden" name="hiddenClientID" value="'.$res[0]['client_id'].'"  />
<input type="hidden" name="confirmDeleted" value="true"  />
<input type="submit" name="action" class="delete" value="Delete Assignment"  />
</form>';
//Check and see if you should check time based on if track has ended or is still running
$clientLog .= '<tr class="'.$alt.'"><td><a href="index.php?track='.urlencode($resProject[0]['unique_id']).'">'.stripslashes($resProject[0]['name']).'</a></td><td>'.tot_time($resProject[0]['unique_id'],'hours').'</td><td>'.$actions.'</td></tr>';
$count_row++;
endforeach;
}else{
$clientLog .= '<tr><td colspan="3">No Projects added to this client yet.</td></tr>';
}
$tfoot = '</tbody></table>';
return $thead.$clientLog.$tfoot;
}

--

/*
This returns the list of projects that are created with a link to view a detail view of each
*/
function getProjectList(){
global $db,$secret;
$return = '';
$return = '<ul>';
$query = "SELECT * FROM project";
$res = $db->query($query,'assoc');
if(!empty($res)){
foreach($res as $row):
$u_id = md5(utf8_encode($row['project_id'].$secret));
$return .= '<li><a href="index.php?track='.$u_id.'" class="'.bResult($row['startStop_track'],'timeStart','time').'">'.stripslashes($row['name']).'</a></li>';
endforeach;
}else{
$return .= '<li>No Projects Yet</li>';
}
$return .= '</ul>';
return $return;
}

--

/**
* 
* This will return the total number of projects
*/
function tot_projects($client = null){
global $db;
if($client){
$query = "SELECT count(p.project_id) FROM project p INNER JOIN project_clients pc ON pc.project_id = p.project_id WHERE client_id = ".$db->prep($client);
}else{
$query = "SELECT count(project_id) FROM project";
}
$res = $db->query($query,'row');
return $res[0][0];
}

有什么想法吗？

function getClientList2(){
    global $db,$secret;
    $return = "";
    $query = "SELECT * FROM clients";
    $res = $db->query($query,'assoc');
    if(!empty($res)){
        foreach($res as $row) {
            $return .= getClientProjectTable($row['client_id']);
        }
    }
    return $return;
}