1

I'm converting my reservation website for lab-equipment from procedural to OOP. After login all equipment a user has rights to will be fetched from a rights-table. Based on these rights the category ID's they are in are fetched from the equipment table and then these categories are shown as a pull-down list, so the user first picks from what category of equipment it wants to book a timeslot. In my procedural website this works fine, but I can't figure out how to do this using OOP. Fetching the list of equipment the user has rights to is no problem. After that I take the equipment-ID column and use that to run a query to get the unique category-ID's, which can be anything from just 1 value and up, depending on the ammount of machines a user is allowed to book.

if (!empty($getMachineRights)) {
$equipment = new equipment();
$Equipment = array_column($getMachineRights, 'EquipmentID');
$getEquipmentGroup = $equipment->getEquipmentGroups($Equipment);
}

The getEquipmentGroups function is as follows:

 public function getEquipmentGroups($params = []) {
    try {
        $db = database::getInstance();
        $i = 0;
        $query = "SELECT DISTINCT (EquipmentgroupID) FROM equipment WHERE id IN (";
        foreach ($params as $key => $value) {
            $query .= ":key, ";
            
            $i++;
        }
        $query = substr($query, 0, -2) . ")";
        $count = count($params);
        $stmt = $db->datab->prepare($query);
        for ($i = 0; $i < $count; $i++) {
               $stmt->bindParam("id", $params[$i], PDO::PARAM_STR);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    } catch (PDOException $e) {
        throw new Exception($e->getMessage());
    }
}

The result would be something like: |EquimentgroupID| |:-------------:| |1 | |3 | |4 |

These group ID's I would subsequently put in a similar query to get group names from the group-table (there probably is a more efficient way to that as well, but I don't know if JOIN's work in OOP), so the user sees a pull-down list with names to slect from, rather then group ID's.

But I can't get that to work. I've tried all kinds of things, but keep getting errors. Most commonly SQLSTATE[HY093]: Invalid parameter number: parameter was not defined, or number of tokens does not match number of bound parameters, but with different things I try I just can't get it to work. So my basic question is: How to get a WHERE IN clause in my query to work with an unknown number of values in OOP

Kevin van der Ven
  • 19
  • 2
  • if the getEquipmentGroup value are obtained from a db table you could sue a single query without the IN clause .. if possibile updated your question add a proper data sample as tabular text and the expected result + – ScaisEdge Feb 20 '21 at 09:33
  • In the meanwhule I kind of solved my problem using a different post on stackoverflow (https://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array), but somehow I think I should use bindParam/Value for safety reasons. – Kevin van der Ven Feb 20 '21 at 10:10
  • if your getEquipmentGroup are already in db you don't need IN clause and don't need the related binding .. – ScaisEdge Feb 20 '21 at 10:54
  • Nope you don't have to use bindParam/Value. What you have to use is placeholders instead of actual data in the query, which you do – Your Common Sense Feb 20 '21 at 10:55
  • Thanks, my problem is solved now, after hours of searching and trying by myself and within 10 minutes after posting my question here. Should have done that way sooner. – Kevin van der Ven Feb 20 '21 at 14:58

0 Answers0