Forum Moderators: coopster
Table 1 - style_img. Contains the style numbers and images.
Table 2 - products. Contains all information about each style, size, style no., sku, etc.
My objective is to create an ordering page for each style. Each style is available in 9 or 10 sizes and 28 - 32 colors. I have been attempting to code for 1 cell of a table, then use a for or foreach to loop through all the colors/sizes to display one color with corresponding sizes in each cell. I then wanted to apply pagination to display 3 cells across the page; 4 or 5 rows on each page.
The linked page is a sample I created by using only one color in my code so I could better explain what I am attempting.
<snipped url>
Here's the code I used to create that page:
<?php
include('admin/misc.inc');
$cxn = mysqli_connect($host,$user,$passwd,$dbname) or die ("couldn't connect to server" . mysqli_error());
$input = "input type=\"text\" size=\"2\" name=";
$colors = array('Black', 'Celadon', 'Chocolate', 'Ciel');
echo "<table width:750px cellpadding='0' cellspacing='0'>";
echo "<tr>";
$result = mysqli_query($cxn,"SELECT * FROM style_img WHERE style_no='4112'")or die ("problem with query" . mysqli_error());
while($row = mysqli_fetch_assoc($result))
{
echo "<td width='250'>";
echo "<img src='../images/{$row['photo_sm']}' border='0' align='left'>";
echo "</p>";
}
$result2= mysqli_query($cxn,"SELECT * FROM products WHERE style_no='4112' ORDER BY size_rank") or die ("problem with second query" . mysqli_error());
while($row = mysqli_fetch_array($result2))
{
echo "<p><font face='Arial Narrow' size='2'><$input\"{$row['price']}_{$row['sku']} {$row['vendor_name']} {$row['color_name']} {$row['desc']} #{$row['style_no']}-{$row['color_code']}-{$row['size']}(nontaxable)_{$row['shipping']}\"> {$row['size']}@ \${$row['price']} each</p>";
}
echo "</td>";
echo "</tr>";
echo "</table>";
?>
The problem I have is if I use "foreach", the photo appears next to each size (i.e. 9 sizes and 9 photos) or all the garment photos are displayed followed by the sizes grouped as all XS, all S, etc. I've been reading about one to many relationships and arrays in arrays as well as loop within a loop. Quite honestly, I'm going loopy trying to get this to work. It's been weeks now and I've had no success.
If someone could please just point me in the right direction or let me know that this is IMPOSSIBLE, I'd appreciate it.
Please also let me know if you need more information.
[edited by: coopster at 10:30 pm (utc) on Jan. 16, 2009]
[edit reason] no personal urls please TOS [webmasterworld.com] [/edit]
I'm having a bit of a hard time following your objective. Does the code you posted produce the content that you want for one cell? Do you just need to replicate this for other values of style_no?
If so, then you would put foreach($style_array as $style_no) { (assuming of course you've got the styles in an array called $style_array) above your first $result = line, replace the hard-coded number in your queries with $style_no, then the closing brace for the foreach after the </td> echo.
That's phase 1. That should get you however many <td>s all across in one row.
Phase 2 is to keep track of the <td> count so you can do separate rows. For that, look at this separately (that is, paste it into a new file, dissect it, play with it, etc):
<table>
<?php
$ary = range(1,13); // This is just some test content
$i = 0;
$cols = 3;
// Your stuff is going to go right here, replacing the next 4 lines:
foreach($ary as $a) {
if(!($i%$cols))
echo " <tr>\n";
echo " <td>$a</td>\n";
$i++;
if(!($i%$cols))
echo " </tr>\n";
} // EndForEach array item
if($i%$cols) {
while(($i++)%$cols)
echo " <td> </td>\n";
echo " </tr>\n";
} // EndIf need to fill a row
?>
</table>
The % is the modulus operator, it returns the remainder of a mathematical division. The lines above echo a <tr> if there's no remainder, which means the start of a row. Then the content is spat out, then a </tr> is echoed if the desired number of columns is reached. Afterward, the counter is checked to see if any empty <td>s need to be echoed to fill out a row.
Put pagination aside for a bit, mess around with this and report back. You should be able to get one big page that has all of your styles, 3 cells across.
Actually, each page is to display one style number. In this case, style #4112. On this page I want to display each color in a separate cell with the photo on the left, then the list of available sizes to the right of that photo. For example, the first cell should show the photo of the black pants, then next to it in a column, XS, S, M, XL, 2XL, 3XL, 4XL, 5XL. The next cell should show the same for the celadon pant, and so on for each color.
I have 1 array - $colors. When I use a foreach on that array, I get all the photos in a row, then the sizes display with all the XS in all colors grouped together, all S grouped together, and so on.
Need to ask a really stupid question now...in my table structure for the style_img, since there were no unique entries in any fields, I used "id" as the primary key and have these numbered. In my table structure for the products, the primary key is sku. To relate the tables for a one to many relationship, should I also have an "id" field in the products table that uses the same "id" number from the style_img table? For example, in style_img table, if the "id" number associated with the 4112 black photo is 3, should all the sizes (XS - 5XL) of the 4112 in black also have a 3 in an "id" field?
I sure hope you followed that...I confuse myself sometimes.
In the products table do you have an entry for 4112 XS in black and another for 4112 XS in celadon (I mean in general; the answer would still be yes if in particular 4112 XS celadon isn't available). If so, then yes, you would want to add a column to the products table to match the color id in the style_img table. Then you would add to your second sql statement to select the correct id for that style (sytle_no = AND id = ).
I believe you need to nest your while's.
while($srow = mysqli_fetch_assoc($result))
{
echo image; // $srow['photo_sm']
$result2= mysqli_query($cxn,"SELECT * FROM products WHERE ((style_no='4112') AND (id={$row['id']})) ORDER BY size_rank") or die ("problem with second query" . mysqli_error());
while($row = mysqli_fetch_array($result2))
{
echo numbers;
} // endwhile result2
} // endwhile result
Because they're nested, I changed the name of the variable that receives the style_img data - while in this particular case it would be just fine to reuse that variable, you could get yourself really messed up in the future, so it's better to form the habit right now.
The way you have your table structured, the "numbers" (price, sku, size, et al) will appear below the picture. To keep that from happening, the "better" way is to use CSS and floating DIVs, but you could easily spend a couple of days hashing that out, so I suggest nesting a table. Each of your table cells above will contain a table, the first column being the photo and the second being the data from $result2.
I added the 'id' field in the products catalog so that every size in style 4112 in chocolate has a 3; black has an 8, etc.
Taking a closer look at the code, I guess I don't follow. What goes in here (id={$row['id']}))? If I want each color to display by referencing the 'id' number, do I list each one? I tried leaving it just the way it's written, but receive an error.
Sorry, guess I'm more confused than I knew!
And so the products table should not have any styles info in this case.
Then the outer loop is for the products and the inner loop for the styles.
$result = mysqli_query($cxn,"SELECT * FROM products")or die ("problem with query" . mysqli_error());
while($row = mysqli_fetch_assoc($result))
..........
$result2= mysqli_query($cxn,"SELECT * FROM styles WHERE products_id=$row['products_id'] ORDER BY size_rank") or die ("problem with second query" . mysqli_error());
..........
}
Again depends how the db structure is. If you have an intermediate table like styles_to_products then that's the one to use for the inner loop.
$result2= mysqli_query($cxn,"SELECT * FROM products WHERE style_no='4112' ORDER BY size_rank") or die ("problem with second query" . mysqli_error());
while the 2nd will retrieve just one image that is associated with the product.
$result = mysqli_query($cxn,"SELECT * FROM style_img WHERE style_no='4112'")or die ("problem with query" . mysqli_error());
For the first query you will need a loop to retrieve all the sizes for a given style_no or id whatever you have as reference. But for the 2nd you need just the id that identifies the image. And before printing anything you store the results of products into a separate array so you can access it later. Like
$products_array = array();
while($row = mysqli_fetch_assoc($result2)) {
$products_array[] = $row;
}
$styles_array = array();
if($row = mysqli_fetch_assoc($result)) {
$styles_array = $row;
}
// Print the images on the left
echo $styles_array['photo_sm'];
for($i=0, $j=count($products_array); $i<$j; $i++ )
echo $products_array[$i]['color_name'] . ' ' . $products_array[$i]['sku'] . ' ' .............etc
}
So this should print one image for each product of the same style.
Usually you define the tables based on relationship among entities. So a product has many styles you could use one db table that holds all the product info including the image and then another that holds the styles. The products would drive the style because new records will create a new primary key value for products that can then be used inside the styles table.
Just when I thought I was going to pop an eyeball...it all came together. I ended up using cameraman's suggested code with nested while statements. Changed the (id={$row['id']})) to (id={$srow['id']})) and it worked like a charm.
Then I wrestled on Phase 1 of his suggestion to display in columns. It took some experimentation, but I finally got the codes in the right places and viola, the columns appeared.
Next step...pagination. This is for the styles that will come in up to 35 colors. I was thinking that that might take too long to load if it was on one page.
Your opinions, please. Should I just let them all display, or would pagination be better?
I think the big thing to consider on pagination is load time for the page. The breaking point would be subjective and arbitrary; I know that if a page takes too long to load when I'm looking for something, I'm off to the next one of the 800 million results that the search engine rounded up for me, but I can't name a specific number of megabytes.
Occasionally the vertical length might be a consideration, but where that point might be is likely a tossup as well.
