Ho can I get the query results based on a specific cell of a table?

General PHP Help
#1

now, I have an advanced search which can give my intended results when a user is selected from the form. So, the results are just for one person in the table. I want to get the same results for all users so that each user should be appeared in a separate row! Here is my code:

<?php

if(isset($_POST['submit'])) {
global $db;
$sdate = $_POST['sdate'];
$edate = $_POST['edate'];
$user = $_POST['user'];

$search_query = "SELECT C.COST, K.DEL_INDEX, K.PROJECT_NAME, 
K.PROJECT_MANAGER, K.APP_NUMBER, K.APP_STATUS, K.DEL_DELEGATE_DATE,
K.DEL_INIT_DATE, K.DEL_FINISH_DATE, K.DEL_TASK_DUE_DATE, 
K.DEL_THREAD_STATUS, K.APP_TITLE, K.APP_TAS_TITLE,
K.USR_UID, K.USER FROM KPI_REPORT   AS K LEFT JOIN COST_USERS AS C ON 
K.USR_UID = C.USR_UID WHERE ";

if(isset($sdate)) {
  $search_query .= " K.DEL_DELEGATE_DATE >= '$sdate'";
 }

if(!empty($edate)) {
 $search_query .= "AND K.DEL_FINISH_DATE <= '$edate'";
}

if(isset($user)){

$search_query .= "AND K.USER LIKE '%".$user."%'";

}

$results = mysqli_query($db, $search_query);

$num_rows = mysqli_num_rows($results);

}
?>

<form name="search" method="post" action="<?php PATH; ?>user-kpi.php">


<input type="text" name="sdate" id="sdate" />
<span id="sdate"></span>

<input type="text" name="edate" id="edate" />
<span id="edate"></span>

<select  name="user" ">
<option value="">---username?---</option>
<?php
global $db;
$run = mysqli_query($db, "SELECT DISTINCT USER FROM KPI_REPORT ORDER BY 
USER ASC");
while($pr_name = mysqli_fetch_array($run)) { 
 ?>
<option value="<?php echo $pr_name['USER']; ?>"><?php echo $pr_name['USER']; ?> 
</option>    
<?php
} 
?>
</select> 

<input type="submit" name="submit" value="جست و جو" />
<?php 
if(empty($num_rows)) {

}else{
echo  '';} ?>
</form>
<table style="width:100%; direction:rtl; color: purple" align="center" width="100%" bgcolor="pink" >

<tr style="border: 1px solid black;" >
<th style="border:1px solid black; padding: 15px; text-align: center;">username</th>
<th style="border:1px solid black; padding: 15px; text-align: center;">delay</th>
<th style="border:1px solid black; padding: 15px; text-align: center;">efficiency</th>
<th style="border:1px solid black; padding: 15px; text-align: center;">cost or save</th>
<th style="border:1px solid black; padding: 15px; text-align: center;">number of tasks</th>
</tr>

<tr align="center" >
<?php if(isset($results) ) {
$sum_planning_time = 0;
$sum_actual_time = 0;
$sum_planning_cost = 0;
$sum_actual_cost = 0;
while($row = mysqli_fetch_array($results)) {
    $planning_time = strtotime($row['DEL_TASK_DUE_DATE']) - strtotime($row['DEL_DELEGATE_DATE']);
    $actual_time = strtotime($row['DEL_FINISH_DATE']) - strtotime($row['DEL_INIT_DATE']);
    $planning_cost = ($planning_time/3600/3) * ($row['COST']);
    $actual_cost = ($actual_time/3600/3) * ($row['COST']);
    $sum_planning_time += $planning_time;
    $sum_actual_time += $actual_time;
 $sum_planning_cost += $planning_cost;
    $sum_actual_cost += $actual_cost;
}
$delay_hour = round(($sum_actual_time - $sum_planning_time)/3600/3, 0);
$delay_cost = round(($sum_actual_cost - $sum_planning_cost), 0);
$delay_seconds = ($delay_hour * 3600);
$efficiency = round($sum_planning_time/($delay_seconds + $sum_planning_time), 2);
    ?>
<td align="center" style="border: 1px solid black;"> <?php echo $user; ?></td>
<td align="center" style="border: 1px solid black;"> <?php echo $delay_hour; ?></td>
<td align="center" style="border: 1px solid black;"> <?php echo $efficiency; ?></td>
<td align="center" style="border: 1px solid black; direction: ltr"> <?php echo number_format($delay_cost); ?></td>
<td align="center" style="border: 1px solid black;"> <?php echo $num_rows; ?></td>

</tr>
<?php       




}else {
    echo "<h1 >Please Search!</h1>";
}


?>

As I mentioned that, my intention is that each user should be appeared in a separate row. I would appreciate if you could help me.

#2

Does anyone know? Please help me

#3

what isn’t working? I see a lot of code that looks like it isn’t needed, but nothing that indicates an issue.

1 Like
#4

Thanks for your reply,
It works, if you run this code, it just show the information for selected user.
I want to show the same information for all of users in a table. But, I do not know it is possible or not. If yes, how can I do that?

#5

And that is what this should do, correct?

1 Like
#6

Thanks again, no my intention is other thing. Let me put it this way
In the table, the first column is username. Is it possible to be selected users in the first column? for example, in the first row the username should be x, in the second row would be y and …
And every row should be based on the first column. because, I want to compare all users in a glimpse>
Thanks in advance

#7

Not relevant. What query should populate the list of users? Because the query I posted is what should be populating the drop down list.

#8

The query is OK. how can I have the same results for all of users to show in a table or chart?
For current code, I just see for one user. how can I have the same result for all of users in separate rows?

#9

First I need to understand where that “one user” is coming from. If it isn’t the other query, that means it is from this block:

And if that is the block, you are doing a like that will prevent other users that don’t match the search criteria.

And use prepared statements

1 Like
#10

First of all, again thanks for your time.
Yes, user is come from the above block. Actually, this is an advanced search based on the selected user. When client selects user from the Dropdown, the results will be appeared in a table.
But, my intention is that, the same result should be appeared in various rows of the table so that the result of each row must be dependent on the user who is appeared in table rows. Also, when I change the loop, I see irrelative information. I don’t know how to meet my goal! I want to compare users in the table!
If I remove $user, again the result remains unchanged. I changed the code in this way.

 if(isset($_POST['submit'])) {
global $db;
$sdate = $_POST['sdate'];
$edate = $_POST['edate'];
$search_query = "SELECT C.COST, K.DEL_INDEX, K.PROJECT_NAME, 
                 K.PROJECT_MANAGER, K.APP_NUMBER, K.APP_STATUS, 
                 K.DEL_DELEGATE_DATE,
                 K.DEL_INIT_DATE, K.DEL_FINISH_DATE, K.DEL_TASK_DUE_DATE, 
                K.DEL_THREAD_STATUS, K.APP_TITLE, K.APP_TAS_TITLE, 
                K.USR_UID, K.USER FROM KPI_REPORT   AS K LEFT JOIN 
                COST_USERS AS C ON 
                K.USR_UID = C.USR_UID WHERE "; 

 if(isset($sdate)) {
    $search_query .= " K.DEL_DELEGATE_DATE >= '$sdate'";
 }

if(!empty($edate)) {
    $search_query .= "AND K.DEL_FINISH_DATE <= '$edate'";
}
$results = mysqli_query($db, $search_query);

$num_rows = mysqli_num_rows($results);
}

Now, how can I do my intention?
I tried to share 2 tables of database and php file for you to would be tangible, but It is not possible in the forum.
Thanks in advance.

#11

PM me the files. How many users should you see?

1 Like
#12

Thank you very much.
There are various users (some of them in the table of database have many rows, However I want the users would be distinct. This is the first SQL (table in database)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 
--

-- --------------------------------------------------------

--
-- Table structure for table `kpi_report`
--

CREATE TABLE `kpi_report` (
  `app_uid` text NOT NULL,
  `del_index` int(11) NOT NULL,
  `project_name` text NOT NULL,
  `project_manager` text NOT NULL,
  `app_number` text NOT NULL,
  `app_status` text NOT NULL,
  `del_delegate_date` datetime NOT NULL,
  `del_init_date` datetime NOT NULL,
  `del_finish_date` datetime NOT NULL,
  `del_task_due_date` datetime NOT NULL,
  `del_thread_status` text NOT NULL,
  `app_title` text NOT NULL,
  `app_tas_title` text NOT NULL,
  `USR_UID` text NOT NULL,
  `user` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `kpi_report`
--

INSERT INTO `kpi_report` (`app_uid`, `del_index`, `project_name`, `project_manager`, `app_number`, `app_status`, `del_delegate_date`, `del_init_date`, `del_finish_date`, `del_task_due_date`, `del_thread_status`, `app_title`, `app_tas_title`, `USR_UID`, `user`) VALUES
('1081804655cd90b07639250051414780', 1, 'تست 3', 'جعفر تیموری (104)', '203', 'TO_DO', '2019-05-13 06:13:27', '2019-05-13 06:13:34', '2019-05-15 21:22:45', '2019-05-13 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'ارائه تقاضای  کمیسیون', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('1081804655cd90b07639250051414780', 2, 'تست 3', 'جعفر تیموری (104)', '203', 'TO_DO', '2019-05-15 21:22:45', '2019-05-16 08:20:26', '2019-05-16 08:20:45', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'انتخاب نماینده دفتر فنی', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1081804655cd90b07639250051414780', 3, 'تست 3', 'جعفر تیموری (104)', '203', 'TO_DO', '2019-05-16 08:20:45', '2019-05-16 08:20:51', '2019-05-16 08:21:02', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'بررسی اسناد  و ارزیابی نحوه ارائه گزراش', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1081804655cd90b07639250051414780', 4, 'تست 3', 'جعفر تیموری (104)', '203', 'TO_DO', '2019-05-16 08:21:02', '2019-05-16 08:21:08', '2019-05-16 08:27:49', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'تعیین نفرات کمیسیون و درخواست باز کردن دسترسی  برای افراد', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 1, '', '', '215', 'COMPLETED', '2019-05-22 08:11:22', '2019-05-22 08:12:06', '2019-05-22 08:15:57', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'ارائه تقاضای  کمیسیون', '5395654235c792cfff01e49037573686', 'کارگشا علی'),
('1813050275ce5042a3bc829010165249', 2, '', '', '215', 'COMPLETED', '2019-05-22 08:15:57', '2019-05-22 08:16:21', '2019-05-22 08:16:39', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'انتخاب نماینده دفتر فنی', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 3, '', '', '215', 'COMPLETED', '2019-05-22 08:16:39', '2019-05-22 08:16:45', '2019-05-22 08:17:21', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'بررسی اسناد  و ارزیابی نحوه ارائه گزراش', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 4, '', '', '215', 'COMPLETED', '2019-05-22 08:17:21', '2019-05-22 08:17:27', '2019-05-22 08:18:02', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'تعیین نفرات کمیسیون و درخواست باز کردن دسترسی  برای افراد', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 5, '', '', '215', 'COMPLETED', '2019-05-22 08:18:02', '2019-05-22 08:18:28', '2019-05-22 08:18:48', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'باز کردن دسترسی برای افراد تعیین شده', '5357940375c7b744ceb8709067871521', 'زنگنه خانم'),
('1813050275ce5042a3bc829010165249', 6, '', '', '215', 'COMPLETED', '2019-05-22 08:18:48', '2019-05-22 08:19:17', '2019-05-22 08:19:29', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'برگزاری کمیسیون', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 7, '', '', '215', 'COMPLETED', '2019-05-22 08:19:29', '2019-05-22 08:19:35', '2019-05-22 08:19:59', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'تهیه صورت جلسه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('1813050275ce5042a3bc829010165249', 8, '', '', '215', 'COMPLETED', '2019-05-22 08:19:59', '2019-05-22 08:21:06', '2019-05-22 08:21:16', '2019-05-22 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'ارائه گزاشات و اسناد نهایی', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2285103625cdd1d1f255c76087805124', 1, 'تست 2', 'جعفر تیموری (104)', '206', 'TO_DO', '2019-05-16 08:19:43', '2019-05-16 08:19:49', '2019-05-16 08:20:02', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'ارائه تقاضای  کمیسیون', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('2285103625cdd1d1f255c76087805124', 2, 'تست 2', 'جعفر تیموری (104)', '206', 'TO_DO', '2019-05-16 08:20:02', '2019-05-16 08:33:14', '2019-05-16 08:33:27', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'انتخاب نماینده دفتر فنی', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2285103625cdd1d1f255c76087805124', 3, 'تست 2', 'جعفر تیموری (104)', '206', 'TO_DO', '2019-05-16 08:33:27', '2019-05-16 08:33:33', '2019-05-16 08:34:03', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'بررسی اسناد  و ارزیابی نحوه ارائه گزراش', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2285103625cdd1d1f255c76087805124', 4, 'تست 2', 'جعفر تیموری (104)', '206', 'TO_DO', '2019-05-16 08:34:03', '2019-05-16 08:34:10', '2019-05-16 08:35:16', '2019-05-16 17:00:00', 'CLOSED', 'فرایند برگزاری کمیسیون', 'تعیین نفرات کمیسیون و درخواست باز کردن دسترسی  برای افراد', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2397661365d4ff88b7d0933088435487', 1, 'تست 125', 'جعفر تیموری (104)', '318', 'TO_DO', '2019-08-11 11:14:19', '2019-08-11 11:14:19', '2019-08-11 11:14:54', '2019-08-12 17:00:00', 'CLOSED', 'فرایند اجرای پروژه ها', 'تعریف پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2725756055d4ff8af540854070247027', 1, 'تست 125', 'جعفر تیموری (104)', '319', 'TO_DO', '2019-08-11 11:14:55', '2019-08-11 11:15:02', '2019-08-11 11:15:25', '2019-08-12 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تعیین مدیر پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2725756055d4ff8af540854070247027', 2, 'تست 125', 'جعفر تیموری (104)', '319', 'TO_DO', '2019-08-11 11:15:25', '2019-08-11 12:01:15', '2019-08-11 12:01:24', '2019-08-12 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'باز کردن دسترسی ها و افزودن پروژه در پورتال', '5357940375c7b744ceb8709067871521', 'زنگنه خانم'),
('2738971005d6f3a183ecf43049891796', 1, '', '', '375', 'TO_DO', '2019-09-04 04:14:16', '2019-09-04 04:14:16', '2019-09-04 04:14:28', '2019-09-04 06:01:00', 'CLOSED', 'overdue task', 'task1', '00000000000000000000000000000001', 'admin Administrator'),
('2738971005d6f3a183ecf43049891796', 2, '', '', '375', 'TO_DO', '2019-09-04 04:14:28', '2019-09-04 04:27:05', '2019-09-04 04:27:05', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2738971005d6f3a183ecf43049891796', 4, '', '', '375', 'TO_DO', '2019-09-04 04:27:18', '2019-09-04 04:29:05', '2019-09-04 04:29:05', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2738971005d6f3a183ecf43049891796', 6, '', '', '375', 'TO_DO', '2019-09-04 04:29:16', '2019-09-04 04:31:05', '2019-09-04 04:31:05', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2738971005d6f3a183ecf43049891796', 8, '', '', '375', 'TO_DO', '2019-09-04 04:31:15', '2019-09-04 04:43:05', '2019-09-04 04:43:06', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2738971005d6f3a183ecf43049891796', 10, '', '', '375', 'TO_DO', '2019-09-04 04:43:17', '2019-09-10 04:56:06', '2019-09-10 04:56:09', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2738971005d6f3a183ecf43049891796', 12, '', '', '375', 'TO_DO', '2019-09-10 04:56:34', '2019-09-12 04:43:21', '2019-09-12 04:43:23', '2019-09-10 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('2775867385cd909d57e8ce4064865459', 1, 'تست 3', 'جعفر تیموری (104)', '202', 'TO_DO', '2019-05-13 06:08:21', '2019-05-13 06:08:32', '2019-05-13 06:09:47', '2019-05-13 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تعیین مدیر پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('2775867385cd909d57e8ce4064865459', 2, 'تست 3', 'جعفر تیموری (104)', '202', 'TO_DO', '2019-05-13 06:09:47', '2019-05-13 06:10:17', '2019-05-13 06:11:03', '2019-05-13 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'باز کردن دسترسی ها و افزودن پروژه در پورتال', '5357940375c7b744ceb8709067871521', 'زنگنه خانم'),
('2775867385cd909d57e8ce4064865459', 3, 'تست 3', 'جعفر تیموری (104)', '202', 'TO_DO', '2019-05-13 06:11:03', '2019-05-13 06:12:36', '2019-05-13 06:13:01', '2019-05-15 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'شروع مطالعات میدانی', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('2775867385cd909d57e8ce4064865459', 4, 'تست 3', 'جعفر تیموری (104)', '202', 'TO_DO', '2019-05-13 06:13:01', '2019-05-13 06:13:08', '2019-05-13 06:13:27', '2019-05-13 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تهیه و تدوین گزارش درخواست کارفرما و گزارش بازدید', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('4211789025d9863783ad316073137057', 1, '', '', '382', 'TO_DO', '2019-10-05 09:33:44', '2019-10-05 09:33:44', '2019-10-05 09:34:17', '2019-10-06 09:33:44', 'CLOSED', 'فرایند آموزش', 'درخواست نیاز آموزش', '2320159045d934a8a09d442077859470', 'مظفری2 محمد'),
('4288243895d6687ac2d8491071048201', 1, '', '', '359', 'TO_DO', '2019-08-28 13:54:52', '2019-08-28 13:54:52', '2019-08-28 13:55:18', '2019-08-29 13:24:52', 'CLOSED', 'Execute once per day', 'dummy task to start endless loop', '00000000000000000000000000000001', 'admin Administrator'),
('4288243895d6687ac2d8491071048201', 2, '', '', '359', 'TO_DO', '2019-08-28 13:55:18', '2019-09-04 04:06:25', '2019-09-04 04:06:25', '2019-08-29 13:25:18', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 4, '', '', '359', 'TO_DO', '2019-09-04 04:06:26', '2019-09-04 04:27:26', '2019-09-04 04:27:27', '2019-09-04 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 6, '', '', '359', 'TO_DO', '2019-09-04 04:27:27', '2019-09-04 04:29:26', '2019-09-04 04:29:26', '2019-09-04 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 8, '', '', '359', 'TO_DO', '2019-09-04 04:29:27', '2019-09-04 04:31:24', '2019-09-04 04:31:25', '2019-09-04 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 10, '', '', '359', 'TO_DO', '2019-09-04 04:31:25', '2019-09-04 04:43:27', '2019-09-04 04:43:28', '2019-09-04 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 12, '', '', '359', 'TO_DO', '2019-09-04 04:43:28', '2019-09-10 04:56:45', '2019-09-10 04:56:45', '2019-09-04 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4288243895d6687ac2d8491071048201', 14, '', '', '359', 'TO_DO', '2019-09-10 04:56:46', '2019-09-12 04:44:00', '2019-09-12 04:44:00', '2019-09-10 23:00:00', 'CLOSED', 'Execute once per day', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('4470140545d062aee954830015164289', 1, '', '104', '315', 'TO_DO', '2019-06-16 11:41:34', '2019-06-16 11:41:34', '2019-06-16 12:13:59', '2019-06-17 17:00:00', 'CLOSED', 'sub_process2', 'Task 1', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('4470140545d062aee954830015164289', 2, '', '104', '315', 'TO_DO', '2019-06-16 12:13:59', '2019-06-16 12:17:29', '2019-06-16 12:22:18', '2019-06-17 17:00:00', 'CLOSED', 'sub_process2', 'Task 2', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('5667758375d5eb7c32d07e5048608040', 1, '', '', '349', 'TO_DO', '2019-08-22 15:41:55', '2019-08-22 15:41:55', '2019-08-22 15:42:10', '2019-08-23 15:11:55', 'CLOSED', 'process for overdue test', 'Task 1', '00000000000000000000000000000001', 'admin Administrator'),
('5731040535d500326aac378074627208', 1, 'پروژه ساخت پلاگین', 'خانم زنگنه (103)', '320', 'TO_DO', '2019-08-11 11:59:34', '2019-08-11 11:59:34', '2019-08-11 12:00:29', '2019-08-12 17:00:00', 'CLOSED', 'فرایند اجرای پروژه ها', 'تعریف پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('6182427835d73c8ad4cfd09029460664', 1, 'پروژه قطر', 'خانم زنگنه (103)', '376', 'TO_DO', '2019-09-07 15:11:41', '2019-09-07 15:11:41', '2019-09-07 15:12:34', '2019-09-08 14:41:41', 'CLOSED', 'فرایند اجرای پروژه ها', 'تعریف پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('6300133535d9863ef7b7a70024069371', 1, '', '', '383', 'TO_DO', '2019-10-05 09:35:43', '2019-10-05 09:35:43', '2019-10-05 09:36:00', '2019-10-06 09:35:43', 'CLOSED', 'فرایند آموزش', 'تشریح برنامه آموزش', '00000000000000000000000000000001', 'admin Administrator'),
('6804571485cd884271badd4047402259', 1, 'تست 2', 'جعفر تیموری (104)', '200', 'TO_DO', '2019-05-12 20:37:59', '2019-05-12 20:38:07', '2019-05-12 20:38:23', '2019-05-13 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تعیین مدیر پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('6804571485cd884271badd4047402259', 2, 'تست 2', 'جعفر تیموری (104)', '200', 'TO_DO', '2019-05-12 20:38:23', '2019-05-15 21:18:09', '2019-05-15 21:18:46', '2019-05-13 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'باز کردن دسترسی ها و افزودن پروژه در پورتال', '5357940375c7b744ceb8709067871521', 'زنگنه خانم'),
('6804571485cd884271badd4047402259', 3, 'تست 2', 'جعفر تیموری (104)', '200', 'TO_DO', '2019-05-15 21:18:46', '2019-05-16 08:18:59', '2019-05-16 08:19:17', '2019-05-20 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'شروع مطالعات میدانی', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('6804571485cd884271badd4047402259', 4, 'تست 2', 'جعفر تیموری (104)', '200', 'TO_DO', '2019-05-16 08:19:17', '2019-05-16 08:19:24', '2019-05-16 08:19:42', '2019-05-16 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تهیه و تدوین گزارش درخواست کارفرما و گزارش بازدید', '1892713345c792cd02c2362080079236', 'تیموری جعفر'),
('8931488785d5d6574ac3ee7062314413', 1, '', '', '345', 'TO_DO', '2019-08-21 15:38:29', '2019-08-21 15:38:29', '2019-08-21 15:38:44', '2019-08-22 15:08:29', 'CLOSED', 'process for overdue test', 'Task 1', '00000000000000000000000000000001', 'admin Administrator'),
('9371096255d50035d8e7a24059998641', 1, 'پروژه ساخت پلاگین', 'خانم زنگنه (103)', '321', 'TO_DO', '2019-08-11 12:00:29', '2019-08-11 12:00:36', '2019-08-11 12:00:48', '2019-08-12 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'تعیین مدیر پروژه', '9442379015c792c3679d383055839089', 'تیموری مهندس'),
('9371096255d50035d8e7a24059998641', 2, 'پروژه ساخت پلاگین', 'خانم زنگنه (103)', '321', 'TO_DO', '2019-08-11 12:00:48', '2019-08-11 12:01:30', '2019-08-11 12:01:39', '2019-08-12 17:00:00', 'CLOSED', 'فرایند تعریف پروژه', 'باز کردن دسترسی ها و افزودن پروژه در پورتال', '5357940375c7b744ceb8709067871521', 'زنگنه خانم'),
('9608335935d6e335d53ac52052605452', 1, '', '', '374', 'TO_DO', '2019-09-03 09:33:18', '2019-09-03 09:33:18', '2019-09-03 09:33:40', '2019-09-03 09:34:18', 'CLOSED', 'overdue task', 'task1', '00000000000000000000000000000001', 'admin Administrator'),
('9608335935d6e335d53ac52052605452', 2, '', '', '374', 'TO_DO', '2019-09-03 09:33:42', '2019-09-04 04:06:02', '2019-09-04 04:06:07', '2019-09-04 09:03:42', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 4, '', '', '374', 'TO_DO', '2019-09-04 04:06:24', '2019-09-04 04:27:18', '2019-09-04 04:27:18', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 6, '', '', '374', 'TO_DO', '2019-09-04 04:27:26', '2019-09-04 04:29:16', '2019-09-04 04:29:16', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 8, '', '', '374', 'TO_DO', '2019-09-04 04:29:26', '2019-09-04 04:31:15', '2019-09-04 04:31:15', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 10, '', '', '374', 'TO_DO', '2019-09-04 04:31:24', '2019-09-04 04:43:18', '2019-09-04 04:43:18', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 12, '', '', '374', 'TO_DO', '2019-09-04 04:43:27', '2019-09-10 04:56:36', '2019-09-10 04:56:36', '2019-09-04 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', ''),
('9608335935d6e335d53ac52052605452', 14, '', '', '374', 'TO_DO', '2019-09-10 04:56:45', '2019-09-12 04:43:51', '2019-09-12 04:43:51', '2019-09-10 23:00:00', 'CLOSED', 'overdue task', 'INTERMEDIATE-CATCH-TIMER-EVENT', '', '');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Another table is this here:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 
--

-- --------------------------------------------------------

--
-- Table structure for table `cost_users`
--

CREATE TABLE `cost_users` (
  `id` int(11) NOT NULL,
  `USR_UID` text NOT NULL,
  `user` text NOT NULL,
  `cost` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `cost_users`
--

INSERT INTO `cost_users` (`id`, `USR_UID`, `user`, `cost`) VALUES
(14, '1892713345c792cd02c2362080079236', 'تیموری جعفر', '300000'),
(15, '9442379015c792c3679d383055839089', 'تیموری مهندس', '500000'),
(16, '5357940375c7b744ceb8709067871521', 'زنگنه خانم', '250000'),
(17, '5395654235c792cfff01e49037573686', 'کارگشا علی', '278000'),
(18, '00000000000000000000000000000001', 'admin Administrator', '500000'),
(19, '2320159045d934a8a09d442077859470', 'مظفری محمد', '2000');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cost_users`
--
ALTER TABLE `cost_users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cost_users`
--
ALTER TABLE `cost_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here is my php code:

<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="<?php echo PATH; ?>styles.css">
<link type="text/css" rel="stylesheet" href="<?php echo PATH; ?>datepicker/dist/css/bootstrap/zebra_datepicker.css" />
<link type="text/css" rel="stylesheet" href="<?php echo PATH; ?>datepicker/dist/css/default/zebra_datepicker.css" />
<link type="text/css" rel="stylesheet" href="<?php echo PATH; ?>datepicker/dist/css/metallic/zebra_datepicker.css" />
<script type="text/javascript" src="<?php echo PATH; ?>jalali/js/jquery-1.10.1.min.js"></script>

<script src="<?php echo PATH; ?>datepicker/dist/zebra_datepicker.min.js"></script>

</head>
<body>
<?php
if(isset($_POST['submit'])) {
	global $db;
	$sdate = $_POST['sdate'];
	
	
	$edate = $_POST['edate'];
	

	
	
	$search_query = "SELECT C.COST, K.DEL_INDEX, K.PROJECT_NAME, K.PROJECT_MANAGER, K.APP_NUMBER, K.APP_STATUS, K.DEL_DELEGATE_DATE, K.DEL_INIT_DATE, K.DEL_FINISH_DATE, K.DEL_TASK_DUE_DATE, K.DEL_THREAD_STATUS, K.APP_TITLE, K.APP_TAS_TITLE, K.USR_UID, K.USER FROM KPI_REPORT AS K LEFT JOIN COST_USERS AS C ON K.USR_UID = C.USR_UID WHERE ";
	
	
	
	if(isset($sdate)) {
	 $search_query .= " K.DEL_DELEGATE_DATE >= '$sdate'";
 }
 
 if(!empty($edate)) {
	 $search_query .= "AND K.DEL_FINISH_DATE <= '$edate'";
 }


$results = mysqli_query($db, $search_query);

$num_rows = mysqli_num_rows($results);

}


?>


	
 
<div class="advanced-search" style=" background-color: grey; color: orange; padding: 10px; " >
<h3 style="direction: ltr;">USER-KPI</h3>

<form name="search" method="post" action="<?php PATH; ?>user-kpi.php">


<input type="text" name="sdate" id="sdate" placeholder="Deleagte Date"  autocomplete="off" style="width: 250px; margin-left: 15px;"/>
<span id="sdate"></span>




<input type="text" name="edate" id="edate" placeholder="Overdue Date"  autocomplete="off" style="width: 250px; margin-left: 15px;"/>
<span id="edate"></span>

<<br> <br>


<input type="submit" name="submit" value="Search" style="background-color: green; cursor: pointer; padding: 5px 20px 5px 20px; color: pink; font-weight: bold; font-size:15px; margin-left: 100px;"   />

<?php 
if(empty($num_rows)) {
	
}else{
echo  '';} ?>
</form>
</div>


<!-- Script -->
<script>

$('#sdate').Zebra_DatePicker();
$('#edate').Zebra_DatePicker();

</script>

<table style="width:100%; color: purple" align="center" width="100%" bgcolor="pink" >
	
	<tr style="border: 1px solid black;" >
	<th style="border:1px solid black; padding: 15px; text-align: center;">UserName</th>
	<th style="border:1px solid black; padding: 15px; text-align: center;">Delay Based on Hour</th>
	<th style="border:1px solid black; padding: 15px; text-align: center;">Efficinecy</th>
	<th style="border:1px solid black; padding: 15px; text-align: center;">Cost / Save</th>
	<th style="border:1px solid black; padding: 15px; text-align: center;">Number of Done Tasks</th>
	</tr>
	
	<tr align="center" style="border: 1px solid black;">
	<?php if(isset($results) ) {
	$sum_planning_time = 0;
	$sum_actual_time = 0;
	$sum_planning_cost = 0;
	$sum_actual_cost = 0;
	while($row = mysqli_fetch_array($results)) {
		$planning_time = strtotime($row['DEL_TASK_DUE_DATE']) - strtotime($row['DEL_DELEGATE_DATE']);
		$actual_time = strtotime($row['DEL_FINISH_DATE']) - strtotime($row['DEL_INIT_DATE']);
		$planning_cost = ($planning_time/3600/3) * ($row['COST']);
		$actual_cost = ($actual_time/3600/3) * ($row['COST']);
		$sum_planning_time += $planning_time;
		$sum_actual_time += $actual_time;
		$sum_planning_cost += $planning_cost;
		$sum_actual_cost += $actual_cost;
	}
	$delay_hour = round(($sum_actual_time - $sum_planning_time)/3600/3, 0);
	$delay_cost = round(($sum_actual_cost - $sum_planning_cost), 0);
	$delay_seconds = ($delay_hour * 3600);
	$efficiency = round($sum_planning_time/($delay_seconds + $sum_planning_time), 2);
		?>
	<td align="center" style="border: 1px solid black;"> <?php echo $user; ?></td>
	<td align="center" style="border: 1px solid black;"> <?php echo $delay_hour; ?></td>
	<td align="center" style="border: 1px solid black;"> <?php echo $efficiency; ?></td>
	<td align="center" style="border: 1px solid black; direction: ltr"> <?php echo number_format($delay_cost); ?></td>
	<td align="center" style="border: 1px solid black;"> <?php echo $num_rows; ?></td>

	</tr>
	<?php		
	

		
	
	}else {
		echo "<h1 style=' color: red; text-align: center; background: yellow; padding: 5px;' >Just Search!</h1>";
	}
	

	?>
	
	
<?php if(isset($num_rows) && $num_rows == 0) {

echo "<h1 style='color: red; text-align: center; background: yellow; padding: 5px;' >Sorry! your intended result does not exist!</h1>";

}
?>






</body>
</html>

Sorry for my long code. Maybe that would be more tangible in this way.
I want to have each distinct user in a row of table, and the result must be based on the same user.
I tried to change the loop, but the result is not compatible with my intention.
Thanks in advance for your valuable time
Kind Regards

#13

I can try and take a look after work, depending when I get out. I have a release tomorrow, so it may be a long night.

1 Like
#14

You would change the sql query so that it calculates the sum of the planning_time and actual_time (see the mysql SUM() and TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) functions) and also add GROUP BY usr_uid to the query. This will give you one row in the result set for each user. You would also get the display name from each row in the result set (it is/was coming from the search $name variable) and you would calculate the cost values from the sum of the planning time, sum of the actual time, and the cost values from each row in the result set.

1 Like
#15

Thank you very much Dear experts for sharing your valuable knowledge with this topic.
My problem has been solved. Words can not describe your kindness!
Warm Regards

Sponsor our Newsletter | Privacy Policy | Terms of Service