a nested loop problem

[lauralee]

I am trying to list an image and description of a product family followed by the image, description, and other information of each of the products in that family. I have the families in one table and the products in another table and using INNER JOIN for my results, but it isn't working. Would you please take a look and see what I am missing. Right now it only displays the first product family with nothing listed below it. If I take out the section requesting all of the products in that particular family, I get the list of each of the families. How can I list all of the products in that family below each family?$result = @mysql_query('SELECT * FROM family1'); if (!result) { exit('<p>Error performing query: ' . mysql_error() . '</p>');}while ($row = mysql_fetch_array($result)) {echo '<div class="productbox1">';echo'<p><img class="products" src="/images/products/' . $row['name'] . '.jpg" alt="' . $row['name'] . '"></img><span>' . $row['name'] . '</span></p>';echo ' <p>' . bbcodeout($row['description']) . '</p>';echo '</div>';//Request all of the products in that family of products$result = @mysql_query('SELECT * FROM products1 INNER JOIN family1 ON name=product_name ORDER BY id'); if (!result) { exit('<p>Error performing query: ' . mysql_error() . '</p>');}while ($row = mysql_fetch_array($result)) {echo '<div class="product-b-1">';echo '<div class="pricebox">' . '$ ' . $row['list_price'] . '</div><div class="productboxcolor">' . $row['size'] . '</div>';echo '<img class="products" src="/images/products/' . $row['id'] . '.jpg" alt="' . $row['name'] . '"></img>' . $row['name'] . '<br />';echo '<em>' . $row['id'] . '</em>';echo '<p>' . bbcodeout($row['description']) . '</p>';echo '</div>';}}

[justsomeguy]

You named both of your mysql_query return values $result, so you're overwriting the result of families with the result of products. When it goes to loop to the next family that result set is already at the end because you already looped through it when you were going through the products. You also used $row twice.

[lauralee]

I have made the changes you suggested. It does now go through the loop and display each family image and description, but now it gives the following error after each family:Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/conservf/public_html/building_products/1indextest.php on line 79I've checked the INNER JOIN several times to make sure that the name and family_name are the same in both tables.Below is the new code$result = @mysql_query('SELECT * FROM family1');if (!result) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($row = mysql_fetch_array($result)){echo '<div class="productbox1">';echo'<p><img class="products" src="/images/products/' . $row['name'] . '.jpg" alt="' . $row['name'] . '"></img><span>' . $row['name'] . '</span></p>';echo ' <p>' . bbcodeout($row['description']) . '</p>';echo '</div>';//Request all of the products in that family of products$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON name=family_name ORDER BY id');if (!result) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($typ = mysql_fetch_array($prods)){echo '<div class="product-b-1">';echo '<div class="pricebox">' . '$ ' . $typ['list_price'] . '</div><div class="productboxcolor">' . $typ['size'] . '</div>';echo '<img class="products" src="/images/products/' . $typ['id'] . '.jpg" alt="' . $typ['name'] . '"></img>' . $typ['name'] . '<br />';echo '<em>' . $typ['id'] . '</em>';echo '<p>' . bbcodeout($typ['description']) . '</p>';echo '</div>';}}

[[dx]]

I guess you want $result here if (!result) {Or maybe $prods in other case.So then you will get your sql error

[lauralee]

The problem is that the products that belong in each family is not listed below the family. Using the INNER JOIN even though the name and family_name are identical in the two separate tables.

[birbal]

i think changing it $result to $prods will work here

[justsomeguy]

You need to change both of these:

$result = @mysql_query('SELECT * FROM family1');if (!result) {

$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON name=family_name ORDER BY id');if (!result) {

The !result needs to be !$result in the first and !$prods in the second.

[lauralee]

Thanks. I now see that I did not have prods in !$prods. And I left out the $ in both examples. However I have changed that, and now I get the following error...Error performing query: Column 'name' in on clause is ambiguousI changed the name of the field to family_name, but get the same message. How do I correct this one?

[[dx]]

Is there any error in report?Please paste full script.

[lauralee]

I edited my reply, so you may not have seen the latest. I am getting the following error:Error performing query: Column 'family_name' in on clause is ambiguousHere is the full script.<?phpecho '<h2>' . $Page . '</h2>';$result = @mysql_query('SELECT * FROM family1 WHERE inactive=" "');if (!$result) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($row = mysql_fetch_array($result)){echo '<div class="productbox1">';echo'<p><img class="products" src="/images/products/' . $row['family_name'] . '.jpg" alt="' . $row['family_name'] . '"></img><span>' . $row['family_name'] . '</span></p>';echo ' <p>' . bbcodeout($row['description']) . '</p>';echo '</div>';//Request all of the products in that family of products$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family_name = familyname ORDER BY id');if (!$prods) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($typ = mysql_fetch_array($prods)){echo '<div class="product-b-1">';echo '<div class="pricebox">' . '$ ' . $typ['list_price'] . '</div><div class="productboxcolor">' . $typ['size'] . '</div>';echo '<img class="products" src="/images/products/' . $typ['id'] . '.jpg" alt="' . $typ['familyname'] . '"></img>' . $typ['familyname'] . '<br />';echo '<em>' . $typ['id'] . '</em>';echo '<p>' . bbcodeout($typ['description']) . '</p>';echo '</div>';}} include '/home/conservf/public_html/includes/returns_building.inc.html.php'; ?>

[justsomeguy]

You need to preface the field name with the table name. e.g.:SELECT * FROM products INNER JOIN family1 ON family1.family_name = products.familyname ORDER BY id

[lauralee]

Thanks. I have another issue that I am working on in this same code....Something I didn't realize until everything started to display. I'll try to work it out myself. If I can't get it...I'll be back.But thanks so much for your help!!!

[lauralee]

Well, I'm back. Now, all of the products are listed under each family, instead of just the products that belong to that family. I've been trying to work out a solution using arrays, but I haven't had any luck.I thought if I could use the following, and substitute: AND familyid="1" for AND familyid="$i" as well as in the second loop following the WHERE statement so I could run through the numbers up to and including the last familyid (I'd have to use a COUNT to determine that last number), but it doesn't work when I substitute the number for the variable $i. Suggestions? In the code sample below the first family description and image is followed by the products in that first family. But I can't get it to run through the entire family list with just those family products listed below each family. Suggestions?$i=1;$names=array("Building Gaskets", "Cornerseals", "Recycle Containers", "HeatSavr", "Ventilation", "Foam");$result = @mysql_query('SELECT * FROM family1 WHERE inactive=" " AND familyid="1"');if (!$result) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($row = mysql_fetch_array($result)){echo '<div class="productbox1">';echo'<p><img class="products" src="/images/products/' . $row['family_name'] . '.jpg" alt="' . $row['family_name'] . '"></img><span>' . $row['family_name'] . '</span></p>';echo ' <p>' . bbcodeout($row['familydescription']) . '</p>';echo '</div>';$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid="1"');if (!$prods) {exit('<p>Error performing query: ' .mysql_error() . '</p>');}while ($typ = mysql_fetch_array($prods)){echo '<div class="productbox1">';echo'<p><img class="products" src="/images/products/' . $typ['id'] . '.jpg" alt="' . $typ['id'] . '"></img><span>' . $typ['id'] . '</span></p>';echo ' <p>' . bbcodeout($typ['description']) . '</p>';echo '</div>';}$i++;}

[justsomeguy]

You don't need to specify the ID when you get the families:$result = @mysql_query('SELECT * FROM family1 WHERE inactive=" "');In the products query, use the ID for the family you're currently on:$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=' . $row['familyid']);You really only need one loop there though. This query:$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id ORDER BY familyid');will return all products and families. You can keep track of the last family you displayed and loop through that list of products and families. When you get to a row that has different family information than the last one you saw, then you would print the family information before printing the products.

[lauralee]

Thanks so much! It works. Now, it becomes a little more complicated because I need to have each product displayed with different sizes and colors if they have different size and color. Again, I'll work on this myself and will ask for help if I get stuck and can't find a solution.I love this forum!!! It is so helpful and no one is condescending with us newbies who are trying to learn PHP. Thanks to ALL!

[lauralee]

Okay, now I want the results to ORDER BY a particular field, but I get errors no matter where I place the ORDER BY. Where do I put it so I don't get errors? $prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='. $row['familyid']); And why does the code below work without the ' before the closing parenthesis at the end? It seems like there should be ('SELECT.......... ')$prods = @mysql_query('SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='. $row['familyid']);

[justsomeguy]

ORDER BY goes near the end, after WHERE. e.g.:$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=' . $row['familyid'] . ' ORDER BY products.id');

[thescientist]

Okay, now I want the results to ORDER BY a particular field, but I get errors no matter where I place the ORDER BY. Where do I put it so I don't get errors? $prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='. $row['familyid']); And why does the code below work without the ' before the closing parenthesis at the end? It seems like there should be ('SELECT.......... ')$prods = @mysql_query('SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='. $row['familyid']);

because this part is the string

'SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='

and this is a variable

$row['familyid']

I'm pretty sure with the way quotes work in PHP, if you put double quotes around the whole thing, and aren't concatenating the variable to the end of the string, it would work in the way you were expecting, because the double quotes would interpolate the variable and it would become the value it represents.

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=$row['familyid']");

but don't quote me on that

[birbal]

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=$row['familyid']");

i think it should be

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid={$row['familyid']}");

[lauralee]

ORDER BY goes near the end, after WHERE. e.g.:$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=' . $row['familyid'] . ' ORDER BY products.id');

That's where I had tried it to begin with, but I get the following error:Parse error: syntax error, unexpected T_IF in /home/conservf/public_html/building_products/1indextest.php on line 70Then I tried putting double quotes here: ("SELECT........") and got this error:Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/conservf/public_html/building_products/1indextest.php on line 69So, I tried placing the ORDER BY in different places in the code, but with no luck. I keep getting the errors.

[lauralee]

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=$row['familyid']");

i think it should be

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid={$row['familyid']}");

When I try that, the page shows the first family item, then the following error:"error performing query: "

[lauralee]

because this part is the string

'SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid='

and this is a variable

$row['familyid']

I'm pretty sure with the way quotes work in PHP, if you put double quotes around the whole thing, and aren't concatenating the variable to the end of the string, it would work in the way you were expecting, because the double quotes would interpolate the variable and it would become the value it represents.

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=$row['familyid']");

but don't quote me on that

Sorry, tried that one too and got this:Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/conservf/public_html/building_products/1indextest.php on line 69

[lauralee]

I have moved things around, added single quotes, double quotes and dots in a variety of places but nothing has worked so far. I know this can't be that complicated. I must be overlooking something......Should be able to order the results. Any other suggestions?

[birbal]

When I try that, the page shows the first family item, then the following error:"error performing query: "

there is some problem in your inner join..i did not notice it before

("SELECT * FROM products ORDER BY id INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid={$row['familyid']}");

("SELECT col1,col2 FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid=$row['familyid'] ORDER BY id");

as jsg said above order by goes to end of the where clause.i belive you should specify the column name in inner join instad of wild card(*)and probably you need to prefix the table name or alias in your id. eg ORDER BY products.id (to avoid ambiguity from same column name in difftren tables)when your sql fails you can always check its error by mysql_error() to get what is going wrong.parse error means you are missing something in your code(eg semicolon,braces). it shows if you have done any syntactical error. parse error comes before when your php file being realy executed.

[lauralee]

Well, well, well here it is:$prods = @mysql_query('SELECT * FROM products INNER JOIN family1 ON family1.familyid = products.family_id WHERE familyid="' . $row['familyid'] . '" ORDER BY products.id');The problem was that I needed the double quotes before the single quote and after the single quote because of the . variable $row['familyid']Thanks for all of the suggestions. I suddenly saw it after thescientist mentioned the variable aspect. The double quotes weren't needed, but because it would normally be written familyid = " ", I needed to add familyid="' . $row['familyid'] . '" the variable inside the double quotes for a string.Thanks, again for helping me work through this!I'm now working on another related, very similar nested loop problem. I'll be back if I can't figure it out on my own.