hello guys i have created the below codes. the first one is the mysql select statement which works fine when i echo. the second one is an html/css table i created. i am stuck to the point where i can merge both of them so that i get a nice display and the total price of the products are calculated in each column.

the first code selects product name, retailer name and product price from 5 tables. using the echo codes i can display the records in columns.

code for mysql select statement

<?php 

session_start(); 

include('db_connect.php'); 

$username = $_SESSION['username']; 

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

$query = mysql_query 
("SELECT * FROM tblfav_ret a, tblretailer b, tblretprod c, tblproduct d, tblfavourites e 
WHERE a.ret_id = b.ret_id 
AND b.user_id = c.user_id 
AND c.prod_id = d.prod_id 
AND d.prod_id = e.prod_id 
AND a.user_id = '{$user['user_id']}'"); 

$num = mysql_num_rows($query); 

if($num>0){ 

echo "<center><table bgcolor='grey' width='80%' border=0>"; 

echo "<tr bgcolor='#CCCCCC'>"; 
echo "<td><b><center>Name</td>"; 
echo "<td><b><center>Retailer</td>"; 
echo "<td><b><center>Price</td>"; 
echo "</tr>"; 

while($row = mysql_fetch_assoc($query)){ 
     extract($row);  

     echo "<tr>"; 
     echo "<td style='text-align: center;'>".$row['prod_name']."</td>"; 
     echo "<td style='text-align: center;'>".$row['ret_name']."</td>"; 
echo "<td style='text-align: center;'>".$row['prod_price']."</td>"; 
echo "</tr>"; 

} 
echo "</table>"; 
} 

?>

i further created this html/css table which displays smthng like this

product name <retailer_name> <retailer_name> <retailer_name> <retailer_name>
prod_1    price1         price2      price3        price4
prod_2    price1         price2      price3        price4
total <total_price> <total_price> <total_price> <total_price>





<html>
<body>

<div id="myfavourites">

<table class="fav_tbl">

<thead>
            <tr>
                        <th scope="col">Product</th>
                        <th scope="col">Retailer 1</th>
                        <th scope="col">Retailer 2</th>
                        <th scope="col">Retailer 3</th>
                        <th scope="col">Retailer 4</th>
           </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th scope="row">Total per month</th>
                        <td>Total 1</td>
                        <td>Total 2</td>
                        <td>Total 3</td>
                        <td>Total 4</td>
                    </tr>
                </tfoot>

</table>

</div>

</body>
</html>

the help i need is to make the display in the html/css table and calculation of total price of products listed

I hope your query returns also prices for each of the retailers (it is not evident from the sql statement since you are using SELECT *). So in the while loop you can calculate running sum for all the prices. Once the while loop is finished the running sums will be totals.

// initialize running sums
$total1 = 0;
$total2 = 0;
$total3 = 0;
$total4 = 0;

while($row = mysql_fetch_assoc($query)){ 
    extract($row);  

    // add prices to running sums (I made up names here)
    $total1 += $row['prod_price1'];
    $total2 += $row['prod_price2'];
    $total3 += $row['prod_price3'];
    $total4 += $row['prod_price4'];

    echo "<tr>"; 
    echo "<td style='text-align: center;'>".$row['prod_name']."</td>"; 
    echo "<td style='text-align: center;'>".$row['ret_name']."</td>"; 
    echo "<td style='text-align: center;'>".$row['prod_price']."</td>"; 
    echo "</tr>"; 
}

// add the totals row
echo '<tfoot><tr><th scope="row">Total per month</th>';
echo "<td>$total1</td>";
echo "<td>$total2</td>";
echo "<td>$total3</td>";
echo "<td>$total4</td>";
echo '</tr></tfoot>';
...

Might be not exactly in line with your code but you get the idea.

Hmmm yes this helps out. My sql query returns the prices by retailer for each product. When i display the records, they are not being aligned as i expect. The product name is being displayed twice if more than one retailer is selling the product. It should display the price in the row under the specific retailer. Am stuck in how to do that with the code i have.

Post the latest version of the code. Also helpful would be structure of the tables and some example data.

structure of tables

tblfav_ret - id, user_id, ret_id
tblretailer - ret_id, user_id, ret_name, ret_address, ret_phone, ret_fax
tblretprod - id, user_id, prod_id, prod_price
tblproduct - prod_id, prod_name, prod_brand, prod_desc, prod_photo
tblfavourites - fav_id, prod_id, user_id

the code is as first provided but when i make the select, all the data i need is retrieved but i am not having the required display

display when the records are retrieved should be like this

Product Name Retailer1 Retailer2,etc
(all products set as favourites by customer) (Retailer set as favourite by customer)

(products set as favourites are displayed here) (price set by this retailer for this product here)

xxxxxx                                                 yyyy   
xxxxxx                                                 yyyy
xxxxxx                                                 yyyy
xxxxxx                                                 yyyy

does this clear what i want to do?

This seems like different requirement that the one you posted in your first post.

In order to test I need the data (I can't make up all this data since I might missinterpret it and it takes too much time). So please send an export of those tables and some data in SQL format. You can do this in phpmyadmin (select a table and go to Export). If there is sensitive data in the table (real usernames, passwords etc.) anonymize them.

Please clarify what the output should be by faking a few rows of the output table (like above, but complete structure).

Sory for asking for so much information but only this way I can understand the problem.

all data have been faked in the attachments. hope this helps.

let me know if more is required

remove the .txt at the end of the file and you will have the sql

OK, thnx for now. I'll have a look and let you know if I need something else.

please note that under retailer 2 and retailer 3, price set by retailer 2 shoudl be displayed for products and price for retailer 3 should be displayed for products
i think i mistyped it there in the pdf attachment for table structure

I also need tblfav_ret, tblfavourites and tblretprod table structures and data (actually all the tables that are included in queries).

please note that under retailer 2 and retailer 3, price set by retailer 2 shoudl be displayed for products and price for retailer 3 should be displayed for products
i think i mistyped it there in the pdf attachment for table structure

I'll take this into account.

sorry for delay, i was at work.

More questions:

  • what exactly is user_id (is it a customer of different retailers, can one user belong to many retailers)?
  • what is the purpose of the table tblfavourites and what of the table tblfav_ret?
  • why is product price in separate table and why is same product price different for each user?

If I understood the thing, you want this:

  • a logged in user has some favourite retailers and wants to compare prices for one (or more) products
  • the data in separate tables should be joined for that user and all of his favourite retailers and comparison made for the number of favourite retailers found (it can be 0 or more)

If this is incorrect let me know what exactly is the purpose. The first thing we have to get the query right.

I am leaving in 2 hrs and wont be back until Sunday night.

This is part of a price comparison site. Each time a retailer or a customer registers, depending on who is registering, tblcustomer (customer details) and tblretailer (retailer details) are stored. The username and password are populated in tbllogin where a user_id is automatically assigned and the profile as well - either retailer or customer. The user_id comes from the tbllogin.

tblproduct is filled in by the Admin. Customers are able to view those products on the site. On clicking on the add to favourites button, tblfavourites is filled in with the prod_id and the user_id (customer which is logged in).

The customer has the possibility of viewing all the retailers registered. If the customer clicks on the add to favourites button, tblfav_ret is populated with the user_id (of customer) and the ret_id.

Retailers have the possibility of setting the prices of their products after they log in. tblretprod is populated with the user_id (logged in customer), prod_id (for which price was set) and prod_price (price of product).

What i am crreating? I have to create a table where the customer (after logging in) can view the products set as favourites, retailers set as favourites and under every retailer the price of the products set by the different retailers (favourites). Customers will also have a total price of products at each retailers. This way they can decide where they can go and buy their products.

Hope this clarifies what i want to do.

Thanks for helping out.

One thing I can't figure out. I am missing a table with prices of products for each retailer (prod_id->ret_id->prod_price relation). I preusme the user wants to find out prices for chosen products at different retailers.

The tblretprod table holds the prices for different users. I can't figure out the relation here.

The tblretprod stores the prices inserted by each retailer for each product. When customer adds a product as favourite. There is a prod_id assigned to the product. When the retailer adds the price of a product, the price is set for that product with a prod_id. The tblretprod holds the prices set by different retailers.

What if you wanted to display the results next to eachother, e.g instead of

xxxxxx yyyy
xxxxxx yyyy
xxxxxx yyyy
xxxxxx yyyy

.....have

xxxxx xxxxx xxxxx
yyyyy yyyyy yyyyy

xxxx would be retailer and yyyy would be price?

if yes, that is what i want. beside each yyyy should be the product name for which the price is being displayed

So the user_id field in the tblretprod table is the ID of administrator and not the regular user. This field name is a bit misleading, I would change it to admin_id or ret_user_id or somthing similar. And the key for each retailer's price is that same user_id which is a bit odd since the price belongs to retailer so the retailer->price relation is more obvious (i.e. what happens when you change the administrator and his ID?). I would change this if I were you.

Anyway I will try with the way data is structured now. I hope I can post something this afternoon.

I thank you a lot for helping me out.

I will explain the user_id thing. Each time a retailer or a customer registers the related tblcustomer and tblretailer are populated. Another table called tbllogin is populated as well where a user_id is generated automatically and the role customer or retailer is assigned.

The user_id field is the id of the retailer in tblretprod. It is the user_id from tbllogin belonging to the customer who has signed in and inserting the product prices.

Hope this clarifies things a bit.

Yes, I understand that and that is not a big issue if you are careful.

The problem for me is the relation between the user_id (the retailer admin) and the prod_id. The relation should be between the ret_id and the prod_id. The table tblretprod should have fields: id, ret_id, prod_id, prod_price, enabled.

Hmmmm ok i understand. Is it too late for me to add the ret_id field to the table to which the user_id corresponds? I can try that out if need be.

OK. The way I got arround this is I did not get all the data in one query but broke it down to separate queries. The thing is you do not know the number of columens (retailers) in advance so it is easier to do it step by step. I hope this roughly is what you wanted. You can improve code a bit (check for values etc.). See also comments in the code. I also removed styles and some table attributes for clarity and did not use aliases in queries (a, b, c... do not tell much but add to confusion).

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=0>";

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

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

// 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.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)) {

        if(isset($prodRow['prod_price']) && $prodRow['prod_price'] > 0) {
            $prod_price = $prodRow['prod_price'];
        } else {
            $prod_price = 'No price stored';
        }

        echo "<td>$prod_price</td>";
    }
    echo '</tr>';   
}

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

Well thank you my friend. I will try work my way out with the codes and let you know about the outcome...

Hello my friend, i changed the ret_admin_id to user_id and am having the below display. The price for only first retailer is being displayed. I tried with 2 retailers.

Does it work OK if you do not change the ret_admin_id to user_id?

yeah it works out but am having same display

Can you post the contents of the tables (in SQL format) so I can test it here (I had to change the data in your previous tables to have useful data for testing).

Remove the .txt to have the sql

Sory, my mistake in the last while loop. The logic is complicated due to using user_id in retprod table for establishing the relation. It would be far more simple if relation would be made as noted in my previous posts. Here is the corrected code. It is hard to test it thoroughly, I hope it works OK.

<?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=0>";

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

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

// 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);

    $tempArr = array();

    // 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>";
            } else {
                echo "<td>&nbsp;</td>";
            }
        }
    }

    echo '</tr>';   
}

// end HTML table
echo "</table>";
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.