Sql pieprasījums uz php

[raimixs_0]

Sveiki, ir neliela problēma ar kodu. Īsti nevaru iebraukt kur ir tā problēma. Varbūt kādam ir idejas kā to savādāk uzrakstīt.

Jo mana ideja ir uztaisīt lai izskatās šādi: 

Datubāzes dizains:
CREATE TABLE table_name(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    Points INT NOT NULL,
    Points2 INT NOT NULL
);

INSERT INTO table_name (Name, Points, Points2)
VALUES 
    ('Bob Johnson', 75, 68),
    ('John Doe', 80, 72),
    ('Emily Davis', 85, 67),
    ('Jane Smith', 90, 82);
 

PHP kods:

<?php
// Connect to the database
$conn = mysqli_connect("hostname", "username", "password", "database_name");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Select data from the table, add consecutive number and sort by Total Points in ascending order
$sql = "SET @count = 0;
        SELECT  @count := @count + (TotalPoints = @prev) as Place,
                @prev := TotalPoints as prev,
                Name, Points, Points2, Points + Points2 AS TotalPoints 
        FROM table_name
        ORDER BY TotalPoints, Name ASC;";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // Output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "Place: " . $row["Place"]. " - Name: " . $row["Name"]. " - Points: " . $row["Points"]. " - Points2: " . $row["Points2"]. " - TotalPoints: " . $row["TotalPoints"]. "<br>";
    }
} else {
    echo "0 results";
}

// Close the connection
mysqli_close($conn);
?>

[werd]

Ņemot vērā, ka tiek izmantots PHP, lai apstrādātu rezultātus:

1. Atlasi datus no datu bāzes kārtojot tos pēc kopējā punktu skaita un vārda (ja punkti sakrīt)
    

   SELECT `Name`, Points, Points2, (Points + Points2) AS TotalPoints 
   FROM table_name 
   ORDER BY TotalPoints ASC, `Name` DESC

    

2. Neredzu vajadzību rēķināt iegūtu vietu ar SQL vaicājumu - tā vietā iterē cauri rezultātiem secīgi un iegūtu vietu rēķini PHP pusē
    

   $place = 1;
   while ($row = mysqli_fetch_assoc($result)) {
       echo sprintf(
           'Place: %d - Name: %s - Points: %d - Points2: %d - TotalPoints: %d',
           $place++,
           $row['Name'],
           $row['Points'],
           $row['Points2'],
           $row['TotalPoints'],
       );
       echo PHP_EOL;
   }

Zemāk piemēri:

[raimixs_0]

Paldies, bet problēma ir tāda, ka man ir tomēr vajadzīgs,ka ja ir cilvēkiem vienāds punktu skaits. Tad viņiem Place ir vienāds bet nākamajam ir ir nevis nākamā vieta bet gan vēl +1. Manā bildē ko biju ielicis tā būtu jāizskatās. Patreiz viņš vienkārši sarindo pa vietām nepārbudot vai ir vienādi.

[werd]

Oi :) Nepamanīju niansi par vienādu vietu un vietas izlaišanu, ja vienādi punkti.

Kaut kas šāds (plain & stupid) risinās problēmu PHP pusē:

<?php

$array = [
    [
        'Name'        => 'Bob Johnson',
        'Points'      => 75,
        'Points2'     => 68,
        'TotalPoints' => 143,
    ],
    [
        'Name'        => 'John Doe',
        'Points'      => 80,
        'Points2'     => 72,
        'TotalPoints' => 152,
    ],
    [
        'Name'        => 'Emily Davis',
        'Points'      => 85,
        'Points2'     => 67,
        'TotalPoints' => 152,
    ],
    [
        'Name'        => 'Jane Smith',
        'Points'      => 90,
        'Points2'     => 82,
        'TotalPoints' => 172,
    ],
];

$place               = 1;
$previousTotalPoints = null;
$increment           = 1;
foreach ($array as $row) {
    $totalPoints = $row['TotalPoints'];

    if ($previousTotalPoints) {
        if ($totalPoints == $previousTotalPoints) {
            $increment++; // only increment - place is the same as before
        } else {
            $place += $increment; // place equals previous place + the increment (how many had the same points)

            $increment = 1; // reset the increment (normally each next place is +1)
        }
    }

    echo sprintf(
        'Place: %d - Name: %s - Points: %d - Points2: %d - TotalPoints: %d',
        $place,
        $row['Name'],
        $row['Points'],
        $row['Points2'],
        $row['TotalPoints'],
    );
    echo PHP_EOL;

    $previousTotalPoints = $totalPoints;
}

 

[Nils Graustiņš]

Vienkāršāk,
$totalPoints = null;

$place = 0;
while{
$place  += $totalPoints == $row['TotalPoints'] ? 1:0;...

$totalPoints = $row['TotalPoints'];
}