我有两个表，我想获取两个表值，这个代码应该可以正常工作，但count=2表示循环运行2次，返回2次的输出，就像count=3表示循环运行3次，返回3次输出，我错了。。。。
输出
{
"status":"success",
"count":2,
"data":[
{
"id":"1",
"t_id":"STV1",
"t_title":"Horoscope Uploading",
"t_project":"1",
"t_sub_project":"Sub-project",
"t_desc":"cfdgdgdcf",
"t_priority":"Urgent",
"t_assign_to":"AE098",
"t_assign_on":"2016-04-13 12:03:49",
"t_started_on":"2016-04-14 05:30 PM",
"t_due_on":"2016-04-22 05:30 PM",
"t_complete_percentage":"100",
"t_est_hours":"35",
"t_worked":"10 Hours",
"t_comment":"dfhfghfgfsfhfgh",
"t_created_on":"2016-04-13 12:03:49",
"t_edited_on":"2016-04-14 07:01:06",
"t_status":"3",
"t_delete_on":"0"
}
]
}{
"status":"success",
"count":2,
"data":[
{
"id":"1",
"t_id":"STV1",
"t_title":"Horoscope Uploading",
"t_project":"1",
"t_sub_project":"Sub-project",
"t_desc":"cfdgdgdcf",
"t_priority":"Urgent",
"t_assign_to":"AE098",
"t_assign_on":"2016-04-13 12:03:49",
"t_started_on":"2016-04-14 05:30 PM",
"t_due_on":"2016-04-22 05:30 PM",
"t_complete_percentage":"100",
"t_est_hours":"35",
"t_worked":"10 Hours",
"t_comment":"dfhfghfgfsfhfgh",
"t_created_on":"2016-04-13 12:03:49",
"t_edited_on":"2016-04-14 07:01:06",
"t_status":"3",
"t_delete_on":"0"
}
]
}
同样的ans，但我会来两次，如何解决这个问题
<?php
session_start();
include('dbconfig.php');
if(empty($_SESSION['email'])){
header('Location:login.php');
}
$dapartment = $_POST['department'];
$sql = mysql_query("SELECT * FROM task_employee WHERE emp_designation='$dapartment'");
$count=mysql_num_rows($sql);
$return = array();
while($r=mysql_fetch_assoc($sql)){
$emp_id=$r['emp_id'];
if($count > 0){
$mysql = mysql_query("SELECT * FROM task WHERE t_assign_to='$emp_id'");
while($row= mysql_fetch_assoc($mysql)){
$data[] = $row;
}
$return=array('status'=>'success','count'=>$count,'data'=>$data);
echo json_encode($return);
}else{
$return=array('status'=>'not found','count'=>$count,'data'=>$data);
echo json_encode($return);
}
}
?>如@Wobbles所述。您可以使用联接。
session_start();
include('dbconfig.php');
if(empty($_SESSION['email'])){
header('Location:login.php');
}
$dapartment = $_POST['department'];
$q = mysql_query("SELECT * FROM task_employee te, task t WHERE te.emp_designation='$dapartment' AND te.emp_id = t.t_assign_to");
$data = array();
while($r = mysql_fetch_assoc($q)){
$data[] = $r;
}
$return = array(
'status' => ((empty($data))?'not found':'success'),
'count' => sizeof($data),
'data' => $data
);
header('Content-Type: text/javascript; charset=utf8');
echo json_encode($return);
有关更多信息，请查看使用joins 的mysql
只需使用内部联接，就不必迭代员工id的
SELECT *
FROM task_employee
INNER JOIN task
ON task_employee.emp_id=task.t_assign_to
ORDER BY task_employee.emp_id;
1）您必须使用第一个查询收集emp_ids
2） 然后通过执行WHERE IN () 获得所有任务
3） 将所有任务收集到阵列
4） 您可以输出一次json_encode，否则您的json是无效的
<?php
session_start();
include('dbconfig.php');
if(empty($_SESSION['email'])){
header('Location:login.php');
}
header('Content-Type: text/javascript; charset=utf8');
$dapartment = $_POST['department'];
$q = "SELECT * FROM task_employee WHERE emp_designation='".mysql_real_escape_string($dapartment)."'";
$q = mysql_query($q);
$taskEmployees = array();
while($taskEmployee = mysql_fetch_assoc($q)) {
$taskEmployees[$taskEmployee['emp_id']] = $taskEmployee;
}
$empIds = array_keys($taskEmployees);
$tasks = array();
if(!empty($empIds)) {
// this query tells mysql to return tasks of assigned users
$q = "SELECT * FROM task WHERE t_assign_to IN (".implode(',', $empIds).")";
$q = mysql_query($q) or die(mysql_error());
// generating array of tasks associated by emp_id (t_assign_to field)
while($task = mysql_fetch_assoc($q)) {
$tasks[] = array(
'task' => $task,
'task_employee' => $taskEmployees[$task['t_assign_to']]
);
}
}
echo json_encode(array(
'status' => (empty($empIds))? 'not found' : 'success',
'count' => sizeof($tasks),
'data' => $tasks
));
在JS中，你会像这样使用它们（这是为了不合并字段名）：
for(var r in records) {
var record = records[r];
console.log(record.task, record.taks_employee);
}
您不必使用联接。您可以在一个查询中从两个表中进行选择。
<?php
session_start();
include('dbconfig.php');
if(empty($_SESSION['email'])){
header('Location:login.php');
}
$dapartment = mysql_real_escape_string($_POST['department']);
$sql = mysql_query("SELECT * FROM task_employee, task WHERE task_employee.emp_designation='$dapartment' AND task_employee.t_assign_to = task.emp_id");
if(false == $sql){
die('Invalid query: ' . mysql_error());
}
$count = mysql_num_rows($sql);
$data = [];
while($row=mysql_fetch_assoc($sql)){
$data[] = $row;
}
if (count($data) <= 0){
$return = array('status'=>'not found','count'=>$count,'data'=>$data);
}
else{
$return = array('status'=>'success','count'=>$count,'data'=>$data);
}
echo json_encode($return);
?>