Query Conundrum

[azimpact]

Hello,

 

I've got a query puzzle that is eluding me.

 

Background:

 

I've installed the Affiliate module on the ms2 release and every thing works dandy. What I'm trying to do is make a custom report that will show only the orders sold by manufacturter id.

 

So, I've modified the affialite_affiliate table to include a affiliate_partnerid. This partner ID corresponds to a specific manufacturers_id and the will never have more than one.

 

So far, the custome query I've built is as follows:

$query  = "SELECT products_name, products_model, products_price, products_quantity FROM orders_products";

$result = mysql_query($query) or die ("Dohhhh, it didn't work");
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = mysql_num_rows($result);

echo "<TABLE class=\"normal\" BORDER=\"0 \" width=\"100%\">\n";
echo "<TR valign=\"top\" bgcolor=\"lightblue\"><TD nowrap><center><strong>Name</strong></center></TD><TD nowrap><center><strong>Part#</TD><TD><center><strong>Price</strong></center></TR><TD><center><strong>Quantity</strong></center></TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
	echo "<TR  bgcolor=\"#EFDBA5\">\n";
} else { //if there isn't a remainder we will do the else
	echo "<TR  bgcolor=\"#C6CEBD\">\n";
}
echo "<TD nowrap><center>".$row['products_name']."</center></TD><TD ><center>".$row['products_model']."</center></TD><TD ><center>".$row['products_price']."</center></TD><TD ><center>".$row['products_quantity']."</center></TD>\n";
echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";

 

It works but lists all the orders so now I'm trying to form some custom join commands.

 

I need to have it join the orders_products table with the products table and then join the affialite-affiliate table and list only the orders where the affiliate_partnerid matches the manufactuters_id in the products table.

 

Thought I'd throw this up hoping some one who is way more versed in mysql commands could help me out.

 

Thanks!

[danjt]

Hi,

 

In a rush but hope this starts to help...

 

query = "SELECT * FROM orders_products, manufacturers WHERE manufacturer_id = manufacturer_id";

 

Dan