I have a table I would like to display data based on other column data on the same table. Below is my code as I am now stuck on what to do.
<tbody>
<?php
$select=$pdo->prepare("select * from tbl_category order by catid desc");
$select->execute();
while($row=$select->fetch(PDO::FETCH_OBJ) ){
echo'
<tr>
<td>'.$row->catid.'</td>
<td>'.$row->category.'</td>
<td>
'.$cummunlative_debt.'
</td>
<td>'.$row->remitted.'</td>
<td>
<button id='.$row->catid.'" class="btn btn-info btnremit"><span class="glyphicon glyphicon-eye-open" style="color:#ffffff" data-toggle="tooltip" title="Pay Company"></span></button>
</td>
<td>
<a href="#.php?id='.$row->catid.'" class="btn btn-info" role="button"><span class="glyphicon glyphicon-edit" style="color:#ffffff" data-toggle="tooltip" title="Edit Category"></span></a>
</td>
<td>
<button id='.$row->catid.' class="btn btn-danger btndelete" ><span class="glyphicon glyphicon-trash" style="color:#ffffff" data-toggle="tooltip" title="Delete Category"></span></button>
</td>
</tr>
';
}
?>
</tbody>
My query
$cat=$pdo->prepare("select category_name from tbl_category");
$cat->execute();
$row=$cat->fetch(PDO::FETCH_OBJ);
$select_debts = $pdo->prepare("select sum(total_cost) as debt from tbl_stock_in where category_name='".$row->category."' group by category_name");
$select_debts->execute();
$row=$select_debts->fetch(PDO::FETCH_OBJ);
$cummunlative_debt=$row->debt;
I would also like to not hardcode the SQL like this category_name=’Pinnacle’…
Below is UI
you can see that BG and Pinnacle had the same values but on my database, BG had a record of zero. I need your help pls. Thanks.
tbl_stock_in that column called ‘table debt’
tbl_category where category is selected from to supoort the second SQL query.
Please i need your help!!
Advertisement
Answer
Having looked at this for a little while my best guess at what you are trying to do is to unify the data from both tables to display in the HTML table.
The query can potentially be modified in such a way as to allow you to draw the data from both tables and display as you wish
select
c.catid,
c.`category`,
ifnull(sum( s.`stock_in` * s.`stock_price` ),0) as `debt`,
c.`remitted`
from `tbl_category` c
left outer join `tbl_stock_in` s on s.`category_name`=c.`category`
group by c.`category`
order by catid desc;
Putting that into the context of the page I put this together as a test
<?php
error_reporting( E_ALL );
try{
$dbport = 3306;
$dbhost = 'localhost';
$dbuser = 'root';
$dbpwd = 'xxx';
$dbname = 'xxx';
$dsn = sprintf( 'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $dbhost, $dbport, $dbname );
$pdo = new PDO( $dsn, $dbuser, $dbpwd );
$sql='select
c.catid,
c.`category`,
ifnull(sum( s.`stock_in` * s.`stock_price` ),0) as `debt`,
c.`remitted`
from `tbl_category` c
left outer join `tbl_stock_in` s on s.`category_name`=c.`category`
group by c.`category`
order by catid desc;';
$stmt=$pdo->prepare( $sql );
$stmt->execute();
}catch( PDOException $e ){
echo $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8' />
<title></title>
</head>
<body>
<table>
<thead>
<tr>
<th scope='col'>ID</th>
<th scope='col'>Company</th>
<th scope='col'>Total Debt</th>
<th scope='col'>Total Remit</th>
<th scope='col'>Pay</th>
<th scope='col'>Edit</th>
<th scope='col'>delete</th>
</tr>
</thead>
<tbody>
<?php
while( $rs=$stmt->fetch(PDO::FETCH_OBJ ) ){
printf(
'<tr>
<td>%1$s</td>
<td>%2$s</td>
<td>%3$s</td>
<td>%4$s</td>
<td>
<button data-id="%1$s" class="btn btn-info btnremit" data-action="Pay">
<span class="glyphicon glyphicon-eye-open" style="color:#ffffff" data-toggle="tooltip" title="Pay Company"></span>
</button>
</td>
<td>
<button data-id="%1$s" class="btn btn-info btnedit" data-action="Edit">
<span class="glyphicon glyphicon-edit" style="color:#ffffff" data-toggle="tooltip" title="Edit Category"></span>
</a>
</td>
<td>
<button data-id="%1$s" class="btn btn-danger btndelete" data-action="Delete">
<span class="glyphicon glyphicon-trash" style="color:#ffffff" data-toggle="tooltip" title="Delete Category"></span>
</button>
</td>
</tr>',
$rs->catid,
$rs->category,
number_format($rs->debt,2),
number_format($rs->remitted,2)
);
}
?>
</tbody>
</table>
</body>
</html>
The following depicts a semi-styled version of the output ~ the css simply removed here for brevity.
And that based upon the following db schema and data almost as per question.
mysql> describe tbl_stock_in; +---------------+------------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------------+------+-----+------------+----------------+ | sid | int(10) unsigned | NO | PRI | NULL | auto_increment | | category_name | varchar(50) | YES | MUL | NULL | | | product_name | varchar(128) | YES | | NULL | | | stock_in | int(10) unsigned | YES | | NULL | | | stock_price | decimal(10,2) unsigned | YES | | 0.00 | | | stocked_date | date | YES | | 0000-00-00 | | +---------------+------------------------+------+-----+------------+----------------+ 6 rows in set (0.00 sec) mysql> describe tbl_category; +----------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------+------+-----+---------+----------------+ | catid | int(10) unsigned | NO | PRI | NULL | auto_increment | | category | varchar(50) | NO | | 0 | | | debt | decimal(10,2) unsigned | YES | | 0.00 | | | remitted | decimal(10,2) unsigned | YES | | 0.00 | | +----------+------------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> select * from tbl_stock_in; +-----+---------------+------------------+----------+-------------+--------------+ | sid | category_name | product_name | stock_in | stock_price | stocked_date | +-----+---------------+------------------+----------+-------------+--------------+ | 1 | PINNACLE | acelofenac 100ml | 10 | 880.00 | 2020-12-17 | | 2 | PINNACLE | acelofenac 100ml | 65 | 920.00 | 2020-12-17 | +-----+---------------+------------------+----------+-------------+--------------+ 2 rows in set (0.00 sec) mysql> select * from tbl_category; +-------+----------+------+----------+ | catid | category | debt | remitted | +-------+----------+------+----------+ | 1 | BG | 0.00 | 0.00 | | 2 | PINNACLE | 0.00 | 0.00 | +-------+----------+------+----------+ 2 rows in set (0.00 sec)