Josh5442

Cool, I'm going to try that later. So basically I really need to understand joining and how you can relate things using them. I'm going to play with that code and see if I can understand piece by piece, I'll report back! Thanks again, Josh

^^ DISREGARD that post ^^ Okay, I'm SLOWLY understanding it. I'm going to play around with it a bit to see if it works the exact way I'm looking for. I got the database running and working: SELECT distinct(p.product_desc) FROM product p INNER JOIN prod_cat pc ON p.prodID = pc.prodID INNER JOIN subcategory s ON pc.subcatID = s.subcatID WHERE s.subcatID = 3 That way there's no duplicates. So far I can search by Brand/Subcategory/Category or a combination and it's working. Here is my first question: If a user is searching by Category 1.... what would the query be or how would I set it up so I can see all the subcategory/brand names linked to that category? I'm trying to create a narrow option... so the user can then narrow the results down. I'm really liking this so far =)

Wow, That's exactly what I'm looking for.... Here's the kicker though, I understand the logic behind it, no problem, but the code is a bit confusing to me. Is it basically the same as: SELECT product.product_description FROM product INNER JOIN prod_cat ON product.prodID = product_cat.prodID INNER JOIN subcategory ON product_cat.subcatID = subcategory.subcatID WHERE subcategory.catID = 1 I have no issues in creating the database in SQL... I'm going to need 5 tables, with the above listed in each table. The functionality of the joins and how the databases are related and how I would search for a cat/subcat/brand or any combination of them is confusing to me. I really want to understand the logic behind it as well as get it working. You don't understand how you are helping me Barand, I've been struggling hours a night with this for a month now. I'm happy to finally see there's potential for the database to be structured how I want it!

Can you explain that for me? I really don't understand how that structure works. Sorry, I'm a novice to SQL and PHP. I managed to get this far though EDIT: Okay you fixed it... but I still cannot picture how it's related =)

I just created that structure. I have no idea how to write the code. I'm going to need to join category + subcategory into the first table somehow. I need the code to be able to execute like so: select * from products WHERE category = category B AND sub_category = subcategory K; And return results.... So the SQL will run through and look for a match in the table: ... it sees entry 2 is a match and displays that record. [pre] Product ID | Category | Subcategory +=====+==========+===========+ | 1 | Category A | Subcategory B | | 1 | Category B | Subcategory K | | 2 | Category C | Subcategory A | [/pre] How do I join table 1 + 2 together in a query before I search the new combined table for matches?

Actually now that you mention it... it's true.... Well, now I'm confused on a good way of writing this. Basically I have a products table; [pre] Product ID | Category | Subcategory | Brand +=====+==========+===========+========= | 1 | Category A | Subcategory B | Brand A | | 2 | Category A | Subcategory C | Brand L | | 3 | Category C | Subcategory A | Brand A | [/pre] Okay, take product ID #1. Here is the issue......what if the product falls into multiple categories or subcategories? I have quite a few products with two MAJOR functions that could be used either or. One way to do it would be to enter the product twice.... but then if you are searching by all products where brand = brand A ... you will see the duplicate product, because it has two different categories or subcategories. So basically, I'd need another table with category + subcategory in it....correct? Question being how the heck would I code that? ??? Thanks Barand I'm assuming it will need to be like: [pre] Product ID | Brand +=====+========+ | 1 | Brand A | | 2 | Brand L | | 3 | Brand A | table2: Product ID | Category | Subcategory +=====+==========+===========+ | 1 | Category A | Subcategory B | | 1 | Category B | Subcategory K | | 2 | Category C | Subcategory A | [/pre]

That's what I figured, but I was hoping there was a way around it. Question then...... how would I join the second table to the first, then search by the combined table? I know I have to use a JOIN, I'm just not sure how. I'm basically looking for the query to run like this: SELECT count(category) from table1 WHERE category=$inputCat AND sub_category=$inputSubcat AND brand = $inputBrand; How it breaks down: The user inputs a category, subcategory and brand------the query will return all results matched where the category, subcategory and brands are matched. What it allows me to do is list as many categories for an item as needed, but only 1 subcategory & brand. Get me? [pre] Table1 prodID sub cat brand +======+===========+======+ | 1 | Subcat | Brand | | 2 | Subcat | Brand | | 3 | Subcat | Brand | | 4 | Subcat | Brand | | 5 | Subcat | Brand | | 6 | Subcat | Brand | Table2 prodID cat +======+===========+ | 1 | A | | 1 | B | | 2 | A | | 2 | C | | 3 | B | | 3 | D | [/pre] I really appreciate the help! I want to get it working so BAD! -Josh

BUMP For anyone who doesn't want to read through all the posts, I'm trying to do this with SQL/PHP: SQL TABLE: Column1 | Column2 | Column3 1 Toyota Ford 2 Ford Acura 3 Honda Ford Query -> Select * from table WHERE column1 = 1 OR column1 = 2 OR column1 = 3; Result: $cat = $array['column2 & column3 combined]; $count = $COUNT['column2 & column3 combined]; In a table or something: Result: (SEPERATELY) $cat = First array: Toyota $count = (1) Second Array: Ford $count = (3) Third Array: Acura $count = (1) Fourth Array: Honda $count = (1)

Well, that will give me only the results from one table grouped. Actually this works: (SELECT distinct(cat_name) from products WHERE $searchF) UNION (SELECT distinct(cat_2_name) from products WHERE $searchF);"; But now I need a way to count cat_name and cat_2_name together and display it... like: $count = $array['COUNT(cat_name + cat_2_name)']; I can't get any type of count working with this. Any advice? I need something similar to: (SELECT COUNT(distinct(cat_name)) from products WHERE $searchF) UNION (SELECT COUNT(distinct(cat_2_name)) from products WHERE $searchF);" $count = $array['COUNT(cat_name)']; $count2 = $array['COUNT(cat_2_name)']; $countTotal = $count + $count2; It acts really funny with anything similar.

Let me elaborate a bit... I have a database called products. In this database I have fields like: productID, category, category2, subcategory, brand What I'm trying to do is combine category + category2 to be recognized as the same column, but they need to be separate. The issue I'm having that I added another category is, what if a product falls into two categories? This way.... when you search, say, for category 'AUDIO' .... it will search the DB for AUDIO in category OR category2 and yield the results. It's working perfectly.... NOW... What if someone searches by just brand? I have a NARROW list that basically says, if NO category is searched, display ALL the categories that are related to the brand being searched. It works if I ARRAY both separately, but then duplicates are shown. I want it to return results in the category + category2 field together, that way no duplicates are shown in the NARROW column. Understand me? I really appreciate any help here... if anyone wants to email me OR instant message me, just ask. I'm seriously pulling my hair out because it's the first time I can't get something to work after two days. Oh yeah.....This works, SORT OF: (SELECT distinct(cat_name) from products WHERE $whereclause ) UNION (SELECT distinct(cat_2_name) from products WHERE $whereclause); That works for me, I'm not sure I understand how. Basically I array the cat_name and it's giving me results for BOTH, which is EXACTLY what I wanted. HOW can I get a count for how many products are in both combined though? I tried this: (This does not work) (SELECT distinct(cat_name), COUNT(cat_name) from products WHERE $searchF GROUP BY cat_name ASC) UNION (SELECT distinct(cat_2_name), COUNT(cat_2_name) from products WHERE $searchF GROUP BY cat_2_name ASC) I've tried different variations of that with no luck. What I need is basically ... (SELECT distinct(cat_name), COUNT(cat_name + cat_2_name) from products WHERE $searchF GROUP BY (cat_name + cat_2_name) ASC) UNION (SELECT distinct(cat_2_name), COUNT(cat_name + cat_2_name) from products WHERE $searchF GROUP BY (cat_name + cat_2_name) ASC) I'm so close... I know someone who's smarter then me can figure this out! =P

Okay, CONCAT will make it joined, that's not what I'm trying to do. I'm just trying to combine the results into one. SELECT * from products where (cat_name = $cat OR cat_2_name = $cat); while ($array = mysql_fetch_array($result)) { $cat = $array['cat_name']; $cat2 = $array['cat_2_name'] print $cat; print $cat2; } ^^ That works, but it will display it two different times. I need it to be like: SELECT (cat_name + cat_2_name) AS new_cat from products where new_cat = $cat GROUP by new_cat; while ($array = mysql_fetch_array($result)) { $cat = $array['new_cat']; print $cat; } ^^ That's how I need it, except that doesn't work =\

That gave me an SQL error... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' '.cat_2_name) as name_combo from products' at line 1 I'm kind of confused to exactly what you did, if I get it working I'll have a better idea. Thank you for the help!

Hi guys, I hope this is the correct section, please move me if it isn't! Anyways... I have an issue I cannot figure out for the life of me! I'm trying to run a query like so... SELECT cat_name, cat_2_name from products WHERE $searchF; NOW... I'm trying to get the results of cat_name and cat_2_name from multiple rows in a table, easy, all I would have to do is: $array1 = $array['cat_name']; $array2 = $array['cat_2_name']; And that works. But I'm looking to combine cat_name and cat_2_name into a single 'column' before I even get to that point. I know that you can use CONCAT and actually put the values together, like cat_name - cat_2_name, but that's not what I want. Say: ROW | CAT NAME | CAT_2_NAME | 1 | Cars | Trucks 2 | Cars | Planes How do merge those two so my output would be: $array1 = $array['combinedCATS']; WOULD ARRAY: CARS TRUCKS PLANES Get where I'm going? In short, I need to take the values from two columns and merge them together (not like merge First and Last name to make full name) .. but so all the items are in one column like: Cars,Planes,Cars,Trucks ... then to array that only. And if possible, group it so no duplicates are show, and maybe even count the combined group? Thank you for any input! I'm going nuts trying to get this to work. Feel free to ask me to further explain if you don't understand! OH, the closest I've come so far is: SELECT cat_name AS combined from products UNION SELECT cat_2_name from products WHERE $searchF $array1 = $array['combined'] But that had major flaws to it...

Err my fault, I had an error. But it still doesn't work, the SQL statement doesn't see it.... <?php function search($fieldName, $getName) { if($_GET['getName'] == NULL) return ""; else return " AND $fieldName = '$_GET[getName]'"; } $where = "active = 'yes'"; $where .= search("brand_name", $_GET['brand']); $where .= search("cat_name", $_GET['category']); $where .= search("s_cat_name", $_GET['subcategory']); $where .= search("product_type", $_GET['product_type']); /// $sql = "select * from products WHERE $where ORDER BY brand_name ASC"; $result = mysql_query($sql,$conn) or die(mysql_error()); if (mysql_num_rows($result) < 1) {header( 'Location: search_test.php?error=noProd&narrow=yes' ); } elseif (mysql_num_rows($result) > 0){ while ($array = mysql_fetch_array($result)) { $pid = $array['product_id']; $text = $array['html']; $catname = $array['cat_name']; $scatname = $array['s_cat_name']; $bname = $array['brand_name']; $ptype = $array['product_type']; $pthumb = $array['thumbnail']; $ptitle = $array['title']; $pdescription = $array['description']; $pkeywords = $array['keywords']; $table .= "Removed"; } } ?>

Okay it worked halfway, it sees active='yes', but it does not see the function inside the SQL statement....Can you even insert a function inside and SQL statement? Code is: <?php function search($fieldName, $getName) { if($_GET['getName'] == NULL) return ""; else return " AND $fieldName = '$_GET[getName]'"; } $where = "active = 'yes'"; $where .= search("brand_name", "$_GET[brand]"); $where .= search("cat_name", "$_GET[category]"); $where .= search("s_cat_name", "$_GET[subcategory]"); $where .= search("product_type", "$_GET[product_type]"); /// $sql = "select * from products WHERE $where ORDER BY brand_name ASC"; $result = mysql_query($sql,$conn) or die(mysql_error()); if (mysql_num_rows($result) < 1) {header( 'Location: search_test.php?error=noProd&narrow=yes' ); } elseif (mysql_num_rows($result) > 0){ while ($array = mysql_fetch_array($result)) { $pid = $array['product_id']; $text = $array['html']; $catname = $array['cat_name']; $scatname = $array['s_cat_name']; $bname = $array['brand_name']; $ptype = $array['product_type']; $pthumb = $array['thumbnail']; $ptitle = $array['title']; $pdescription = $array['description']; $pkeywords = $array['keywords']; $table .= "Removed"; } } ?>