SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
Apr 23, 2004, 11:04 #1
- Join Date
- Apr 2000
- Location
- Los Angeles, California
- Posts
- 1,008
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Please help...very complicated query.
I have 100 produts in 25 subcategories in 10 categories. They are connected via "connecter tables"...anyone know the real name of those?? For example...subcategory_product has too colums linking subcategory IDS with product IDS. This way products can be in multiple subcategories, and subcategories can be in multiple cateogries.
My goal is to select any number of category and/or subcateogires and/or products and them update all the products that fall under the cateogories selected and the subcategories selected or are explicitly selected.
So far I have...
Code:SELECT DISTINCT products.* FROM categories, category_subcategory, subcategories, subcategory_product, products WHERE (categories.category_id=$category_id AND categories.category_id=category_subcategory.category_id AND category_subcategory.subcategory_id=subcategory_product.subcategory_id AND subcategory_product.product_id=products.product_id) OR (subcategories.subcategory_id=$subcategory_id AND subcategories.subcategory_id=subcategory_product.subcategory_id AND subcategory_product.product_id=products.product_id) OR (products.product_id=$product_id)
There must be a better way to do this??
The goal is to give it a list of cateogries, subcategories, and products, and return all products falling under those criterias.
ONRE MORE THING...
Is there any way to UPDATE products SET product_price=2 WHERE product_id=(AN ARRAY)?
I have an array of the product_ids that I want to update, but am currently looping through them using a foreach loop and updating them seperatly, creating numerous quieries to the database...
-
Apr 23, 2004, 11:34 #2
- Join Date
- May 2003
- Location
- CT, USA
- Posts
- 32
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ok i dont understand the first question, but the second question i can answer. Yes you can like this.
UPDATE products SET product_price=2 WHERE product_id=firstitem OR product_id=seconditem OR product_id=thirditem OR product_id=fourthitem.....
Im pretty sure that will workPromote Your Site For Free:
Go Radic Banner Exchange
-
Apr 23, 2004, 11:37 #3
Originally Posted by jumpthru
Originally Posted by jumpthru
Originally Posted by jumpthru
Code:UPDATE products SET product_price=2 WHERE product_id IN ('QX324', 'QY553', 'YY342') -or- UPDATE products SET product_price=2 WHERE product_id IN (314234, 123434, 123412, 883921) -or even- UPDATE products SET product_price = 2 WHERE product_id IN (SELECT product_id FROM products LEFT OUTER JOIN blah-blah-blah...) but in this instance, the SELECT subquery (the one in the parenthesis) would be a complex query of sorts (typically lots of joins and such) otherwise you could just use a normal WHERE clause without the subquery/IN combo.
Last edited by StephenBauer; May 26, 2004 at 11:16.
-
Apr 23, 2004, 11:40 #4
Check out the "IN" SQL clause. (Not sure if mySQL supports it offhand...and no time to look that up.)
Originally Posted by GoRadic.Com
Last edited by StephenBauer; May 26, 2004 at 11:15.
-
Apr 23, 2004, 12:08 #5
I had some time at lunch to kill...
A select statement:
Code:select c.CategoryID, c.CategoryName, s.SubCategoryID, s.SubCategoryName, p.ProductID, p.ProductName from [Catgories] c left outer join [Category_SubCategory] cs on c.CategoryID = cs.CategoryID left outer join [SubCategories] s on cs.SubCategoryID = s.SubCategoryID left outer join [SubCategory_Product] sp on s.SubCategoryID = sp.SubCategoryID left outer join [Products] p on sp.ProductID = p.ProductID where c.CategoryID = 2203 order by c.CategoryID, s.SubCategoryID, p.ProductID
You can then use the above code, slightly modified, as a subquery in your UPDATE clause:
Code:UPDATE Products SET ProductMarkup = 1.20 WHERE ProductID IN ( select p.ProductID from [Catgories] c left outer join [Category_SubCategory] cs on c.CategoryID = cs.CategoryID left outer join [SubCategories] s on cs.SubCategoryID = s.SubCategoryID left outer join [SubCategory_Product] sp on s.SubCategoryID = sp.SubCategoryID left outer join [Products] p on sp.ProductID = p.ProductID where c.CategoryID = 2203 order by c.CategoryID, s.SubCategoryID, p.ProductID )
This type of layout can be used for security as well but with a branch for "roles" support.Last edited by StephenBauer; May 26, 2004 at 11:15.
-
Apr 25, 2004, 22:39 #6
- Join Date
- Apr 2000
- Location
- Los Angeles, California
- Posts
- 1,008
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you to everyone for taking the time to reply. I wish I was good at SQL casue I know the code I finally came up wtih can be optimised but its really hard to get help (nothing to do with your guys lack of skills) when you dont have access to all the code and databases. Here is what I ended up with.
(and remember, each array edit_adjust_subcategories_criteria, edit_adjust_categories_criteria, edit_adjust_products_criteria, edit_adjust_colors_criteria gets looped through and can have up to 100 memebers)
PHP Code:// Make adjusts
if ($yes=="Yes") {
// Retrieve products to adjust
if (isset($edit_adjust_products_criteria)) {
foreach ($edit_adjust_products_criteria as $value) {
$edit_adjust_products_list[]=$value;
}
}
if (isset($edit_adjust_categories_criteria)) {
foreach ($edit_adjust_categories_criteria as $value) {
$result=mysql_query("SELECT DISTINCT product_id FROM category_subcategory, subcategory_product
WHERE category_subcategory.category_id=$value AND category_subcategory.subcategory_id=subcategory_product.subcategory_id");
while($row=mysql_fetch_array($result)) {
$product_id=$row['product_id'];
$edit_adjust_products_list[]=$product_id;
}
}
}
if (isset($edit_adjust_subcategories_criteria)) {
foreach ($edit_adjust_subcategories_criteria as $value) {
$result=mysql_query("SELECT DISTINCT product_id FROM subcategory_product WHERE subcategory_id=$value");
while($row=mysql_fetch_array($result)) {
$product_id=$row['product_id'];
$edit_adjust_products_list[]=$product_id;
}
}
}
if (isset($edit_adjust_colors_criteria)) {
foreach ($edit_adjust_colors_criteria as $value) {
$result=mysql_query("SELECT DISTINCT product_id FROM
product_color WHERE color_id=$value");
while($row=mysql_fetch_array($result)) {
$product_id=$row['product_id'];
$edit_adjust_products_list[]=$product_id;
}
}
}
// Remove duplicate products
$edit_adjust_products_list=array_unique($edit_adjust_products_list);
// Adjust products
if (isset($edit_adjust_products_list)) {
foreach ($edit_adjust_products_list as $value) {
// Adjust prices
$result=mysql_query("UPDATE prices SET price_price=price_price*" . (1+($edit_adjust_price/100)) . " WHERE price_product=$value");
// Adjust personalization prices
$result=mysql_query("UPDATE personalization_prices SET personalization_price=personalization_price*" .
(1+($edit_adjust_personalization_price/100)) . " WHERE
personalization_product=$value");
echo (mysql_error());
// Insert or remove subcategories
if (isset($edit_adjust_subcategories)) {
if ($edit_adjust_subcategories_choice==0) {
foreach ($edit_adjust_subcategories as $value2) {
$result=mysql_query("INSERT subcategory_product SET subcategory_id=$value2, product_id=$value");
}
}
else if ($edit_adjust_subcategories_choice==1) {
foreach ($edit_adjust_subcategories as $value2) {
$result=mysql_query("DELETE FROM subcategory_product WHERE subcategory_id=$value2 AND product_id=$value");
}
}
}
// Insert or remove colors
if (isset($edit_adjust_colors)) {
if ($edit_adjust_colors_choice==0) {
foreach ($edit_adjust_colors as $value2) {
$result=mysql_query("INSERT product_color SET color_id=$value2, product_id=$value");
}
}
else if ($edit_adjust_colors_choice==1) {
foreach ($edit_adjust_colors as $value2) {
$result=mysql_query("DELETE FROM product_color WHERE color_id=$value2 AND product_id=$value");
}
}
}
}
}
$message.="The mass adjusts were made.<br>";
}
PSS Arhge!!! Now the php tags are breaking from my code....!!
-
Apr 25, 2004, 22:49 #7
- Join Date
- Apr 2000
- Location
- Los Angeles, California
- Posts
- 1,008
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I just did a test, and with everything selected on the page, there is a total of 4005 queries made...
Hmm...
-
Apr 26, 2004, 00:54 #8
- Join Date
- Apr 2004
- Location
- TX
- Posts
- 71
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Stephen, if you don't mind me asking, why the outer joins instead of inner joins?
-
Apr 26, 2004, 08:21 #9
Originally Posted by null
Also, at times, it may be nice to include catagories that do not have sub-categories or sub-categories that do not have products (which the left outer joins include).Last edited by StephenBauer; May 26, 2004 at 11:15.
-
Apr 26, 2004, 08:40 #10
- Join Date
- Apr 2004
- Location
- TX
- Posts
- 71
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by StephenBauer
-
Apr 26, 2004, 09:25 #11
Originally Posted by null
You are right about the smaller result set and increased performance if going with inner joins!
Edit: I just re-read this thread...he did mention "product updating"...oh heck!Last edited by StephenBauer; May 26, 2004 at 11:14.
Bookmarks