Query Help

[azimpact]

I'm tyring to pull a custom query and can't seen to get it straight.

 

I'm trying creat a custom order report page that will list the orders based on the manufacturers_id for affiliates. The affiliates are actually the manufacturers so lets say JohnDoe is going to click my custom sales page and it will show only orders that were sold with the JohnDoe manufacturer_id.

 

Unforturnatly, the products_orders table does not list the manfacturers_id so I know have to do join a few tables to do this.

 

The closest I've got so far is this query:

 

 $query = "SELECT * FROM orders_products JOIN products WHERE  manufacturers_id = '$partid' ";

 

The $partid is the equivelant of the manufactuters_id and is present in the affiliate_affiliate table. I've got the page correctly identifying different affiliates partid but I can't seem to get that darn join query to what I need it to do.

 

Any help would be GREATLY appreciated...

[mraccine]

Try:

SELECT * FROM orders_products op, products p WHERE op.products_id = p.products_id and manufacturers_id = '$partid'

[azimpact]

Try:

SELECT * FROM orders_products op, products p WHERE op.products_id = p.products_id and manufacturers_id = '$partid'

 

Thanks for the code!

 

It doesn't error out but it's not giving a result either. Just a blank row.

 

Your code however gave me a different perspective so I'll play around with it and post if I come with the answer.

[azimpact]

I came at it from another angle. Instead of using the partnerid, I tried to extract that id from the affiliate_affiliate table.

 

So, only the orders that match the affiliate_partnerid in the affiliate_affiliate table will show. The partnerID are the same values as the ManufacturersID.

 

$query = "SELECT * FROM orders_products op, products p , affiliate_affiliate aa WHERE op.products_id = p.products_id and p.manufacturers_id = aa.affiliate_partnerid ";

 

I was thinking this but it just produces a blank row. No errors?

 

///scratch that thought, it has use the partid other wise it does not know which affialite it is. The partID is tied to the session affiliate_id.

[azimpact]

Thought I would post my solution in case others run into the same thing.

 

My mistake was confusing the manfacturers_id with the manufacturers_name. I needed to query against the name and once I my brain lock left, the query was fairly simple. Here is the query the works and produces the result I'm looking for.

 

$query = "SELECT * FROM products p JOIN orders_products op ON p.products_id = op.products_id JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id WHERE m.manufacturers_name = '$partid' ORDER BY orders_id DESC";