PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE users (`user_name` varchar(25), `age` smallint, `previous_level` varchar(20), `current_level` varchar(20)) ; INSERT INTO users (user_name, age, previous_level, current_level) VALUES ('User1', 25, 'N/A', 'lvl-Blue'), ('User2', 30, 'lvl-Blue', 'lvl-Bronze'), ('User3', 28, 'lvl-Bronze', 'lvl-Silver'), ('User4', 35, 'lvl-Silver', 'lvl-Gold'), ('User5', 22, 'lvl-Gold', 'lvl-Diamond'), ('User6', 27, 'N/A', 'lvl-Blue'), ('User7', 33, 'lvl-Blue', 'lvl-Bronze'), ('User8', 29, 'lvl-Bronze', 'lvl-Silver'), ('User9', 26, 'lvl-Silver', 'lvl-Gold'), ('User10', 31, 'lvl-Gold', 'lvl-Diamond'), ('User11', 24, 'N/A', 'lvl-Blue'), ('User12', 32, 'lvl-Blue', 'lvl-Bronze'), ('User13', 34, 'lvl-Bronze', 'lvl-Silver'), ('User14', 23, 'lvl-Silver', 'lvl-Gold'), ('User15', 36, 'lvl-Gold', 'lvl-Diamond'), ('User16', 29, 'N/A', 'lvl-Blue'), ('User17', 27, 'lvl-Blue', 'lvl-Bronze'), ('User18', 30, 'lvl-Bronze', 'lvl-Silver'), ('User19', 26, 'lvl-Silver', 'lvl-Gold'), ('User20', 25, 'lvl-Gold', 'lvl-Diamond'); Select * from users;
Copy Clear
Copy Format Clear
<?php $user_name = 'Floris'; $user_pass = 'abcd'; $curLevel = 'lvl-Gold'; $levels = ['N/A', 'lvl-Blue', 'lvl-Bronze', 'lvl-Silver', 'lvl-Gold', 'lvl-Plat', 'lvl-Diamond', 'lvl-Privee']; // Find the index of an 'x' level $targetIndex = array_search($curLevel, $levels); // Select the substring up to 'lvl-Diamond' index $previousLevelsArray = array_slice($levels, 0, $targetIndex); // Database connection using PDO try { // Set PDO to throw exceptions on error $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { die("Connection failed: " . $e->getMessage()); } // Generate named placeholders for the IN clause $placeholders = array_map(function($index) { return ":prev_level_$index"; }, array_keys($previousLevelsArray)); // Prepare the SQL query with the dynamically generated named placeholders $query = "SELECT * FROM users WHERE current_level = :cur_level AND previous_level in (".implode(', ', $placeholders).")"; // Prepare the query $stmt = $pdo->prepare($query); // Bind parameters $stmt->bindValue(':cur_level', $curLevel); foreach ($previousLevelsArray as $key => &$value) { $stmt->bindValue(":prev_level_$key", $value); } // Execute the query $stmt->execute(); // Fetch the result set $result = $stmt->fetchAll(PDO::FETCH_ASSOC); // Output each row foreach ($result as $row) { print_r($row); }
Show:  
Copy Clear