Newbie in tangled web of php/sql, help untangling

[elso]

Hello, maybe someone can help me untangle this to make it do as I want it to...

 

SQL Query objective

 

3 Tables:

#1 – product

 

product_ID , product_name , page_group , default_price

TILE1212 , 12 X 12 Tile , ElsoT , 40.35

TILE810 , 8 X 10 Tile , ElsoT , 29.75

TILE88 , 8 X 8 Tile , ElsoT , 27.95

TILE68 , 6 X 8 Tile , ElsoT , 25.75

 

#2 – product_options

 

option_number , product_ID

1920 , TILE1212

1945 , TILE810

1947 , TILE88

1966 , TILE68

 

#3 – option_choices

 

option_number , disp_value , percentage

1920 , 1-2 , 0.000

1920 , 3-11 , 0.063

1920 , 12-23 , 0.094

1920 , 24-35 , 0.125

1920 , 36-47 , 0.156

1920 , 48-59 , 0.188

1920 , 60-71 , 0.219

1920 , 72+ , 0.250

1945 , 1-2 , 0.000

1945 , 3-11 , 0.000

1945 , 12-23 , 0.000

1945 , 24-35 , 0.000

1945 , 36-47 , 0.000

1945 , 48-59 , 0.000

1945 , 60-71 , 0.000

1945 , 72+ , 0.000

1947 , 1-2 , 0.000

1947 , 3-11 , 0.000

1947 , 12-23 , 0.000

1947 , 24-35 , 0.000

1947 , 36-47 , 0.000

1947 , 48-59 , 0.000

1947 , 60-71 , 0.000

1947 , 72+ , 0.000

1966 , 1-2 , 0.000

1966 , 3-11 , 0.000

1966 , 12-23 , 0.000

1966 , 24-35 , 0.000

1966 , 36-47 , 0.000

1966 , 48-59 , 0.000

1966 , 60-71 , 0.000

1966 , 72+ , 0.000

 

Objective is to create a pricing grid with column headings:

Tile size, to be populated with “product.product_name”

next 8 column headings to be populated with “option_choices.disp_value”

These 8 columns under “option_choices.disp_value” to be polulated with “option_choices.percentage”

 

This has gotten me close but not yet what I am looking for;

$group_query="SELECT * FROM product, product_options, option_choices 
WHERE 
product.page_group='ElsoT' AND
product.product_ID=product_options.product_ID AND
product_options.option_number=option_choices.option_number
";

$group_result=mysql_query($group_query);

$num=mysql_numrows($group_result);

the $num here results in all 32 records but really only want the 4 product records.

 

Next we build our display table Heading using while loop to populate column headings:

<table border="1" cellspacing="2" cellpadding="2">
<tr><td><font face="Arial, Helvetica, sans-serif">Size</font></td>
<?php
$numss=8;
$iii=0;
while ($iii < $numss) {
$f9=mysql_result($group_result,$iii,"disp_value");
?>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f9; ?></font></td>
<?php
$iii++;
}
?>
</tr>

Last the actual display table

<?php
$i=0;
do 
{

$f3=mysql_result($group_result,$i,"product_name");
$f4=mysql_result($group_result,$i,"default_price");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<?php
$nums=8;
$ii=0;
while ($ii < $nums) {
$f8=mysql_result($group_result,$ii,"percentage");

?>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f8; ?></font></td>
<?php
$ii++;
}
?>
</tr>

<?php
$i++;
}
while ($i < $num) ;
?>
</table>

What I am after is for this example to display:

4 rows plus the header row with once only tile name first column

Base price in second column

percentage discounts to loop to remain in the same row as the tile it represents.

 

See my working result page to better see what I am after... or not after..http://www.elsographics.com/tiles/elso.php

 

Thanks in advance to anyone who can help me sort this out

[Barand]

try this

$db = new mysqli(HOST, USERNAME, PASSWORD, 'elso' );

//
// GET DISP_VALUES FOR HEADINGS
//
$sql = "SELECT DISTINCT disp_value FROM option_choices";
$res = $db->query($sql);

$blank_discounts = array();
$output = "<tr><th>Product</th><th>Price</th>";
while ($row = $res->fetch_row()) {
    $output .= "<th>{$row[0]}</th>";
    $blank_discounts[$row[0]] = 0; 
}
$output .= "</tr>\n";

//
// CREATE THE PRICING GRID
//
$current_prod = '';
$current_price = 0;
$sql = "SELECT product_name, default_price, disp_value, percentage
    FROM product
    INNER JOIN product_options USING (product_ID)
    INNER JOIN option_choices USING (option_number)
    ORDER BY product_name";
#echo query2HTMLtable($db, $sql);
$res = $db->query($sql);
while (list($prod, $price, $disp, $pc) = $res->fetch_row()) {
    if ($current_prod != $prod) {
        if ($current_prod) {
            $output .= "<tr><td>$current_prod</td><td>$current_price</td>";
            foreach ($prod_discounts as $disc) {
                $output .= "<td>$disc</td>";
            }
            $output .= "</tr>\n";
        }
        $current_prod = $prod;
        $current_price = $price;
        $prod_discounts = $blank_discounts;
    }
    $prod_discounts[$disp] = number_format($pc,3);
}
$output .= "<tr><td>$current_prod</td><td>$current_price</td>";
foreach ($prod_discounts as $disc) {
    $output .= "<td>$disc</td>";
}
$output .= "</tr>\n";

?>

<table border="1" cellpadding="4">
    <?php echo $output ?>
</table>

results attached

 

[elso]

Thank you. It seems in my searching for a solution I see that there are different SQL databases (forgive me if I am using incorrect terms). You version returns errors and (I could be wrong) this makes me think that perhaps it's not recognized by MYSQL.  I am just learning this stuff to see my objective happen. Your attached thumbnail would be a very workable display.

[Barand]

 

 

$db = new mysqli(HOST, USERNAME, PASSWORD, 'elso' );

 

Did you change that line to use your own connection values?

 

What error messages are you getting?

[Irate]

Maybe he hasn't got the MySQLi module installed... which would be strange since it's gotten quite a standard, no?

[elso]

I have been viewing tutorials pertaining to MySQL, not MySQLi. This is why the trouble. Still haven't figuard all the detail with your suggestion to know how to modify for a more refined search criteria.

 

In layman terms (so I don't confuse myself) I have the 3 tables. All 3 tables hold a diverse varitety of item details thus all need their own unique match criteria.

First match criteria would be in page_group in product table. This would be a predefined constant.

WHERE 
product.page_group='ElsoT'

I would use the reteived product content accordingly.

 

Next we need to query the product_options table using each of the returned product_ID values from the above product table to obtain the option_number associated with the given product_ID.

 

Last, use the retrieved option_number values to query option_choices table.

 

A scenario of sequence using the sample tables with some non relevant rows to show typical obstacles to filter out.

#1 – product

 

product_ID , product_name , page_group , default_price

TILE1212 , 12 X 12 Tile , ElsoT , 40.35

TILE810 , 8 X 10 Tile , ElsoT , 29.75

IMUG11 , 11oz Mug , ElsoM , 12.95

TILE88 , 8 X 8 Tile , ElsoT , 27.95

TILE68 , 6 X 8 Tile , ElsoT , 25.75

IMOUSE, Mouse Pad , Smouse , 9.95

 

WHERE 
product.page_group='ElsoT'

would return 4 rows (4 product_ID values). Also use the returned count of 4 to set number of rows in our final display table/grid.

 

Using the returned product_ID obtain an option_number for each.

 

#2 – product_options (slightly modified for example)

 

option_number , product_ID

851 , IMUG11

1294 , IMOUSE

1920 , TILE1212

1945 , TILE810

1947 , TILE88

1966 , TILE68

 

Returns the 4 corresponding option_number values.

 

Using each of the option_number values we need to retrieve sets of rows from the option_choices table.

This is where it all gets tricky (I think) We need to display the received row groups as columns in the final result price grid.

 

 

#3 – option_choices (slightly modified for example also realizing I had a bunch of non-intended 0.000 percentages in my original post)

Also added in a break between each group.

 

option_number , disp_value , percentage

851 , 1-11 , 0.000

851, 12-23 , 0.05

851, 24-35 , 0.10

851, 36-83 , 0.18

851, 84-203, 0.27

851, 204-227 , 0.38

851, 228-251 , 0.49

851,252-275 , 0.55

851 , 276+ , 0.60

 

1294 , 1-11 , 0.000

1294 , 12-23 , 0..05

1294 , 24-35 , 0.10

1294 , 36-47 , 0.20

1294 , 48-83, 0.30

1294 , 84-167 , 0.40

1294 , 168-215 , 0.50

1294 ,216-263 , 0.60

1294 , 264+ , 0.70

 

 

1920 , 1-2 , 0.000

1920 , 3-11 , 0.063

1920 , 12-23 , 0.094

1920 , 24-35 , 0.100

1920 , 36-47 , 0.156

1920 , 48-59 , 0.188

1920 , 60-71 , 0.219

1920 , 72+ , 0.250

 

1945 , 1-2 , 0.000

1945 , 3-11 , 0.063

1945 , 12-23 , 0.094

1945 , 24-35 , 0.100

1945 , 36-47 , 0.156

1945 , 48-59 , 0.188

1945 , 60-71 , 0.219

1945 , 72+ , 0.250

 

1947 , 1-2 , 0.000

1947 , 3-11 , 0.063

1947 , 12-23 , 0.094

1947 , 24-35 , 0.100

1947 , 36-47 , 0.156

1947 , 48-59 , 0.188

1947 , 60-71 , 0.219

1947 , 72+ , 0.250

 

1966 , 1-2 , 0.000

1966 , 3-11 , 0.063

1966 , 12-23 , 0.094

1966 , 24-35 , 0.100

1966 , 36-47 , 0.156

1966 , 48-59 , 0.188

1966 , 60-71 , 0.219

1966 , 72+ , 0.250

 

My use of the disp_value as the column heading is completely optional as it can easily just be hard written into the table structure for the displayed product group. The heading and percentages would be consistent within each group defined by the product.page_group matching value.

 

This is close to what I am looking for except without the duplicate rows, http://www.elsographics.com/tiles/elso.php

 

Once I get to this point, I will then use the option.choices.percentage values to multiply by the product.default_price

 

Did I bite off more then I can chew?

 

Thanks for any assistance offered..

Edited June 13, 2013 by elso

[mac_gyver]

you need to look past the actual database library that Barand used and 'look' at the program logic that he showed you. it's the program logic in his code that does what you want.

[elso]

I figured the logic was probably there for the most part, just haven't yet been able to see exactly what's going on to follow the logic through.

Will keep pushing in that direction, just want to make sure I am pointed in the correct direction and that I am not trying to do something that is too far outside the box. I will figure it out.

 

Thank you

[Barand]

You need to avaoid running queries inside loop as it kills performance. My version uses a single query to get all the required data.

 

It stores the discount values in an array which output on change of product then cleared ready for the next set of product data. The keys of the array are the disp_values which are stored in a pre-created blank array.

 

This the mysql version

//
// GET DISP_VALUES FOR HEADINGS
//
$sql = "SELECT DISTINCT disp_value FROM option_choices";
$res = mysql_query($sql);

$blank_discounts = $vals = array();
$output = "<tr><th>Product</th><th>Price</th><th>";
while ($row = mysql_fetch_row($res)) {
    $vals[] = $row[0]; 
}
// ensure values in correct order
natsort($vals);
$output .= join('</th><th>', $vals) . "</th></tr>\n";

$blank_discounts = array_fill_keys($vals,'0.000');

//
// CREATE THE PRICING GRID
//
$current_prod = '';
$current_price = 0;
$sql = "SELECT product_name, default_price, disp_value, percentage
    FROM product
    INNER JOIN product_options USING (product_ID)
    INNER JOIN option_choices USING (option_number)
    ORDER BY default_price";

$res = mysql_query($sql);
while (list($prod, $price, $disp, $pc) = mysql_fetch_row($res)) {
    if ($current_prod != $prod) {
        if ($current_prod) {
            // on change of product output array of discounts
            $output .= "<tr><td>$current_prod</td><td>$current_price</td>";
            foreach ($prod_discounts as $disc) {
                $output .= "<td>$disc</td>";
            }
            $output .= "</tr>\n";
        }
        $current_prod = $prod;
        $current_price = $price;
        $prod_discounts = $blank_discounts;
    }
    $prod_discounts[$disp] = number_format($pc,3);
}
// output array of dicounts for final product
$output .= "<tr><td>$current_prod</td><td>$current_price</td>";
foreach ($prod_discounts as $disc) {
    $output .= "<td>$disc</td>";
}
$output .= "</tr>\n";
?>

<table border="1" cellpadding="4">
    <?php echo $output ?>
</table>

[elso]

Thank you Barand.

 

It's going to take me a bit to see through the logic, but I will. Once I can see it through, then I can look into how to filter the results rather then just display everything. http://www.elsographics.com/tiles/elso1.php

[Barand]

I had a look at your elso1.php output and I'm puzzled why the column headings are out of sequence. Did you use the latest version I posted that has the natsort() included?

[elso]

Yes, this is your latest version. Still haven't figured out how to get only the desired records.

My initial version seen at http://www.elsographics.com/tiles/elso.php I have only the requested data in the grid,

Problem is, I had initially embedded a loop (to add columns/percentage) inside the loop to add rows.

Result is the duplicate rows. Beyond my version duplicate rows, it does exactly as I want.

 

Your version, you have the desired percentage values adding columns and without the duplicate rows, both being part of the objective.

I have not yet been able to figure out how it add in additional query criteria to your version.

http://www.elsographics.com/tiles/elso1.php

 

Both of these, "elso.php" & "elso1.php" are reading from the exact same tables.

 

The custom headings are not important. Content is.

Edited June 15, 2013 by elso

[elso]

Sorry, my last post here was incorrect (user/my error) This was NOT the latest version as I got a list of errors. http://www.elsographics.com/tiles/elso1.php

 

My initial version seen at http://www.elsograph.../tiles/elso.php I have only the requested data in the grid,

Problem is, I had initially embedded a loop (to add columns/percentage) inside the loop to add rows.

Result is the duplicate rows. Beyond my version duplicate rows, it does exactly as I want.