Mysqli - multiple help consultation

Question

Mysqli - multiple help consultation

Navigation

#1 by (2 votes)

1

I need to consult the following id where the following is fulfilled:

Values entered by the user from 1 to 5 (people per room), and I have to obtain the id's of the hotels where it is true that there is a place, for example;

ROOM-1 : 2 PASSENGERS;

ROOM-2 : 3 PASSENGERS;

DATABASE:

CORRECT RESULT 1 =

        FECHA:18/09/2018;
        ID:HOTEL100001;
        DOBLE:CUPO 1;
        TRIPLE:CUPO 1;
        CUADRUPLE:CUPO 1;

CORRECT RESULT 2 =

        FECHA:18/09/2018;
        ID:HOTEL100002;
        DOBLE:CUPO 0;
        TRIPLE:CUPO 4;
        CUADRUPLE:CUPO 0;

INCORRECT RESULT 3 =

        FECHA:18/09/2018;
        ID:HOTEL100003;
        DOBLE:CUPO 0;
        TRIPLE:CUPO 1;
        CUADRUPLE:CUPO 0;

How do I search exclusively?

Thanks for the help

MySQL query

$ mysqli = new mysqli ("localhost", "root", "pass", "db"); / * verify connection / if (mysqli_connect_errno ()) { printf ("Connect failed:% s \ n", mysqli_connect_error ()); exit (); } / create a prepared statement * /

                $cupo_habitacion1=1;
                $personashabitacion1=2;
                $cupo_habitacion2=1;
                $personashabitacion2=2;
                if ($stmt = $mysqli->prepare("
                SELECT id_hotel from cupo_tarifa_hoteles WHERE capacidad_habitacion>=? AND cupo_hotel >=?
                INTERSECT
                SELECT id_hotel from cupo_tarifa_hoteles WHERE capacidad_habitacion >=? AND cupo_hotel >=? ")) {
                mysqli_stmt_bind_param($stmt, "ssss", $personashabitacion1,$habitacion1,$personashabitacion2,$habitacion2);
                    /* ejecutar la consulta */
                    mysqli_stmt_execute($stmt);
                    /* ligar variables de resultado */
                     mysqli_stmt_bind_result($stmt, $id_hotel);
                    /* obtener valor */

                    while ( $stmt->fetch() ) {
                        echo $id_hotel;


                    }

                    /* cerrar sentencia */
                    $stmt->close();
                }
                /* cerrar conexión */
                $mysqli->close();

php mysqli

asked by leonardo sebastian 18.09.2018 в 14:35

source

1 answer

My SQL Query gives a wrong result Delete repeated components java

score 2 · Accepted Answer

You should be doing set intersections.

That is, the hotel that is worth you, will be the result of searching:

1.- El hotel donde haya al menos, 1 habitación de 2,   
2.- De la lista anterior, deberás buscar los que tengan una de 3  
3.- De la lista anterior, deberás buscar los que tengan otra de 2  
4.- De la lista anterior, deberás buscar los que tengan una de 1

That is, 4 queries that must be "put together / crossed"

Therefore, it should be something like:

--Habitacion A
SELECT id_hotel from HOTELES where capacidad_habitacion = 2 and cupo_hotel > 0
INTERSECT
SELECT id_hotel from HOTELES where capacidad_habitacion = 3 and cupo_hotel > 0
INTERSECT
SELECT id_hotel from HOTELES where capacidad_habitacion = 2 and cupo_hotel > 0
INTERSECT
SELECT id_hotel from HOTELES where capacidad_habitacion = 1 and cupo_hotel > 0

The problem is that when you do that, you are not taking into account that the space of the hotel must be updated (if you have taken into account the first room of two people, it is not valid if the hotel only has one of two people)

Therefore, you should first summarize the rooms of the same number of clients, and therefore, so you would know directly how many.

SELECT id_hotel from HOTELES where capacidad_habitacion = 2 and cupo_hotel > 1
INTERSECT
SELECT id_hotel from HOTELES where capacidad_habitacion = 3 and cupo_hotel > 0
INTERSECT
SELECT id_hotel from HOTELES where capacidad_habitacion = 1 and cupo_hotel > 0

In this way you are looking for a hotel with 2 rooms of 2 people, 1 of 3 and 1 of 1.

I hope it helps you raise it

The operator you are looking for is INTERSECT (check here your docs)