PHP runQuery函数代码示例

function getUserRanking($idSelected)
{
    $users = getUsers();
    $ranking = initialRanking();
    $result = runQuery('SELECT userId, matchId, coteResult, coteScore, round,
      bet.scoreDomicile AS betScoreDomicile, 
      bet.scoreExterieur AS betScoreExterieur, 
      result.scoreDomicile AS resultScoreDomicile,
      result.scoreExterieur AS resultScoreExterieur
      FROM bet 
      INNER JOIN result WHERE id IN (' . implode(',', $idSelected) . ') AND bet.matchId = result.id');
    foreach ($result as $row) {
        //Variable sql
        $userId = intval($row['userId']);
        $coteResult = floatval($row['coteResult']);
        $coteScore = floatval($row['coteScore']);
        $round = intval($row['round']);
        $betScoreDomicile = intval($row['betScoreDomicile']);
        $betScoreExterieur = intval($row['betScoreExterieur']);
        $resultScoreDomicile = intval($row['resultScoreDomicile']);
        $resultScoreExterieur = intval($row['resultScoreExterieur']);
        if ($resultScoreDomicile >= 0) {
            //Variable de calcul
            $scorePoint = 0;
            if ($betScoreDomicile == $resultScoreDomicile && $betScoreExterieur == $resultScoreExterieur) {
                $scorePoint = $coteScore;
            }
            $betPoint = 0;
            if ($betScoreDomicile > $betScoreExterieur && $resultScoreDomicile > $resultScoreExterieur || $betScoreDomicile == $betScoreExterieur && $resultScoreDomicile == $resultScoreExterieur || $betScoreDomicile < $betScoreExterieur && $resultScoreDomicile < $resultScoreExterieur) {
                $betPoint = $coteResult;
            }
            $ranking[$userId]['betCount']++;
            $ranking[$userId]['betWon'] += $betPoint > 0 ? 1 : 0;
            $ranking[$userId]['betPoint'] += $betPoint;
            $ranking[$userId]['scoreWon'] += $scorePoint > 0 ? 1 : 0;
            $ranking[$userId]['scorePoint'] += $scorePoint;
            $ranking[$userId]['globalPoint'] += $betPoint + $scorePoint;
        }
    }
    $sortedranking = array_values($ranking);
    usort($sortedranking, 'compareUserPoint');
    $currentRanking = 0;
    $currentScore = 0;
    $index = 0;
    foreach ($sortedranking as $row) {
        $index++;
        if ($currentRanking == 0) {
            $currentRanking = 1;
            $currentScore = $row['globalPoint'];
        }
        if ($currentScore != $row['globalPoint']) {
            $currentRanking = $index;
            $currentScore = $row['globalPoint'];
        }
        $ranking[$row['userId']]['rank'] = $currentRanking;
    }
    $ranking = array_values($ranking);
    usort($ranking, 'compareUserRanking');
    return $userRanking = array('ranking' => $ranking);
}

 public function GetNextID($serviceName)
 {
     $returnArray = array();
     $responseArray = array();
     $nextID = 0;
     $selectQuery = "select id from idinfo where servicename = :servicename;";
     $parameters = array(':servicename' => trim($serviceName));
     $stmt = runQuery(DB_SERVER, DB_PORT, DB_USERNAME, DB_USERPASSWORD, DB_NAME, $selectQuery, $parameters);
     if ($stmt == QUERY_FAILED) {
         return FAILED;
     }
     $row = fetchNextRow($stmt['statement']);
     if ($row != null) {
         try {
             $nextID = getValue($row, "id");
             $nextID = $nextID + 1;
         } catch (Exception $e) {
             error_log('Database Error: ' . $e->getMessage());
             return FAILED;
         }
     }
     $updateQuery = "UPDATE idinfo SET id = :id  WHERE servicename = :servicename;";
     $parameters = array(':id' => $nextID, ':servicename' => trim($serviceName));
     $stmt = runQuery(DB_SERVER, DB_PORT, DB_USERNAME, DB_USERPASSWORD, DB_NAME, $updateQuery, $parameters);
     if ($stmt == QUERY_FAILED) {
         error_log('Database Error: ' . $e->getMessage());
         return FAILED;
     }
     return $nextID;
 }

function updateBlog($p)
{
    $q = 'UPDATE Blogs SET title=?, content=?, quote=?';
    $b = array($p['title'], $p['content'], $p['quote']);
    $results = runQuery($q, $b);
    return $results;
}

function updateUIDsForPrevSemester($db)
{
    $datesArr = getPrevSemesterDates();
    $startDate = $datesArr["start"];
    $endDate = $datesArr["end"];
    try {
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db->beginTransaction();
        $updateBookingIdsQuery = "UPDATE Bookings JOIN BookingSlots ON Bookings.bookingID = BookingSlots.bookingID\n\t\t\t\t\t\t\t\tSET uID = CASE hrsSource\n\t\t\t\t\t\t\t\t\tWHEN 'Admin' THEN 'Admin'\n\t\t\t\t\t\t\t\t\tWHEN 'Faculty' THEN 'Faculty'\n\t\t\t\t\t\t\t\t\tWHEN 'Weekly' THEN 'Student'\n\t\t\t\t\t\t\t\t\tWHEN 'Special' THEN 'Student'\n\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\tWHERE bookingDate >= '{$startDate}' AND bookingDate <= '{$endDate}'";
        runQuery($db, $updateBookingIdsQuery, []);
        $checkQuery = "SELECT BookingSlots.bookingID, bookingDate, uID, hrsSource \n\t\t\t\tFROM Bookings JOIN BookingSlots ON Bookings.bookingID = BookingSlots.bookingID \n\t\t\t\tORDER BY BookingSlots.bookingID";
        $checkQueryStmt = runQuery($db, $checkQuery, []);
        $outputArray = $checkQueryStmt->fetchAll(PDO::FETCH_ASSOC);
        if (sizeof($outputArray) > 0) {
            // DO NOTHING. Uncomment for debugging
            // foreach ($outputArray as $rows){
            // 	print_r($rows);
            // 	echo "<br>";
            // }
        } else {
            echo "<br>There are no bookings or booking slots in the database.<br>";
        }
        $db->commit();
    } catch (Exception $e) {
        http_response_code(500);
        //Internal Server Error
        if (isset($db)) {
            $db->rollback();
            echo "Error:  " . $e;
        }
    }
}

function findUserByUsernameOrEmail($usernameOrEmail)
{
    $table = getTableQuote("users");
    $query = "SELECT * FROM {$table} WHERE (username='{$usernameOrEmail}' OR email='{$usernameOrEmail}') AND active=1;";
    $queryReturn = runQuery($query);
    $possibleUser = count($queryReturn) == 1 ? $queryReturn[0] : null;
    return $possibleUser;
}

function updatePrice()
{
    if (isset($_POST['priceId'])) {
        $price = ($_POST['price'] == 0 or $_POST['price'] == '') ? 'NULL' : $_POST['price'];
        $updatePriceQuery = 'UPDATE archi SET price=' . $price . ' WHERE id=' . $_POST["priceId"];
        runQuery($updatePriceQuery);
    }
}

function getGroupsWeeklyHours($db, $userID)
{
    //TODO check this is the correct date to use
    $startThirdWeek = date("Y-m-d", strtotime('monday next week next week'));
    $getWeeklyHrsQuery = "SELECT Sum(UGroups.hours) weeklyHours\n\t\t\tFROM Permission JOIN UGroups on UGroups.groupID = Permission.groupID \n\t\t\tWHERE uID = '{$userID}' and '{$startThirdWeek}' BETWEEN UGroups.startDate and UGroups.endDate";
    $getStudentStmt = runQuery($db, $getWeeklyHrsQuery, []);
    return $getStudentStmt->fetch(PDO::FETCH_ASSOC)["weeklyHours"];
}

function getFullTrait($table, $type)
{
    $query = "SELECT * FROM `" . $table . "` WHERE type='" . $type . "' ORDER BY RAND() LIMIT 1;";
    $result = runQuery($query);
    if (!empty($result[0])) {
        return $result[0];
    }
}

function returnResults($conn, $sql)
{
    $result = runQuery($conn, $sql);
    if (mysqli_num_rows($result)) {
        return mysqli_fetch_assoc($result);
    } else {
        return 0;
    }
}

function getUsers()
{
    $users = array();
    $result = runQuery('SELECT * FROM user');
    foreach ($result as $row) {
        $users[$row['id']] = $row['pseudo'];
    }
    return $users;
}

function getSingleColumnData($table, $column)
{
    $query = "SELECT " . $column . " FROM " . getTableQuote($table);
    $results = runQuery($query);
    $data = [];
    foreach ($results as $row) {
        array_push($data, $row[$column]);
    }
    return $data;
}

function countUserBet($ids)
{
    global $countBet;
    $notificationBet = $countBet;
    $result = runQuery('SELECT * FROM bet WHERE matchId IN (' . implode(',', $ids) . ') AND userId = ' . $_SESSION['id']);
    foreach ($result as $row) {
        $notificationBet = $notificationBet - 1;
    }
    echo json_encode(array('notificationBet' => $notificationBet));
}

function teamInfo()
{
    $selectName = 'SELECT * FROM teamInfo';
    $result = runQuery($selectName);
    $teamInfo = array();
    foreach ($result as $row) {
        $teamInfo[$row['name']] = array('trueName' => $row['trueName'], 'littleName' => $row['littleName'], 'countryName' => $row['countryName']);
    }
    return $teamInfo;
}

function fetchBets()
{
    $bets = array();
    $betsQuery = 'SELECT * FROM bet';
    $result = runQuery($betsQuery);
    foreach ($result as $row) {
        $bet = ['userId' => $row['userId'], 'matchId' => $row['matchId'], 'scoreDomicile' => $row['scoreDomicile'], 'scoreExterieur' => $row['scoreExterieur'], 'coteResult' => $row['coteResult'], 'coteScore' => $row['coteScore']];
        $bets[] = $bet;
    }
    return $bets;
}

function getInviteKey($email)
{
    $table = "invitations";
    $sentInviteKey = runQuery("SELECT inviteKey FROM " . getTableQuote($table) . " WHERE email='{$email}';");
    if (count($sentInviteKey) == 1) {
        $inviteKey = $sentInviteKey[0]['inviteKey'];
    } else {
        $inviteKey = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647));
    }
    return $inviteKey;
}