Return 1st Match per Company Only

dolphinsnot

evenin' all.

I am working with a simple 2-column MySQL table for a trade association. One column holds company IDs used in a seperate 'Contact Information' table, and the other holds 'Products & Services' IDs.

#######################
company_id !! product_id #
########## !! ###########

137 !! A0505

649 !! A1025

828 !! A0525

828 !! A0505

#######################

notice how 828 appears twice because it falls into both product categories, and has 'A05' in common

i'm using a system with ((master_categories,subcategories,specific_items))
thus ((A,10,25))

i want people to be able to search for specific items or entire subcategories.
all subcategories have '00' as the last 2 digits ((A1000))

I'm doing this with the following code that is passed $prod_id

<?
if(eregi('00$', $prod_id)){
$prod_class = substr($prod_id, 0, -2);
$prodquery = "SELECT FROM product_index WHERE prod_id LIKE '%$prod_class%'";
}else{
$prodquery = "SELECT FROM product_index WHERE prod_id=\"$prod_id\"";
}
$prodresult = @($prodquery, $db);
$prodnum_rows = @mysql_num_rows($prodresult);
?>

the company_id is fetched from all matches for product_id and used to mysql_fetch_row()s from another table inside a loop.

the problem i run in to is that for companies with multiple entries in a single subcategory (like 828 above), i end up with redundant company_ids and thus return duplicate contact informations to the browser.

is this something that should be handled by some sort of regular expression/arrays/loops widget, or can i simply phrase my MySQL query to return only the first result where there are identical company IDs?

thanks much for your brain juice.

-dolphinsnot

mdmann

I have this situation a lot in what I'm
working on, and I just set up a flag with
a value of either 0 or 1 that indicates if
a particular customer's information has been
fetched previously. If so, just don't
execute the subsequent searches.

I don't know that there is some slick or
elegant way of doing the same thing with
the MySQL query, but sometimes you don't
really need slick or elegant.

MIchael

dolphinsnot

hmm...a flag, eh?

how exactly do you do that? do you have an array that stores the values of returned IDs and compare against it before you execute a search?

do you construct a function called flag() or something?

it sounds like a great idea. i'll see what i can do about it...

let me know if you have any further feedback.

thank ya!

-dolphinsnot

Anon

Couln't you use a SELECT Distinct statement like:

SELECT DISTINCT(company_id) FROM product_index WHERE prod_id LIKE '%$prod_class%'

dolphinsnot

excellent!

SELECT DISTINCT(company_id) works perfectly,

thank ya so much.

-dolphinsnot