CODEWITHSUNDEEP
phpmysql
MiChAeLoKGB
MiChAeLoKGB

Reputation: 805

SQL select categories where fields are set to show

I am trying to select categories (only categories), where fiels (which are in separate table) have their show value set to 1.

I managed to made it with INNER JOIN, but problem is, this will get everything (even the fields themselves), so when I try to use while and print categories, it prints one category as many times, as many fields is there.

$get_cats = dbquery("SELECT A.field_cat_name, A.field_cat_id FROM ".DB_USER_FIELD_CATS." A INNER JOIN ".DB_USER_FIELDS." B ON A.field_cat_id=B.field_cat WHERE B.field_show!='0' ORDER BY field_cat_order");

while ($cat = dbarray($get_cats)){
    echo "<div class='tester' id='cat-".$cat['field_cat_id']."'><span>".$cat['field_cat_name']."</span></div>";
}

Returns

CONTACT INFORMATION
CONTACT INFORMATION
CONTACT INFORMATION
CONTACT INFORMATION
CONTACT INFORMATION
CONTACT INFORMATION
CONTACT INFORMATION
MISCELLANEOUS INFORMATION
MISCELLANEOUS INFORMATION
STATISTICS
STATISTICS
STATISTICS

Some ideas? I tried googling, but have no idea what to tell google to search, so it would show me relevant results.

TL;DR

I need to get all categories which have any fields that have show value set to 1. Categories and fields are in separate tables tho.

Thanks.

Upvotes: 1

Views: 36

Answers (3)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can use DISTINCT like this:

$get_cats = dbquery("SELECT DISTINCT A.field_cat_name, A.field_cat_id 
                     FROM ".DB_USER_FIELD_CATS." A 
                       INNER JOIN ".DB_USER_FIELDS." B 
                     ON A.field_cat_id=B.field_cat 
                     WHERE B.field_show!='0' 
                     ORDER BY field_cat_order");

Upvotes: 1

colintobing
colintobing

Reputation: 167

Try to use distinct.

SELECT DISTINCT
A.field_cat_name,
A.field_cat_id
FROM ".DB_USER_FIELD_CATS." A
INNER JOIN ".DB_USER_FIELDS." B
ON A.field_cat_id=B.field_cat
WHERE B.field_show!='0'
ORDER BY field_cat_order

Upvotes: 1

Lynne Davidson
Lynne Davidson

Reputation: 183

Use $get_cats = dbquery("SELECT DISTINCT A.field_cat_name,....

Upvotes: 1

Related Questions