SQL SELECT statement and combining MULTIPLE columns into one column

[Josh5442]

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...

[cooldude832]

<?php
$q = "Select cat_name as cat_1, cat_2_name as cat_2, CONCAT(cat_name.' '.cat_2_name) as name_combo from `table`";
?>

CONCAT allows you to join fields, strings together as formatted output

[Josh5442]

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!

[Josh5442]

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 =\

[activeserver]

Maybe this helps:

(SELECT distinct(cat_name) from products WHERE $whereclause ) 
UNION 
(SELECT distinct(cat_2_name) from products WHERE $whereclause);

[aschk]

I suggest some normalisation, as this looks wrong to me.

What is the relationship?

 

You appear to have "categories" listed side by side, neither of which is a child (more specific grouping) of the other. What is the real life mapping you're attempting to achieve here?

[Josh5442]

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

[Barand]

is mysql's GROUP_CONCAT function what you are looking for?

 

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

[Josh5442]

is mysql's GROUP_CONCAT function what you are looking for?

 

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

 

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.

[Josh5442]

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)

[Barand]

try

/*
SQL TABLE:
productID | Cat1 | Cat2
     1        Toyota    Ford
     2         Ford      Acura
     3         Honda    Ford
*/
$res = mysql_query ("SELECT productID , Cat1 , Cat2 FROM tablename");
$data = array();
while (list($prod, $cat1, $cat2) = mysql_fetch_row($res))
{
    $data[] = $cat1;
    $data[] = $cat2;
}

$counts = array_count_values($data);

foreach ($counts as $cat => $count)
{
    echo "$cat - $count<br/>";
}
?>

[Barand]

the correct way would be a second table

 

[pre]

    prod      cat

  +======+===========+

  |  1  | Toyota    |

  |  1  | Ford      |

  |  2  | Ford      |

  |  2  | Acura    |

  |  3  | Honda    |

  |  3  | Ford      |

[Josh5442]

the correct way would be a second table

 

[pre]

    prod      cat

  +======+===========+

  |   1  | Toyota    |

  |   1  | Ford      |

  |   2  | Ford      |

  |   2  | Acura     |

  |   3  | Honda     |

  |   3  | Ford      |

 

 

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

 

[Barand]

It seems odd that there are several categories for a product but only a single subcategory. So what exactly is the relationship between cat and subcat? Are subcats related at all to categories?

[Josh5442]

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]

[gluck]

What is the issue with the code? You structure seems right.

[Josh5442]

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?

 

[Barand]

Table structure should perhaps be

[[pre]

category        subcategory              prod_cat          product            brand

===========      =============            =========        ===========        =============

catID    ---+  subcatID    ---+        id          +---  prodID        +--- brandID

category    |  subcategory    |        prodID  ----+    product_desc  |    brandname

            +-- catID          +------  subcatID          brandID    ---+ 

                                                           

[/pre]

 

edited: forgot the multiple cats

[Josh5442]

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 =)

[Barand]

(See edited version)

 

Each category has one or more subcategories.

A product can belong to one or more cat/subcategories.

A product has a single brand

 

To find all products in cat1

SELECT p.product_desc 
FROM product p
    INNER JOIN prod_cat pc ON p.prodID = pc.prodID
    INNER JOIN subcategory s ON pc.subcarID = s.subcatID
WHERE s.catID = 1 

[Josh5442]

(See edited version)

 

Each category has one or more subcategories.

A product can belong to one or more cat/subcategories.

A product has a single brand

 

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!

[Josh5442]

^^ 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 =)

[Barand]

SELECT distinct c.category, s.subcategory, b.brandname

FROM product p

    INNER JOIN prod_cat pc ON p.prodID = pc.prodID

    INNER JOIN subcategory s ON pc.subcatID = s.subcatID

    INNER JOIN category c ON s.catID = c.catID

    INNER JOIN brand b ON p.brandID = b.brandID

WHERE s.catID = 1

[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