title: create dynamic reports using core php and mysql
body: <p>Currently we are doing a project to manage projects and our client need a dynamic reports which he can get Time Sheet Project wise.</p>
  
  <p>Let me explain the situation.</p>
  
  <p>3 database tables.</p>
  
  <p>Project table, user table and task table.</p>
  
  <p>This is the sample reports format with a sample data, but structure is the same.</p>
  
  <p><img src="https://i.stack.imgur.com/BBhQe.png" alt="Sample report structure"></p>
  
  <p>Multiple users can work on a single project and user can work on multiple projects at the same time.</p>
  
  <p>This is what i have tried to up to now.</p>
  
  <pre><code>&lt;?php
  
  /////get projects which are not finished/////
  $sql_project = "SELECT * FROM project where P_Status!='finished'";
  $result_project = mysql_query($sql_project) or die("Error in query: $sql_project.".mysql_error());
  
  ////////get users from the users table
  $sql_getUsers="select * from tbl_user where status='Active' order by U_Name";
  $result_getUsers=mysql_query($sql_getUsers);
  
      ?&gt;
   &lt;tr&gt;
      &lt;td&gt;Project&lt;/td&gt;
      &lt;td&gt;Client&lt;/td&gt;
      &lt;td&gt;Estimated Cost&lt;/td&gt;
      &lt;td &gt;Total spent to date &lt;/td&gt;
      &lt;?php
      while($row_getUsers=mysql_fetch_array($result_getUsers))
      {
      ?&gt;
      &lt;td &gt;&lt;?php echo $row_getUsers['U_Name']; ?&gt;&lt;/td&gt;
      &lt;?php
      }
      ?&gt;
  
  &lt;/tr&gt;
  &lt;?php
  
  while($row_project=mysql_fetch_array($result_project))
  {
  $individualProject=trim($row_project['P_Title']);
  
  $sql_tasks = "Select SUM(Time)as totalTime from tbl_txn where project like '%$individualProject%' and Date&lt;='$endDate'";
  $result_sql = mysql_query($sql_tasks) or die("Error in query: $sql_file_no.".mysql_error());
  
  
    ?&gt;
  &lt;?php
  while($row_sql_tasks=mysql_fetch_array($result_sql))
  {
  
  ?&gt;
  
  
  &lt;tr&gt;
      &lt;td&gt;&lt;?php echo $row_project['P_Title']; ?&gt;&lt;/td&gt;
      &lt;td &gt;&lt;?php echo $row_project['P_Client']; ?&gt;&lt;/td&gt;
      &lt;td &gt;&lt;?php echo $row_project['estimatedCost']; ?&gt;&lt;/td&gt;
      &lt;td &gt;&lt;?php echo round($row_sql_tasks['totalTime']); ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;?php echo round($row_getIndiUserTime['Time']); ?&gt;&lt;/td&gt;
   &lt;?php
  
  
      }
  }
      ?&gt;
  </code></pre>
  
  <p>I have managed to display the users, but i have no idea how to display users have work on each project?.<br>
  on project A, how much time User 1 has spent and user 2 like wise.
  hope i have clearly mentioned about the problem.
  can anyone help me on this please?</p>
  
  <p>Thanks in advance</p>