How to check if multiple values exists with one query

Question

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

denil · Accepted Answer · 2011-11-03 07:47:32Z

5

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.

edited Nov 3, 2011 at 7:47

answered Nov 3, 2011 at 7:40

denil

6905 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shef Over a year ago

What will this prove to the app logic, that is if it should display a button or not?

denil Over a year ago

he can just loop into the result and display which button that wasn't in the list (with respect to the award id)

Shef Over a year ago

I don't think it's going to do that good by adding the IN clause there with all those awards.

denil Over a year ago

@oOo--STAR--oOo stated he needed a query to check every awards if the user already received it. the IN clause will be the best way to do

oOo--STAR--oOo Over a year ago

@shef O.k. so dani says this is the best possible way todo this. After I have done the query how would I check if exists? I have edited my original post to show exactly what I am doing.

Shef · Accepted Answer · 2011-11-03 07:40:17Z

2

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
}

answered Nov 3, 2011 at 7:40

Shef

45.6k15 gold badges82 silver badges91 bronze badges

4 Comments

oOo--STAR--oOo Over a year ago

This seems like the best solution also the fastest. Can you give me an example of how you would put them all in an array?

Shef Over a year ago

@oOo--STAR--oOo I already gave you one. If you have some custom function/wrapper for running mysql queries, you should share that so I can adjust the code according to that wrapper.

oOo--STAR--oOo Over a year ago

This would work.. Thank you very much.. I am going to be testing this method and the in clause method.. I learnt something new today XD

oOo--STAR--oOo Over a year ago

Tested and worked perfectly XD Thanks for your answer. Appreciated!

Marco · Accepted Answer · 2011-11-03 07:40:08Z

1

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.

answered Nov 3, 2011 at 7:40

Marco

57.7k15 gold badges135 silver badges159 bronze badges

4 Comments

oOo--STAR--oOo Over a year ago

There is only one table to query why would I need to left join? I don't understand this answer sorry.

Marco Over a year ago

@oOo--STAR--oOo: so you don't have a table with all awards a member can win?

oOo--STAR--oOo Over a year ago

No sorry.. I only need one table. Because the member ID is already determined so I would be doing a where clause on ID_MEMBER and ID_AWARD I don't need to join any tables.. Thank you for your input.

Marco Over a year ago

@oOo--STAR--oOo: so your awards are "fixed" and you need to have a static list to check all of them... it's not a good idea...

Collectives™ on Stack Overflow

How to check if multiple values exists with one query

3 Answers 3

5 Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related