Jump to content
php.lv forumi

Sql pieprasījums uz php


raimixs_0

Recommended Posts

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: DUXYtxb.png

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);
?>

Link to comment
Share on other sites

Ņ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:

image.png.16434268a42f7189723e79db6e8bea4b.png

image.thumb.png.bd497227fd5c4dee5cd590ba31302723.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;
}

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...