Jump to content

Order by value from another table *solved*


stualk

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?
Link to comment
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
Link to comment
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]
Link to comment
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
Link to comment
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]
Link to comment
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?
Link to comment
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.
Link to comment
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.
Link to comment
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!!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.