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. You should switch to MySQLi or PDO and use prepared statements. — TimWolla
– TimWolla, Commented Mar 4, 2014 at 2:31
Why SELECT * If you're just counting rows, just SELECT 1 — Akshay Kalose
– Akshay Kalose, Commented Mar 4, 2014 at 2:32
Use proper back-ticks like SELECT * FROM `player_items` WHERE `player`='$uid' AND `item`='$itemID' AND `value`>='$parseAmount[1]' — Isaiah Turner
– Isaiah Turner, Commented Mar 4, 2014 at 2:34
@Akshay2598 there are multiple rows, containing different itemID's — user2392120
– user2392120, Commented Mar 4, 2014 at 2:36

TimWolla · Accepted Answer · 2014-03-04 02:47:54Z

1

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;

edited Mar 4, 2014 at 2:47

answered Mar 4, 2014 at 2:38

TimWolla

32.9k8 gold badges67 silver badges97 bronze badges

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

2 Comments

user2392120 Over a year ago

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.

TimWolla Over a year ago

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

Collectives™ on Stack Overflow

Check if multiple rows exist in MySQL using PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related