Order by value from another table solved

stualk · September 20, 2006

Hi Chaps,

I'm struggling with the order of the results i'm calling in from a table. The problem is I want to display products from one table but in the order specified in another table. The table that specifies the order is called 'product_types'. In there are two fields, one called 'product_type_name' and the other 'ordervalue'.

I need to use the 'ordervalue' from 'product_types' for each product displayed when called in from 'products_table' where 'product_type_name' is equal to 'product_type'.

(For example: a product_type_name might be 'Lamp Shade' with the ordervalue of '1'. I then need all the products from 'products_table' to show all 'Lamp Shade' products first, then the products with ordervalue of 2, and so on...)

The code i'm using is below and it works perfectly, except for the order the products appear in.

[code]$NumRows=mysql_query("SELECT * FROM products_table where brand_name='$brand_name'&&visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("SELECT * FROM products_table where brand_name='$brand_name'&&visible = 'Yes'") or die("");
for ($i=1; $i<=$NumRows; $i++) {
if ($row = mysql_fetch_array($Result)) {
$id = $row["id"];
$product_name = $row["product_name"];
$item_number = $row["item_number"];
$description = $row["description"];
$product_thumbnail = $row["product_thumbnail"];

echo("
<tr valign='top'>
<td width='220' rowspan='3'><div align='left'><a href=javascript:MM_openBrWindow('image_enlarge.php?id=$row[id]','enlarge','width=740,height=575,resizable=yes,status=no,toolbar=no,scrollbars=yes')><img border='1' src='URL HERE/$product_thumbnail'></a></div></td>
<td>$product_name</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$item_number</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$description </td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'> </td>
<td> </td>
</tr>
");
}}[/code]

Anyone got any suggestions?

HuggieBear · September 20, 2006

Give this code a try in place of what you've got.

[code]
<?php
$Result = mysql_query("SELECT p.* t.ordervalue
FROM products_table p, product_types t
WHERE p.product_type = t.product_type_name
AND p.brand_name = '$brand_name'
AND p.visible = 'Yes'
ORDER BY t.ordervalue, p.brand_name");
?>
[/code]

Regards
Huggie

stualk · September 21, 2006

That's not quite worked. It could be one of two things. One is that i've done something wrong! The other I think could be that when we say 'WHERE p.product_type = t.product_type_name' etc, have we called these values in before we ask it to check that line?

All I'm getting with the following code is an image box with a red cross. It should display about 40 products in total with an image to the left of the product details.

[code]$Result = mysql_query("SELECT p.* t.ordervalue
FROM products_table p, product_types t
WHERE p.product_type = t.product_type_name
AND p.brand_name = '$brand_name'
AND p.visible = 'Yes'
ORDER BY t.ordervalue, p.brand_name");

echo("
<tr valign='top'>
<td width='220' rowspan='3'><div align='left'><a href=javascript:MM_openBrWindow('image_enlarge.php?id=$row[id]','enlarge','width=740,height=575,resizable=yes,status=no,toolbar=no,scrollbars=yes')><img border='1' src='http://www.net-strategy.co.uk/leisurebrands/product_thumbnails/$product_thumbnail'></a></div></td>
<td>$product_name</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$item_number</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$description </td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'> </td>
<td> </td>
</tr>
");[/code]

I also tried it by including my original code that calls from the database at the top, which is these two lines above the code above, but that didn't work either:

[code]$NumRows=mysql_query("SELECT * FROM lb_products where brand_name='$brand_name'&&visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);[/code]

HuggieBear · September 21, 2006

[quote author=stualk]
I need to use the 'ordervalue' from 'product_types' for each product displayed when called in from 'products_table' where 'product_type_name' is equal to 'product_type'.
[/quote]

I'm assuming from this that the data in product_type column in the products_table is the same as the product_type_name in the product_types table?

If it is then the above code should have worked, assuming you have the column names correct.

Can you confirm that the above statement is correct and also, do you have phpMyAdmin installed?

Regards
Huggie

Barand · September 21, 2006

What did you do with the chunk of code that you had after the query? IE

[code] for ($i=1; $i<=$NumRows; $i++) {
if ($row = mysql_fetch_array($Result)) {
$id = $row["id"];
$product_name = $row["product_name"];
$item_number = $row["item_number"];
$description = $row["description"];
$product_thumbnail = $row["product_thumbnail"];
[/code]

stualk · September 21, 2006

Yeah I tried adding that bit in as well so that the code looked like this...

[code]
$NumRows=mysql_query("SELECT * FROM products_table where brand_name='$brand_name'&&visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("SELECT p.* t.ordervalue FROM products_table p, product_types t WHERE p.product_type = t.product_type_name AND p.brand_name = '$brand_name' AND p.visible = 'Yes' ORDER BY t.ordervalue, p.brand_name");
for ($i=1; $i<=$NumRows; $i++) {
if ($row = mysql_fetch_array($Result)) {
$id = $row["id"];
$product_name = $row["product_name"];
$item_number = $row["item_number"];
$description = $row["description"];
$product_thumbnail = $row["product_thumbnail"];

echo("
<tr valign='top'>
<td width='220' rowspan='3'><div align='left'><a href=javascript:MM_openBrWindow('image_enlarge.php?id=$row[id]','enlarge','width=740,height=575,resizable=yes,status=no,toolbar=no,scrollbars=yes')><img border='1' src='URL HERE'></a></div></td>
<td>$product_name</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$item_number</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$description </td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'> </td>
<td> </td>
</tr>
");
}}[/code]

That didn't work either. It returned the error

"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in URL on line 178" five times! I get this error regularly with PHP but I haven't got a clue what it means!

Any ideas what it could be?

Barand · September 21, 2006

It means there is an error in the query.

After calling the query, add

if (!$Result) die (mysql_error());

stualk · September 21, 2006

Ah right, that tells me what the error is, excellent! Here's the error...

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '.ordervalue FROM products p, product_types t WHERE p.prod

So there's something not quite right with the code I'm using in my query. I never use the type of code above so i'm not sure what part of the code is incorrect.

Barand · September 21, 2006

Needs a comma between

p.* , t.ordervalue

stualk · September 21, 2006

I can't believe that all my problems were due to a missing comma!!!

That now works perfectly! Thank you very much for your help with this.

HuggieBear · September 21, 2006

Please accept my apologies, it was well past my bedtime when I posted that :-[

Regards
Huggie

stualk · September 25, 2006

Hi Chaps,

Got another query on this issue I had last week. The above all worked fine when viewing by product brand but there is another part of the site that i'm working on where people can also view products by product type. Once you have selected the product type it should display the products in brand order from the brands table (in a similar way to above where the products are displayed in type order from the product types table - this works perfectly).

Using the same code but changing the obvious bits so that it is relevant to the brand order rather than type order, it isn't quite working. The code i'm using is below. I can't think for the life of me why this isn't working. The order the products are being displayed seems a bit random rather than displaying in brand order. Can anyone see what might be wrong with this code...

[code]$NumRows=mysql_query("SELECT * FROM products_table where product_type='$product_type'&&visible = 'Yes'");
$NumRows=mysql_num_rows($NumRows);
$Result = mysql_query("SELECT p.*, t.* FROM products_table p, brands_table t WHERE p.brand_name = t.the_brand_name AND p.product_type = '$product_type' AND p.visible = 'Yes' ORDER BY t.brand_ordervalue");
if (!$Result) die (mysql_error());
for ($i=1; $i<=$NumRows; $i++) {
if ($row = mysql_fetch_array($Result)) {
$id = $row["id"];
$brand_name = $row["brand_name"];
$product_name = $row["product_name"];
$item_number = $row["item_number"];
$description = $row["description"];
$product_thumbnail = $row["product_thumbnail"];

echo("
<tr valign='top'>
<td width='220' rowspan='3'><div align='left'><a href=javascript:MM_openBrWindow('image_enlarge.php?id=$row[id]','enlarge','width=740,height=575,resizable=yes,status=no,toolbar=no,scrollbars=yes')><img border='1' src='URL HERE/$product_thumbnail'></a></div></td>
<td>$brand_name $product_name</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$item_number</td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'>$description </td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'> </td>
<td> </td>
</tr>
");
}}}
[/code]
Thanks for your help.

Barand · September 25, 2006

You have ".. ORDER BY t.brand_ordervalue"

stualk · September 25, 2006

The brand_ordervalue field is correct. That's the field in products_table that specifies the order they should appear in. I gave the field a slightly different name to try and avoid any possible conflict. Do you think this could be causing me a problem?

Barand · September 25, 2006

If you have this
[pre]
Brand | Ordervalue
-------+-----------------
A | 4
B | 2
C | 1
D | 3
[/pre]

When you order by ordervalue column you would get

Brand sequence - C B D A

stualk · September 25, 2006

Just figured out what the problem was and it wasn't the code despite hours of tearing my hair out! I hadn't specified an order number for some of the brands. I have been through and added order numbers for all of the brands and now everything works perfectly!!

Thanks once again for your help, it's much appreciated - novices huh!!

Sign In

Order by value from another table *solved*

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information

Order by value from another table solved