SQL MIN() function value with PHP, how to fetch the value of MIN() function and use it?

Question

So in the following code that is supposed to check availability when a user books a restaurant table I don't know how to fetch the result:

$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
    */
}

What should I do?Thank you.

Optional, not so relevant, this is the original source code after some comments I received, I don't use 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>
        ";
    }
?>

It could be better I know but I'm just a beginner, this is the website I'm programming for a webdevelopment course I'm making, it's all in Italian unfortunately https://corsowebgo.sbmsp.it/il_leone_pizzeria_ristorante_affittacamere/ I'm doing this only for practice.

Start from here [PDO::prepare](https://www.php.net/manual/en/pdo.prepare.php) — M Khalid Junaid, Sep 05 '20 at 08:31
Thank you sir I already use mysqli_real_escape_string() function first and after that preg_match() functions for data format check, I won't post the entire code for obvious purposes, nobody would read 300 lines of code on StackOverflow, so $row = mysqli_fetch_assoc($result)["MIN(total_availability)"]; will do or not? — , Sep 05 '20 at 08:41
Sorry I made a mistake in this case there are 50 lines of code in this file, if somebody wants me to I'll be glad to post them, but everything else is working I guess I only have a doubt about these lines, thank you. — , Sep 05 '20 at 08:52
Check the source code, I updated the post, maybe can you please answer my question now?Thank you all ;-) — , Sep 05 '20 at 09:28
Just a thought. *I don't like PDO*, then why not try [MySQLi Prepared Statement](https://stackoverflow.com/a/2553892/4903314). Also please read [SQL injection that gets around mysql_real_escape_string()](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string). I know you must be fed up by everyone telling you about Prepared Statements but you are in learning phase, so why not learn something better. :-) — Umair Khan, Sep 05 '20 at 11:54
Thank you sir can you please explain me how can somebody inject some sql code if I also use preg_match() function?Thank you ;-) — , Sep 05 '20 at 11:56

score -1 · Accepted Answer · edited Sep 05 '20 at 10:09

While using SELECT command in sql statement, mysqli_query() function returns a mysqli_result_object so in order to access the result we convert it to an array by using mysqli_fetch_all() function. It fetches all the result rows and returns the result-set as an associative array,numeric array or both.Whereas in INSERT or DELETE command mysqli_query() returns a boolean result.

so i modified your code


$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
    */
}

Also you can use mysql alias operator [AS](https://www.w3schools.com/sql/sql_alias.asp) to shorten / change `MIN(total_availability)`. Have a look at https://stackoverflow.com/a/12053349/4903314 — Umair Khan, Sep 05 '20 at 11:58

SQL MIN() function value with PHP, how to fetch the value of MIN() function and use it?

1 Answers1