[SOLVED] DINSTINCT complications...

[NerdConcepts]

This is what I have

 

Table Name: "Vendors"

Fields: "id","name","cat_id","cat_id2","cat_id3","cat_id4","cat_id5"

 

Table Name: "Category"

Fields: "cat_id","cat_title"

 

Total Categories of about 130 at this point.

 

What I am trying to do is ONLY display categories that have a vendor associated with them. This would be easy if I wasn't allowing vendors to be listed in up to 5 categories. Because of this I cannot use just the "SELECT DISTINCT" because it grabs separate DISTINCT cat_ids. For example right now in one of the vendors cat_id=11 and in other cat_id3=11, so it display the category with the cat_id of 11 twice. Is there maybe a way I can run maybe 5 different querys getting each of the cat_id fields unique id's and then comparing all 5 cat_ids for the vendors and then just keeping the ones that are repeated? So each cat_id can only be used once, and only if there is a vendor associated with them?

[teng84]

show your sql i gues its better to understand that lol

[hitman6003]

SELECT cat_title, count(*) 
FROM Vendors v
  LEFT JOIN Category c1 ON v.cat_id = c1.cat_id
  LEFT JOIN Category c2 ON v.cat_id2 = c2.cat_id 
  LEFT JOIN Category c3 ON v.cat_id3 = c3.cat_id
  LEFT JOIN Category c4 ON v.cat_id4 = c4.cat_id
  LEFT JOIN Category c5 ON v.cat_id5 = c5.cat_id
GROUP BY cat_title
ORDER BY cat_title;

 

I think it'll work...of course I haven't tried it : )

[NerdConcepts]

ERROR:

 

Query: SELECT cat_title, count(*) FROM vendors v LEFT JOIN vendor_category c1 ON v.cat_id = c1.cat_id LEFT JOIN vendor_category c2 ON v.cat_id2 = c2.cat_id LEFT JOIN vendor_category c3 ON v.cat_id3 = c3.cat_id LEFT JOIN vendor_category c4 ON v.cat_id4 = c4.cat_id LEFT JOIN vendor_category c5 ON v.cat_id5 = c5.cat_id GROUP BY cat_title ORDER BY cat_title;

MySQL Error: Column 'cat_title' in field list is ambiguous

 

I've tried quite a few things and nothing will seem to work. Also, why would I count it? I'm going to go ahead and use those unique cat_id's to then display the cat_title that goes along with it.

[teng84]

post your query even if you know its wrong we might get better i dea on that

 

FYI ambiguous means the field is also a field in other table so you have to use

tablename.field something like that

[NerdConcepts]

This is my query to find DISTINCT numbers in each of the cat_id fields...all 5 of them. This is also the code I'm using to check to see if it was doing what I thought it would.

 

$query = "SELECT DISTINCT cat_id,cat_id2,cat_id3,cat_id4,cat_id5 FROM vendors";
$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());	

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $row['cat_id'] . ' - ';
echo $row['cat_id2'] . ' - ';
echo $row['cat_id3'] . ' - ';
echo $row['cat_id4'] . ' - ';
echo $row['cat_id5'] . '<br />';
}

 

Results are what I thought they would be for a test.

83 - 0 - 0 - 0 - 0
134 - 135 - 0 - 0 - 0
136 - 137 - 0 - 0 - 0
11 - 62 - 0 - 0 - 0
17 - 11 - 0 - 0 - 0

displays 5 rows because there are 5 vendors in the database at this time.

 

But what I want is just for each unique cat_id for it to display its title, which is stored in the "category" table. And as you see "11" is shown twice, it was a test to see if that would happen, which it does like I thought it would. Just don't know how to check each coloumn against the others. To get unique numbers across all 5 cat_id (cat_id2....) fields.

[teng84]

ok ill do my own query and just fallow i dont understand the tbl you have

 

select * from tablename where catid in(select something here from category limit 5)

 

using this will require you to loop the query

 

 

[NerdConcepts]

Ok let me try to make this make more since.

 

Table: vendors

Fields: id,name,cat_id,cat_id2,cat_id3,cat_id4,cat_id5

 

Table: categories

Fields: cat_id,cat_title

 

Query needs to scan in "vendors" fields: cat_id,cat_id2,cat_id3,cat_id4,cat_id5 for unique numbers. Thats is really all it needs to do. Or PHP needs to do it. I have no clue how to do this. I know how to do it with just one field, that is simple. Just when it is comparing 5 different fields for unique numbers I have no idea.

 

Like it is finding "11" in both cat_id and cat_id2 it should only see "11" once. That away when I use the "while" stuff to display each category title/link information it only shows in the web browser once.

 

Does this make more since?

[NerdConcepts]

Got something that is extremely close to be doing what I need it to do. Figure this out....

 

$query = "SELECT DISTINCT cats FROM  (
SELECT cat_id AS cats FROM vendors
UNION
SELECT cat_id2 AS cats FROM vendors
UNION
SELECT cat_id3 AS cats FROM vendors
UNION
SELECT cat_id4 AS cats FROM vendors
UNION
SELECT cat_id5 AS cats FROM vendors ) as cat_table";
$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());	

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$qTitle = "SELECT * FROM vendor_category WHERE cat_id='{$row['cats']}'";
$rTitle = mysql_query($qTitle) or trigger_error("Query: $qTitle\n<br />MySQL Error: " . mysql_error());	
$rowTitle = mysql_fetch_array($rTitle, MYSQL_ASSOC);

if ($row['cats'] != '0') {
	echo $row['cats'] . ' - ' . $rowTitle['cat_title'] . '<br />';
}
}

 

This does almost exactly what I want it to do. It looks at all 5 fields and grabs distinct numbers, then takes those numbers and finds the appropriate cat_title. One problem...no way to alphabetize them, lol. I want a ORDER BY cat_title DESC but well cannot do that since I'm not finding the vendor_category.vendor_title in the query...any ideas?

[Barand]

This would be easy if I wasn't allowing vendors to be listed in up to 5 categories.

 

It isn't the fact that vendors can be in multiple categories that's making it difficult, it's your failure to normalise your data that is causing the difficulty.

 

My solution would be to create the "vendor_category" table (that you should have already) as a new table and then join that to category to get the titles with a simple "SELECT DISTINCT ..." query. You can then drop the multiple cat columns from the vendor table.

 

<?php
     $sql = "CREATE TABLE vendor_category            
            SELECT vendor_id, cat_id AS cat_id FROM vendors WHERE cat_id IS NOT NULL
            UNION
            SELECT vendor_id, cat_id2 AS cat_id FROM vendors WHERE cat_id2 IS NOT NULL
            UNION
            SELECT vendor_id, cat_id3 AS cat_id FROM vendors WHERE cat_id3 IS NOT NULL
            UNION
            SELECT vendor_id, cat_id4 AS cat_id FROM vendors WHERE cat_id4 IS NOT NULL
            UNION
            SELECT vendor_id, cat_id5 AS cat_id FROM vendors WHERE cat_id5 IS NOT NULL";
     mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
     
     $sql = "SELECT DISTINCT c.cat_title
            FROM vendor_category v
            INNER JOIN category c ON v.cat_id = c.cat_id
            ORDER BY c.cat_title";
     $res = mysql_query($sql) or die (mysql_error()."<pre>$sql</pre>");
     while ($row = mysql_fetch_row($res)) {
        echo $row[0], '<br/>';
     }
?>

[NerdConcepts]

Ok all that works perfectly. And I understand what you mean, I'm going to add a bit a coding to make sure when I add vendors it also inserts it's cat_id, cat_id2... records into the new vendor_category table also. Thanks a lot.