Check if multiple rows exist in MySQL using PHP

Question

I am currently busy on a textbased RPG game, but I am stuck at one part right now.

In order to start a mission, the player does need some items, these are stored in a string: item:1x3-item:5x1 - (basicly item:IDxamount).I have already made a function that explodes the string into variables, but now the script needs to check if the player does have all the items listed.

I've tried to solve the issue with a foreach, but that returns positive or negative for every item, and I only need to know if the player has all items at once.

(don't mind the unsafe query) $parseAmount is an array, containing all item ID's. $uid is an variable containing userID

// check if player has all items
   foreach($parseAmount as $itemID)
   {
    $check_query = mysql_query("SELECT * FROM `player_items` WHERE `player`='$uid' AND `item`=='$itemID' AND `value`>='$parseAmount[1]'");
    if(mysql_num_rows($check_query)>=1)
    {return true;}
    else
    {return false;}
   }

If you want me to post the whole function, please let me know.

[The mysql extension is deprecated](http://www.php.net/manual/en/function.mysql-query.php). You should switch to [MySQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO](http://www.php.net/manual/en/ref.pdo-mysql.php) and use prepared statements. — TimWolla, Mar 04 '14 at 02:31
Why `SELECT *` If you're just counting rows, just `SELECT 1` — Akshay Kalose, Mar 04 '14 at 02:32
Use proper back-ticks like `SELECT * FROM \`player_items\` WHERE \`player\`='$uid' AND \`item\`='$itemID' AND \`value\`>='$parseAmount[1]'` — Isaiah Turner, Mar 04 '14 at 02:34
@Akshay2598 there are multiple rows, containing different itemID's — , Mar 04 '14 at 02:36
@JordiPrevost Yes, but if you are only counting rows why select all, just select 1 from each row and that will count as a row. — Akshay Kalose, Mar 04 '14 at 02:37
@Akshay2598 It does not matter, it will be optimized by MySQL anyway: http://stackoverflow.com/a/1221649/782822 — TimWolla, Mar 04 '14 at 02:39
Also for one liner returns you don't need brackets like that. `(mysql_num_rows($check_query)>=1) ? return true : return false;` will do the same thing in a shorter fashion. — Isaiah Turner, Mar 04 '14 at 02:39

TimWolla · Answer 1 · 2014-03-04T02:47:54.947

If I understood your question correctly you need something like:

foreach($parseAmount as $itemID) {
    $sql = "SELECT COUNT(*) AS count
            FROM   player_items
            WHERE      player = '".mysql_real_escape_string($uid)."'
                   AND item = '".mysql_real_escape_string($itemID)."'
                   AND value >= ".intval($parseAmount[1]);
    $row = mysql_fetch_array(mysql_query($sql));

    if ($row['count'] == 0) {
        return false;
    }
}
return true;

You must not early return true. You know the result is true only after checking all the items. My code could be improved by selecting all the items at once, but it's up to you to build this.

Keep in mind my comment about the deprecation of the MySQL extension, using MySQLi and Prepared Statements it will look something like this (note that I never worked with MySQLi before and built it with help of the manual):

foreach($parseAmount as $itemID) {
    $sql = "SELECT COUNT(*) AS count
            FROM   player_items
            WHERE      player = ?
                   AND item = ?
                   AND value >= ?"
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("ssi", $uid, $itemID, $parseAmount[1]);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_array();

    if ($row['count'] == 0) {
        return false;
    }
}
return true;

Yes, you did understood it correctly, but the main problem was that I don't know how to select, nor loop trough all the items being selected. I will take a look at MySQLi and PDO. Still looking for an answer. — , Mar 04 '14 at 02:54
@JordiPrevost I suspect the query returns at most one row. Anyway: To loop through the result set you can use something like: `while(!$row = mysql_fetch_array($result))` / `while(!$row = $result->fetch_array())`. If this does not answer it: Please expand your question. — TimWolla, Mar 04 '14 at 02:59

Check if multiple rows exist in MySQL using PHP

1 Answers1