Jump to content

Archived

This topic is now archived and is closed to further replies.

stualk

Order by value from another table *solved*

Recommended Posts

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><font size=\"2\"><b>$product_name</b></font></td>
</tr>
<tr height='25' valign='top' align='left'>
    <td valign='top'><font size=\"2\"><i>$item_number</i></font></td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'><font size=\"2\">$description<br></font></td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'><font color='#000000' face=\"Verdana\" size=\"2\"><br></font></td>
<td><font size=\"2\"><br></font></td>
</tr>
");
}}[/code]

Anyone got any suggestions?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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><font size=\"2\"><b>$product_name</b></font></td>
</tr>
<tr height='25' valign='top' align='left'>
    <td valign='top'><font size=\"2\"><i>$item_number</i></font></td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'><font size=\"2\">$description<br></font></td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'><font color='#000000' face=\"Verdana\" size=\"2\"><br></font></td>
<td><font size=\"2\"><br></font></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]

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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><font size=\"2\"><b>$product_name</b></font></td>
</tr>
<tr height='25' valign='top' align='left'>
    <td valign='top'><font size=\"2\"><i>$item_number</i></font></td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'><font size=\"2\">$description<br></font></td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'><font color='#000000' face=\"Verdana\" size=\"2\"><br></font></td>
<td><font size=\"2\"><br></font></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?

Share this post


Link to post
Share on other sites
It means there is an error in the query.

After calling the query, add

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Please accept my apologies, it was well past my bedtime when I posted that  :-[

Regards
Huggie

Share this post


Link to post
Share on other sites
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><font size=\"2\"><b>$brand_name $product_name</b></font></td>
</tr>
<tr height='25' valign='top' align='left'>
    <td valign='top'><font size=\"2\"><i>$item_number</i></font></td>
</tr>
<tr height='25' valign='top' align='left'>
<td valign='top'><font size=\"2\">$description<br></font></td>
</tr>
<tr valign='top' align='left'>
<td valign='top' width='180'><font color='#000000' face=\"Verdana\" size=\"2\"><br></font></td>
<td><font size=\"2\"><br></font></td>
</tr>
");
}}}
[/code]
Thanks for your help.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.