CODEWITHSUNDEEP
phpmysql
oOo--STAR--oOo
oOo--STAR--oOo

Reputation: 438

How to check if multiple values exists with one query

Basically I am building an auto Awards system for users of my forum. What it will do is check if they meet the criteria for the award IE Posts, Karma etc.... Allow the member to self issue if they meet this criteria..

One problem I am facing is I would like to query the MySQL database once and have multiple checks on each award to see if they already have it.. If not allow them to self issue the award by clicking a button.

Here is what I have so far

//Query Member Awards DB to check if we already have this Award
    $query2 = $smcFunc['db_query']('', '
        SELECT id_award, id_member
        FROM {db_prefix}awards_members
        WHERE id_member = {int:id_prof}',
            array(
                'id_prof' => $user_info['id'],
            )
    );
    $info2 = $smcFunc['db_fetch_assoc']($query2);

    echo $info2['id_award'];

    if ($info2['id_award'] == '27')
        echo 'exists';

My table looks like this

uniq_id  id_award id_member date_received favorite
    44      29        1       2011-11-02    1
    31      21        1       2011-11-02    0
    41      32        1       2011-11-02    0
    43      27        1       2011-11-02    0
    34      18        1       2011-11-02    0
    35      38        1       2011-11-02    1
    39      31        1       2011-11-02    0
    40      30        1       2011-11-02    1

Now basically underneath I will have a bunch of Awards all with different ID's I want to be-able to say if exists do not display a button.. Now it would be fine if there was only one award to check but I need to check for a few awards.

I don't want to be making multiple queries using the where clause to see if this member already has this award.

Is there a simpler way of doing this so I only need one query and can check for multiple awards..

if ($info2['id_award'] == '1')
            echo 'exists';

if ($info2['id_award'] == '2')
            echo 'exists';

And so on

I am pretty puzzled at the best way todo this. Any help would be amazing.

EDIT:

Yeah basically its gonna look like this.. I will be using ajax to submit the data..

if ($info2['id_award'] != '27') && ($info['karma_good'] >= '100') {
        echo '
        <form id="karma_god" method="post">
            <input type="hidden" id="recipient_to" name="recipient_to" value="' . $info['id_member'] . '" size="32" />
            <input type="hidden" id="award" name="award" value="28" size="32" />
            <input type="hidden" id="year" name="year" value="' . date('Y') . '" size="32" />
            <input type="hidden" id="month" name="month" value="' . date('n') . '" size="32" />
            <input type="hidden" id="day" name="day" value="' . date('d') . '" size="32" />
            <span>You have above 100 Karma! A New Award Awaits. </span><button class="button"> <img src="http://www.uniquez-home.com/awards/28.gif" alt="" style="vertical-align:bottom;"> Karma God </button>
        </form>';

And obviously for a few more awards 2 underneath this one..

Upvotes: 1

Views: 6654

Answers (3)

denil
denil

Reputation: 690

You can do this:

Select * FROM awards_member WHERE id_award in (1,2,3,4,5) AND id_member = 5

Just add additional award id that you needed to check

Based on the result from the query you can loop through each of the result and check which award id is not yet given and display the button if needed.

Upvotes: 5

Shef
Shef

Reputation: 45589

Fetch the whole range of awards awarded to that user, and add them to an array.

// generate the array
// obviously you can use your function here to fetch results and iterate over it
// it just matters that you generate an array of awarded awards
$awarded = array();
while($row = mysql_fetch_assoc($result)){
    $awarded[] = $row['id_award'];
}

Then, it's easy to check on your application logic:

// check for award id 2
if(in_array(2, $awarded)){
    // echo award 2 is received
}

// check for award id 3
if(in_array(3, $awarded)){
    // echo award 3 is received
}

Upvotes: 2

Marco
Marco

Reputation: 57573

You could try

SELECT aw.id, COALESCE(am.id_member, 0)
FROM awards aw LEFT JOIN award_members am
ON aw.id = am.id_award
WHERE am.id_member = your_member

With this query you have all awards-id and, if single award has been given to a member you have member-id, else you get a zero.

Upvotes: 1

Related Questions