current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Context: I'm querying a database to return a specified number of monsters to be used in a fight within a simple web based RPG.

Problem: I have a straightforward MYSQL query that includes a WHERE clause to determine the "level" of the monsters. In a previous function I'm building an array that includes the levels of the corresponding monsters that should be returned; something like:

$levels = Array( [0] => 3 [1] => 1 [2] => 1 )

So in the above array, the Value corresponds to the level. So I'm looking to return 3 monsters -- Level 3, Level 1 and Level 1.

Simple enough. Now I want to query MYSQL with the following and presumably use a loop to do the query 3 times to get my 3 monsters; something like:

$query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP";
$query .= " FROM Cards";
$query .= " WHERE CardTypeId = 1 AND CardRarity = {$level}";
$query .= " ORDER BY RAND() LIMIT 1";

So in the above query, the variable {$level} is the Monster Level from the previous array. The loop formatting is where I'm struggling. I'm trying something like:

while ($i = $levels) {
     $query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP";
     $query .= " FROM Cards";
     $query .= " WHERE CardTypeId = 1 AND CardRarity = {$i}";
     $query .= " ORDER BY RAND() LIMIT 1";
 $result_set = mysql_query($query, $connection);
 confirm_query($result_set);
 return $result_set;    
}

I think this is close, but I'm not seeing how I can get this to ultimately give me a single array of monsters from MYSQL. It seems in the above, I'm building 3 separate arrays? Do I need to use array_merge(), or is there a simpler more elegant way to do this? If I were getting all the same level monsters I could simply just run the query 1 time and LIMIT 3, but I need monsters to correspond to what the previous array shows.

Making sense? Help is always appreciated.

share|improve this question
1  
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial –  vanneto Jan 14 '13 at 7:05
 
ok, thanks. This project has been around so I've been dragging to upgrade it. But I will do the work to learn the change. Thanks. –  Jeff Solomon Jan 14 '13 at 7:14
add comment

3 Answers

up vote 2 down vote

I guess WHERE IN is what you need.

$query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP";
$query .= " FROM Cards";
$query .= " WHERE CardTypeId = 1 AND CardRarity IN (".implode(',', $levels).")";
$query .= " ORDER BY RAND() LIMIT 1";
share|improve this answer
add comment
up vote 1 down vote

I don't fully understand what you're trying to do, but this should loop and get you your array:

foreach ($levels as $key => $value) {
    $query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP FROM Cards WHERE WHERE CardTypeId = 1 AND CardRarity = {$value} ORDER BY RAND() LIMIT 1";
    $results = mysqli_query($connection, $query);
    while($row = $result->fetch_array(MYSQLI_BOTH)) {
        $monsters[$key]['HP'] = $row['CardHP'];
        //Add as needed
    }
}

It creates a multidimensional array where the first key is the same as the one from levels and subsequent keys are the results of the SQL query.

$monsters = array(
    [0] => array(
        ['HP'] => 10
        ['AP'] => 5
    )
    [1] => array(
        ['HP'] => 5
        ['AP'] => 7
    )
)

Also, mysqli is probably better for you to use since it supports procedural style as well as object oriented and most of the functions are exactly the same, albeit with a mysqli prefix instead.

share|improve this answer
add comment
up vote 0 down vote accepted

It was actually much easier than I thought; maybe I over-explained the issue. Here is what I did.

1) Once I returned my array $levels which looked like this:

$levels = Array( [0] => 3 [1] => 1 [2] => 2 )

2) I created an empty array called $result_set 2) Then looped through the $levels array with a foreach loop and ran the query with the first $level in the $levels array. However, rather than just returning the $result_set with the first row returned, I inserted the first row into the empty array I created in step 2. After each loop through, I inserted the new row from MYSQL into the now filled $result_set array.

Here is what it looked like in the end:

$result_set = array();
foreach ($levels as $level) {
    $query = "SELECT *, CardHP as EncounterCardHP, CardAP as EncounterCardAP";
    $query .= " FROM Cards";
    $query .= " WHERE CardTypeId = 1 AND CardRarity = {$level}";
    $query .= " ORDER BY RAND() LIMIT 1";
    $result = mysqli_query($query, $connection);
    $result_set[] = mysqli_fetch_array($result);    
    confirm_query($result_set);
}
return $result_set;

My $result_set now has 3 monsters in it, a level 3, level 1 and level 2, all chosen randomly from the database for each respective level.

Thanks for all the help.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.