Displaying mysql select records in html css table and calculate total price - Page 2

[sanbhu2105]

It's ok my friend. I think we forgot one thing here. The total price by retailer in the table column. Can you tell me how we go about that?

[broj1]

Ups. Give me couple of minutes.

[broj1]

Here is corrected code. I added $totalsArray array in couple of places.

<?php 
session_start(); 

// uncomment this for you to work
// include('db_connect.php'); 
// $username = $_SESSION['username']; 

// --------------------------------------------
// IGNORE THIS - IT IS MY STUFF
include('../dbconnect.php');
$link = dbConnect(array('driver' => 'mysql'));
include('../../html/lib/func.php');
$username = 'davinci'; 
// --------------------------------------------

$user = mysql_fetch_assoc(mysql_query("select user_id from tbllogin where username = '{$username}'")); 

// get favourite retailers
$qRet  = "SELECT tblfav_ret.ret_id, tblretailer.ret_name, tblretailer.user_id AS ret_admin_id ";
$qRet .= "FROM tblfav_ret ";
$qRet .= "JOIN tblretailer ON tblfav_ret.ret_id=tblretailer.ret_id ";
$qRet .= "WHERE tblfav_ret.user_id = '{$user['user_id']}' ";
$qRet .= "ORDER BY tblretailer.ret_id";
$retRes = mysql_query($qRet);

// array with data about favourite retailers (id and name)
// used for table heading
$favRetailerArray = array();

// array with retailer user_ids (called ret_admin_id to avoid confusion)
$favRetailerAdminArray = array();

// create arraya with retailer data and retailer admin IDs (to map to retailers later)
while($retRow = mysql_fetch_assoc($retRes)) {

    $ret_id = $retRow['ret_id'];
    $favRetailerArray[$ret_id] = $retRow['ret_name'];
    $favRetailerAdminArray[] = $retRow['ret_admin_id'];
}

// make a string that will be an IN condition for SQL statement for prices
$retAdminIdList = implode(',', $favRetailerAdminArray);

// start HTML table
echo "<table bgcolor='grey' width='80%' border=1>";

// HTML table header
echo "<th>Name</th>";
foreach($favRetailerArray as $ret_name) {

    echo "<th>$ret_name</th>";
}
echo "</tr>";

// initialize totals array
for($i = 0; $i < count($favRetailerAdminArray); $i++) {
    $totalsArray[$i] = 0;
}

// query for getting products of favourite retailers (without any prices)
$qFav  = "SELECT tblfavourites.prod_id, tblproduct.prod_name, tblretailer.user_id as ret_admin_id  ";
$qFav .= "FROM tblfavourites ";
$qFav .= "JOIN tblproduct ON tblproduct.prod_id=tblfavourites.prod_id ";
$qFav .= "JOIN tblfav_ret ON tblfav_ret.user_id=tblfavourites.user_id ";
$qFav .= "JOIN tblretailer ON tblfav_ret.ret_id=tblretailer.ret_id ";
$qFav .= "WHERE tblfavourites.user_id = '{$user['user_id']}' ";
$qFav .= "GROUP BY tblproduct.prod_id ";
$qFav .= "ORDER BY tblproduct.prod_id";
$favRes = mysql_query($qFav);

// print HTML rows
// first cell is product name
// - other cells are prices for favourite retailers (read by SQL below)
while($favRow = mysql_fetch_assoc($favRes)) {

    $prod_id = $favRow['prod_id'];
    $prod_name = $favRow['prod_name'];

    // add table cell for product name
    echo "<tr><td>$prod_name</td>";

    // read proces for favourites retailers for this product
    $qProd  = "SELECT tblretprod.user_id as ret_admin_id, tblretprod.prod_price FROM tblretprod ";
    $qProd .= "JOIN tblretailer ON tblretprod.user_id=tblretailer.user_id ";
    $qProd .= "WHERE tblretprod.user_id IN ($retAdminIdList) AND tblretprod.prod_id=$prod_id ";
    $qProd .= "ORDER BY tblretailer.ret_id";
    $prodRes = mysql_query($qProd);

    // add cells for prices for each retailer
    while($prodRow = mysql_fetch_assoc($prodRes)) {

        $ret_admin_id = $prodRow['ret_admin_id'];

        for($i = 0; $i < count($favRetailerAdminArray); $i++) {

            if($ret_admin_id == $favRetailerAdminArray[$i]) {

                echo "<td>{$prodRow['prod_price']}</td>";
                $totalsArray[$i] += $prodRow['prod_price'];

            } else {
                echo "<td>&nbsp;</td>";
            }
        }
    }

    echo '</tr>';   
}

// a row with totals
echo "<tr><td>TOTAL</td>";
for($i = 0; $i < count($favRetailerAdminArray); $i++) {
    echo "<td>{$totalsArray[$i]}</td>";
}
echo '</tr>';

// end HTML table
echo "</table>";
?>

[sanbhu2105]

That works great. Thank you a lot my friend. I would not have been able to do that without you.

This completes my project.

I owe u this one.

[broj1]

No problem. Happy coding.