时间戳

首页 > 解决方案 > 使用 PHP 的 SQL MIN() 函数值，如何获取 MIN() 函数的值并使用它？

php - 使用 PHP 的 SQL MIN() 函数值，如何获取 MIN() 函数的值并使用它？

问题描述

因此，在以下代码中，当用户预订餐厅餐桌时应该检查可用性，我不知道如何获取结果：

$sql = "SELECT MIN(total_availability) FROM restaurant WHERE date='$date'";
$result = mysqli_query($link, $sql);
$sql_value = "";
/*
assign some value to $sql_value with the minimum value of the $sql statement
*/
if($sql_value > 0){
    /*
        insert row into restaurant table
    */
}

我该怎么办？谢谢。

可选的，不太相关，这是我收到一些评论后的原始源代码，我不使用PDO。

<?php
    include "sessione.php";
    include "connessione.php";
    $name = mysqli_real_escape_string($connection, $_POST["name"]);
    $surname = mysqli_real_escape_string($connection, $_POST["surname"]);
    $people = $_POST["pax"];
    $date = $_POST["date"];
    $time = $_POST["time"];
    $preference = $_POST["preference"];
    $email = mysqli_real_escape_string($connection, $_POST["email"]);
    
    if(preg_match("/^[a-zA-Z\s]*$/", $name) && preg_match("/^[a-zA-Z\s]*$/", $surname) && preg_match("/^[a-zA-Z0-9\.\-\_@]*$/", $email) && !empty($name) && !empty($surname) && !empty($people) && !empty($date) && !empty($time) && !empty($preference) && !empty($email)){
            $sql = "SELECT * FROM il_leone_ristorante WHERE data='$date' AND disponibilita_totale <= 1";
            $result = mysqli_query($connection, $sql);
            $count = mysqli_num_rows($result);
            if($count > 0){
                mysqli_free_result($result);
                mysqli_close($connection);
                echo "
                    <script type=\"text/javascript\">
                        window.alert('Disponibilità terminata.');
                        window.location.replace('ristorante.php');
                    </script>
                ";
            } else {
                $username = $_SESSION["username"];
                
                $sql = "SELECT MIN(disponibilita_totale) FROM il_leone_ristorante WHERE data='$date'";
                $result = mysqli_query($connection, $sql);
                $row = mysqli_fetch_all($result, MYSQLI_ASSOC);
                
                foreach ($row as $r){
                    $min_value = $r["MIN(disponibilita_totale)"];
                }
                
                $availability = $min_value;
                
                if($availability > 0){
                    $availability -= 1;
                } else {
                    $availability = NULL;
                }
                
                if($availability > 0 && $availability != NULL){
                    
                    $sql = "INSERT INTO il_leone_ristorante (nome, cognome, persone, data, ora, preferenza, utente, disponibilita_totale) VALUES ('$name', '$surname', '$people', '$date', '$time', '$preference', '$username', '$availability')";
                    mysqli_query($connection, $sql);

                    $date = explode("-", $date); // for italian date format, data means date (day) in italian
                    $data = "".$date[2]."/".$date[1]."/".$date[0]."";
                    
                    $subject = "Tavolo prenotato - conferma digitale";
                    
                    $to = "$email";
                    $message = "
                    <html>
                        <head>
                            <title>Conferma</title>
                        </head>
                        <body>
                            <h1 style=\"color: blue; font-weight:bold;\">Conferma prenotazione del tavolo</h1><br />
                            <p>Nome: $name</p>
                            <p>Cognome: $surname</p>
                            <p>Persone: $people</p>
                            <p>Data: $data</p>
                            <p>Ora: $time</p>
                            <p>E-mail: $email</p>
                        </body>
                    </html>
                    ";
                    
                    $headers = "MIME-Version: 1.0" . "\r\n";
                    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                    
                    mail($to, $subject, $message, $headers);
                    
                    mysqli_free_result($result);
                    mysqli_close($connection);
                    
                    echo "
                        <script type=\"text/javascript\">
                            window.alert('Tavolo prenotato 1, controlla la tua mail per la conferma digitale.');
                            window.location.replace('index.php');
                        </script>
                    ";
                } else if($availability == NULL){
                    $sql = "SELECT * FROM il_leone_tavoli WHERE tipologia = 'disponibilita_totale'";
                    $result = mysqli_query($connection, $sql);
                    $row = mysqli_fetch_assoc($result);
                    $tables = $row["quantita"];
                    $tables -= 1;
                    $availability = $tables;
                    
                    $sql_ = "INSERT INTO il_leone_ristorante (nome, cognome, persone, data, ora, preferenza, utente, disponibilita_totale) VALUES ('$name', '$surname', '$people', '$date', '$time', '$preference', '$username', '$availability')";
                    mysqli_query($connection, $sql_);
                    
                    $date = explode("-", $date); // for italian date format, data means date (day) in italian
                    $data = "".$date[2]."/".$date[1]."/".$date[0]."";
                    
                    $subject = "Tavolo prenotato - conferma digitale";
                    
                    $to = "$email";
                    $message = "
                    <html>
                        <head>
                            <title>Conferma</title>
                        </head>
                        <body>
                            <h1 style=\"color: blue; font-weight:bold;\">Conferma prenotazione del tavolo</h1><br />
                            <p>Nome: $name</p>
                            <p>Cognome: $surname</p>
                            <p>Persone: $people</p>
                            <p>Data: $data</p>
                            <p>Ora: $time</p>
                            <p>E-mail: $email</p>
                        </body>
                    </html>
                    ";
                    
                    $headers = "MIME-Version: 1.0" . "\r\n";
                    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                    
                    mail($to, $subject, $message, $headers);
                    
                    mysqli_free_result($result);
                    mysqli_close($connection);
                    echo "
                        <script type=\"text/javascript\">
                            window.alert('Tavolo prenotato 2, controlla la tua mail per la conferma digitale.');
                            window.location.replace('index.php');
                        </script>
                    ";
                } else {
                    mysqli_free_result($result);
                    mysqli_close($connection);
                    echo "
                        <script type=\"text/javascript\">
                            window.alert('Disponibilità terminata.');
                            window.location.replace('index.php');
                        </script>
                    ";
                }
            }
    } else {
        mysqli_close($connection);
        echo "
            <script type=\"text/javascript\">
                window.alert('Formato dati errato.');
                window.location.replace('ristorante.php');
            </script>
        ";
    }
?>

我知道可能会更好，但我只是一个初学者，这是我正在为我正在制作的网络开发课程编程的网站，不幸的是，它都是意大利语https://corsowebgo.sbmsp.it/il_leone_pizzeria_ristorante_affittacamere/我我这样做只是为了练习。

标签： phpsql

解决方案

在 sql 语句中使用 SELECT 命令时，mysqli_query() 函数返回一个 mysqli_result_object，因此为了访问结果，我们使用 mysqli_fetch_all() 函数将其转换为数组。它获取所有结果行并将结果集作为关联数组、数字数组或两者返回。而在 INSERT 或 DELETE 命令中，mysqli_query() 返回布尔结果。

所以我修改了你的代码


$sql = "SELECT MIN(total_availability) FROM restaurant WHERE date='$date'";

$result = mysqli_query($link, $sql);

$result_array=mysqli_fetch_all($result,MYSQLI_ASSOC); /*MYSQLI_ASSOC is used to convert it into an associative array*/

foreach ($result_array as $r) 
{
    $min_value=$r['MIN(total_availability)'];
}

$sql_value =$min_value;

if($sql_value > 0)
{
    /*
        Insert row into restaurant table
    */
}

推荐阅读