...

View Full Version : Help making this unique db query



lansing
02-01-2007, 05:00 AM
I have spent hours & hours on this query & can't get it to work. I have three tables to use in this query. I have the following tables to use.

table_a houses the categories_id, parent_id
table_b houses the products_id, categories_id
table_c houses the products_id, products_price
table_d houses the products_id, products_name
table_e houses the products_id, specials_price, start_date, end_date

I am posting a category_id number from a form.

I need to query table_a for all categories_id where the parent_id equals the posted category_id. This is simple to do.

I then need to query table_b for products_id where the categories_id equals the categories_id pulled from table_a.

I then need to query table_c for products_price where the products_id equals the products_id pulled from table_b.

I then need to query table_d for products_name where the products_id equals the products_id pulled from table_b.

I then need to query table_e for specials_price, start_date & end_date where the products_id equals the products_id pulled from table_b.

This is pulling all products from the database so I need to be able to list them on a page. I do know this is a complicated query & I would appreciate any & all help that I could get.

I am just not experience in using join statements in my MySQL query statements.

Someone else in the php section said this should have worked, but it didn't.

$category_id = $HTTP_POST_VARS['category_id'];
$qry = "SELECT a.categories_id, b.products_id, c.products_price, d.products_name, e.specials_price, e.start_date, e.end_date
FROM table_a a, table_b b, table_c c, table_d d, table_e e
WHERE a.parent_id = '$category_id'
AND b.categories_id = a.categories_id
AND c.products_id = b.products_id
AND d.products_id = c.products_id
AND e.products_id = d.products_id";

guelphdad
02-01-2007, 02:11 PM
In my opinion tables b, c and d should all be a single table.

lansing
02-01-2007, 02:58 PM
In my opinion tables b, c and d should all be a single table.You are sooo right. I don't know why the previous person make this system like this. I wish table c & d was together, but the table b needs to be separate since this system allows us to copy products to be displayed in several categories if the product is useful in more than one category. This table only houses the products_id & its category_id. This way if I want the products_id 10 to show in category 4 & 35 & just enter two new records into this table. I think this would be easier than listing the categories in the db separated by commas then using explode(",",$category_id) to find the categories.

Can you help me with this anyways?

lansing
02-01-2007, 05:17 PM
I have been messing around with this learning a little about join. I have this code below that seems to work, but it list every record twice. I have just posted the raw code as I edited out certain data on the first post.


In that LEFT JOIN () I put the categories_id or parent_id since I have subcategories. My categories go 3 deep...I have Balls,-> Color -> Brands. If I query for all Brands I get data returned, if I query for Color I get data returned, but if I query the main category Balls & get a blank screen.

This prices aren't right either...I don't even have the MilTec Tactical Blue Paintballs in the specials table yet. It has never been on sale before so it shouldn't even have a sale price.


$specials_qudery = tep_db_query("select ptc.products_id, ptc.categories_id, pd.products_name, p.products_price, s.specials_new_products_price
from " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc, " . TABLE_PRODUCTS_DESCRIPTION . " pd,
" . TABLE_PRODUCTS . " p, " . TABLE_SPECIALS . " s LEFT JOIN " . TABLE_CATEGORIES . " c on (c.categories_id = '" . $HTTP_POST_VARS['categories_id'] . "' or c.parent_id = '" . $HTTP_POST_VARS['categories_id'] . "')
where pd.products_id = ptc.products_id and ptc.categories_id = c.categories_id and p.products_id = ptc.products_id");

while ($producdt = tep_db_fetch_array($specials_qudery)) {

echo '<hr>Category ID: s' . $producdt['categories_id'] . ' - Product Name: ' . $producdt['products_name'] . ' - List: ' . $producdt['products_price'] . ' - Sale: ' . $producdt['specials_new_products_price'] . '<hr>';
}


This is what is being displayed...
Category ID: s19- Product Id: 30 - Product Name: Nelson Anarchy Chaos Paint Blue Shell Yellow Fill - List: 59.9900 - Sale: 50.9915
Category ID: s19- Product Id: 33 - Product Name: Protection Plan 1099 - List: 50.0000 - Sale: 50.9915
Category ID: s18- Product Id: 34 - Product Name: MilTec Tactical Blue Paintballs - List: 59.9900 - Sale: 50.9915
Category ID: s19- Product Id: 30 - Product Name: Nelson Anarchy Chaos Paint Blue Shell Yellow Fill - List: 59.9900 - Sale: 250.0000
Category ID: s19- Product Id: 33 - Product Name: Protection Plan 1099 - List: 50.0000 - Sale: 250.0000
Category ID: s18- Product Id: 34 - Product Name: MilTec Tactical Blue Paintballs - List: 59.9900 - Sale: 250.0000

guelphdad
02-01-2007, 05:35 PM
Read this article (http://guelphdad.wefixtech.co.uk/sqlhelp/joins_basic.shtml) on using JOINS. Don't mix list syntax and OUTER JOINS.

Read this article (http://sqllessons.com/categories.html) so you can understand categories and subcategories.

Now instead of trying to do everything at once join tables A and B above and get the info you need out of them. Got that far? Good. Now add table C. Is that working correctly? Then add successive tables until you get all the information you need.

When something "doesn't work" that doesn't tell folks exactly what the problem is. Pinpoint things with error messages or an explanation beyond that so folks know where the problem might lie.

Oh yes, last, but not least, take the time to format your posts so that they are legible and don't require left/right scrolling as well as up/down. It is easier if you keep terms all on one line like in the JOINS article posted above.

lansing
02-01-2007, 08:43 PM
I read over those links. Some of it I understand...I have this code below that I copied part of the code from that Categories & Subcategories page you gave. I am going to get the data from table a & b then try for the rest.

I am trying to get all categories id's below the category id that I am posting. I might post a parent id & then I might post a 3 level deep id. The code I have below does pull the categories id's for every category right below the category id I posted & it then gets the products id for those categories it found. Can you help me with the code to get it to pull the other levels below?

The part about the code not working...I just get blank page with no errors to provide. I did test this & I can echo before the loop statement, but after it it wont display any data & that is where the page contents is & I think that is why I get a blank page.


select
root.categories_id,
root.parent_id,
down1.products_id
from " . TABLE_CATEGORIES . " as root
left outer join " . TABLE_PRODUCTS_TO_CATEGORIES . " as down1
on root.categories_id = down1.categories_id
where root.parent_id = '" . $HTTP_POST_VARS['categories_id'] . "'



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum