Databases
StackOverflow2013
Postsdbo
POHow to create PHP Mysql daily report without duplicating agent name?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to create PHP Mysql daily report without duplicating agent name?
    text
    copied!<p><strong>Table Structure bill</strong></p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE IF NOT EXISTS `bill` ( `id` int(11) NOT NULL AUTO_INCREMENT, `agent_id` int(11) NOT NULL, `date` date NOT NULL, `amount` int(100) NOT NULL, `cheque` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -- Dumping data for table `bill` -- INSERT INTO `bill` (`id`, `agent_id`, `date`, `amount`, `cheque`) VALUES (1, 1, '2013-01-01', 10, 15), (2, 1, '2013-01-01', 10, 0), (3, 2, '2013-01-02', 10, 0), (4, 1, '2013-01-03', 12, 0), (5, 3, '2013-01-04', 100, 0), (6, 2, '2013-01-05', 14, 0), (7, 5, '2013-01-05', 18, 0), (8, 7, '2013-01-05', 22, 0), (9, 6, '2013-01-04', 19, 0), (10, 6, '2013-01-06', 20, 0), (11, 5, '2013-01-07', 21, 0), (12, 6, '2013-01-08', 11, 0), (13, 7, '2013-02-09', 19, 0), (14, 6, '2013-01-10', 150, 0), (15, 1, '2012-12-31', 100, 0), (16, 1, '2012-01-11', 500, 20), (17, 1, '2013-02-01', 210, 0), (18, 1, '2013-02-18', 100, 0), (19, 6, '2013-01-31', 100, 0), (20, 3, '2013-01-11', 250, 50); </code></pre> <p><strong>Table Structure bill_agents</strong></p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE IF NOT EXISTS `bill_agents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(11) NOT NULL, `target` int(11) NOT NULL, `join_date` date NOT NULL, `resign_date` date NOT NULL, `hide` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; -- -- Dumping data for table `bill_agents` -- INSERT INTO `bill_agents` (`id`, `name`, `target`, `join_date`, `resign_date`, `hide`) VALUES (1, 'Agent 1', 10, '2013-01-01', '0000-00-00', 0), (2, 'Agent 2', 5000, '2013-01-01', '0000-00-00', 0), (3, 'Agent 3', 5000, '2013-01-01', '0000-00-00', 0), (4, 'Agent 4', 5000, '2013-01-03', '2013-01-06', 0), (5, 'Agent 5', 5000, '2013-01-01', '0000-00-00', 0), (6, 'Agent 6', 5000, '2013-01-02', '0000-00-00', 0), (7, 'Agent 7', 5000, '2013-01-02', '0000-00-00', 1); </code></pre> <p>My Current result: <a href="https://i.stack.imgur.com/Dv4iU.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Dv4iU.jpg" alt="current result"></a></p> <p>( 1-31 are the dates) with following code </p> <pre class="lang-php prettyprint-override"><code>&lt;table width="100%" border="1" cellspacing="4" cellpadding="1"&gt; &lt;tr&gt; &lt;td&gt;&amp;nbsp;&lt;/td&gt; &lt;?php for ($i=01; $i&lt;=31; $i++) {?&gt; &lt;td&gt;&lt;?php echo $i; ?&gt;&lt;/td&gt; &lt;?php } ?&gt; &lt;td&gt;Total&lt;/td&gt; &lt;/tr&gt; &lt;?php $query4 = "SELECT DISTINCT bill.agent_id, bill.date, SUM(bill.amount + bill.cheque) AS total, bill_agents.id,bill_agents.name ". "FROM bill, bill_agents ". "WHERE bill.agent_id = bill_agents.id AND YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) ". "GROUP BY bill.agent_id , DATE(bill.date) ". "ORDER BY bill.date ASC "; $result4 = mysql_query($query4) or die('Error, query failed1'); if (mysql_num_rows($result4)&gt;0){ mysql_data_seek($result4, 0); ?&gt; &lt;?php $total_1 = 0; while($row4 = mysql_fetch_array($result4, MYSQL_ASSOC)){?&gt; &lt;?php $date = $row4['date']; $var = $date; $date = date("d-m-Y", strtotime($var) ); $date=substr($date, 0, -8); echo $date; ?&gt; &lt;tr&gt; &lt;td&gt;&lt;?php echo $row4['name']; ?&gt;&lt;/td&gt; &lt;?php for ($i=01; $i&lt;=31; $i++) {?&gt; &lt;td&gt;&lt;?php if ($date == $i) echo $row4['total']; ?&gt;&lt;/td&gt; &lt;?php } ?&gt; &lt;td&gt;&lt;/td&gt; &lt;/tr&gt; &lt;?php } } ?&gt; &lt;tr&gt; &lt;td colspan="31"&gt;&lt;/td&gt; &lt;td&gt;Total&lt;/td&gt; &lt;td&gt;&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; </code></pre> <p>What I am trying to create is i don't want to duplicate agents name <strong>if more than one entry in database it should come under the current date</strong> only and grand total also should be . how is is it possible sql query ? or do i need to create with other code?</p> <p>Here is an example what i am trying to create <a href="https://i.stack.imgur.com/N13ae.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/N13ae.jpg" alt="expected result"></a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload