...

View Full Version : Help making this unique db query



lansing
01-30-2007, 01:02 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.

martialtiger
01-30-2007, 01:18 AM
Try this


$category_id = $_REQUEST['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";


Good luck!

lansing
01-30-2007, 02:57 AM
I am not getting anything displayed w/what you have. How do I display every record found using that query on the page?

martialtiger
01-30-2007, 04:10 AM
Your initial question was just regarding construction of the sql statement. You would want to get the results from the query and display it accordingly.

You would have to create a loop and format how you'd want the values to be displayed.

Just so you know that you're retrieving values add this


var_dump (@mysql_fetch_assoc($rslt));


Good luck!

lansing
01-31-2007, 12:13 AM
Your initial question was just regarding construction of the sql statement. You would want to get the results from the query and display it accordingly.

You would have to create a loop and format how you'd want the values to be displayed.

Just so you know that you're retrieving values add this


var_dump (@mysql_fetch_assoc($rslt));


Good luck!I was using the first code below & it wouldn't work so that is why I was asking for more help. You can see in the other code sample that I added what you said & it didn't work. I might not have added it correctly so if I didn't add it where it should be.

I know it isn't the number_format() function since the product_id wont even display.


while ($page = mysql_fetch_array($qry))
{
$commision_pd = $page['product_id'];
$display_page_paid_nds = number_format($page['products_price'];, 2);
$display_page_paid_wds = "$ " . number_format($page['specials_new_products_price'];, 2);
}



while ($page = var_dump (@mysql_fetch_assoc($qry)))
{
$commision_pd = $page['product_id'];
$display_page_paid_nds = number_format($page['products_price'];, 2);
$display_page_paid_wds = "$ " . number_format($page['specials_new_products_price'];, 2);
}



while ($page = @mysql_fetch_assoc($qry))
{
$commision_pd = $page['product_id'];
$display_page_paid_nds = number_format($page['products_price'];, 2);
$display_page_paid_wds = "$ " . number_format($page['specials_new_products_price'];, 2);
}

StupidRalph
01-31-2007, 02:42 AM
while ($page = var_dump (@mysql_fetch_assoc($qry)))
{
$commision_pd = $page['product_id'];
$display_page_paid_nds = number_format($page['products_price'];, 2);
$display_page_paid_wds = "$ " . number_format($page['specials_new_products_price'];, 2);
}


Martial was stating for you to add that as a debugging feature. You can add it exactly how they typed it.


var_dump(@mysql_fetch_assoc($qry);

Also, you would have faired better by posting this is the MySQL portion of this site as your problem isn't with the PHP. I suggest that you get the query working in MySQL before trying to implement it in your PHP. Have a look at this page concerning joins written by CF's own Guelphdad. Join Basics (http://www.guelphdad.wefixtech.co.uk/sqlhelp/joins_basic.shtml) Also, you might want to read some of the other articles as well.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum