Show diferent images acording to different tables

[mathiasc]

I have the following code to show different images according a value of a field in another table. It shows the images correctly where $LocalidadEstado == 4 but then shows repeatedly all the images where $LocalidadEstadoAsiento is not equal $IdUbicacion. If I set L.IdTeatroAsiento = U.Id in the SELECT, it only shows $LocalidadEstado == 4, but not the others. Any suggestions?

 

Here is what is shown and what should be:

 

http://l4c.me/fotos/mathiasc/imagen-descriptiva-de-problema

$resultButacas = mysql_query("SELECT U.Id AS IdUbicacion, U.IdModoTeatro, U.Numero, U.Fila, U.IdSector, U.CordX, U.CordY,
                                P.IdEspectaculo, P.IdFuncion, P.IdSector, P.Precio,
                                L.IdFuncion, L.IdTeatroAsiento AS LocalidadEstadoAsiento, L.IdEstado AS Estado
                                FROM TeatroAsiento U, PrecioEspectaculoSector P, LocalidadEstado L
                                WHERE U.IdSector = '$Sector' 
                                AND P.IdSector = '$Sector'
                                AND P.IdFuncion = '$fn'
                                AND P.IdEspectaculo = '$id'
                                AND L.IdFuncion = '$fn'

                       ");
while($rowButacas=mysql_fetch_array($resultButacas)){ 
$fila = $rowButacas["Fila"];
$asiento = $rowButacas["Numero"];
$IdUbicacion = $rowButacas["IdUbicacion"];
$precio = $rowButacas["Precio"];
$LocalidadEstado = $rowButacas["Estado"];
$LocalidadEstadoAsiento = $rowButacas["LocalidadEstadoAsiento"];

    echo '<div id="Casilla"><label for="asiento' . $fila . '' . $asiento . '">';

    if ($LocalidadEstadoAsiento == $IdUbicacion && $LocalidadEstado == 4) {
        echo '<div id="ubicacion" style="background-image: url(../css/images/asientosNoDisponible.png);"> F: ' . $fila . '<br>Nro: ' . $asiento . '<br>' . $precio . '';
    } else {
        echo '<div id="ubicacion" style="background-image: url(../css/images/asientosDisponible.png);"> F: '.$fila.'<br>Nro: '.$asiento.'<br>'.$precio.'<input id="asiento'.$fila.''.$asiento.'" value="'.$IdUbicacion.'" type="checkbox" name="asientoI[]"><input id="'.$IdUbicacion.''.$precio.'" value="'.$precio.'" type="hidden" name="precioseleccionI[]">';
    }
       echo '</div></label></div>';

}

[requinix]

I don't follow your description, but looking at the query it doesn't seem right: you're selecting from three tables at once and there aren't any relationships set up between them. That means you'll get a cross-product of all the rows: every matching row in TeatroAsiento coupled with every matching row in PrecioEspectaculoSector coupled with every matching row from LocalidadEstado.

 

My Spanish is horrible. How do those three tables relate to each other? What columns in each table share values with what columns from another table?

[mathiasc]

Hello and thanks for replying!
 

I will try to translate everything to english in order to be more descriptive.

 

This is a map of cinema seats.

 

TeatroAsiento is the table where I get the base seats.

 

Id

Row
Column

Sector

 

PrecioEspectaculoSector is the table where I get the price for each group of seats

 

Id

IdFuncion 

Sector

Price

 

LocalidadEstado is the table where I get seat status and I set a row every time a seat is Not Available.

 

Id

IdFuncion 

IdTeatroAsiento

Status (1= Available, 2=Not Available)

 

TeatroAsiento and LocalidadEstado are related by Id and IdTeatroAsiento.

PrecioEspectaculoSector and LocalidadEstado are related by IdFuncion field that is set in the variable $fn

 

 

What I'm trying to do:

If TeatroAsiento.Id exists in LocalidadEstado.IdTeatroAsiento and LocalidadEstado.Status is 2 it should show NotAvailable.png

else it should show Available.png

 

My english is quite poor... I apologize for that.

Edited May 22, 2015 by mathiasc

[requinix]

So you want to show the various seats in a particular sector for a particular show?

 

The relationships that I see are

1. TeatroAsiento.Id = LocalidadEstado.IdTeatroAsiento

2. TeatroAsiento.Sector = PrecioEspectaculoSector.Sector

3. PrecioEspectaculoSector.IdFuncion = LocalidadEstado.IdFuncion

 

Which table you select FROM and which you JOIN doesn't matter very much, so

SELECT ...
FROM TeatroAsiento U
JOIN PrecioEspectaculoSector P ON U.Sector = P.Sector
JOIN LocalidadEstado L ON U.Id = L.IdTeatroAsiento AND P.IdFuncion = L.IdFuncion
WHERE
	U.Sector = '$Sector'
	AND P.IdFuncion = '$fn'
	AND P.IdEspectaculo = '$id'

Looking at your query again, I see that it does kinda have relationships. It's not clear at first sight but because you use $Sector and $fn in multiple places, you basically end up with a relationship.

Anyway, your query gets very close to the above but it doesn't cover the first relationship: TeatroAsiento.Id = LocalidadEstado.IdTeatroAsiento. You can add it into your query, or use the JOIN form (which I recommend) like above.

Edited May 22, 2015 by requinix

[mathiasc]

Hey! Thank you very much!
That works!

But the problem is that it only shows the coincidences on TeatroAsiento.Id = LocalidadEstado.IdTeatroAsiento

I also need to show those and the seats that are in the TeatroAsiento table but not in the LocalidadEstado table.

 

Example:

It shows:

A1 - NotAvailable

A3 - NotAvailable

A5 - NotAvailable

 

It should show:

 

A1 - NotAvailable

A2 - Available

A3 - NotAvailable

A4 - Available

A5 - NotAvailable

 

I've  tried this:

FROM TeatroAsiento U
JOIN PrecioEspectaculoSector P ON U.IdSector = P.IdSector
JOIN LocalidadEstado L ON P.IdFuncion = L.IdFuncion
WHERE
	U.IdSector = '$Sector'
	AND P.IdFuncion = '$fn'
	AND P.IdEspectaculo = '$id'
	ORDER BY U.Id 

But it shows:

 

A1 - NotAvailable

A1 - Available

A1 - Available

A2 - Available

A2 - Available

A2 - Available

A3 - NotAvailable

A3 - Available

A3 - Available

A4 - Available

A4 - Available

A4 - Available

A5 - NotAvailable

A5 - Available

A5 - Available

 

The coincidences on TeatroAsiento.Id = LocalidadEstado.IdTeatroAsiento are 3.

[requinix]

Can you post some sample data from those three tables? I'm still not quite getting how this is supposed to work.

[mathiasc]

Yes, of course!

 

TABLE `TeatroAsiento`

(`Id`, `IdModoTeatro`, `Numero`, `Fila`, `IdSector`) 

VALUES

(135, '1001', 1, '7', '2002')

(136, '1001', 2, '7', '2002')

(137, '1001', 3, '7', '2002')

(138, '1001', 4, '7', '2002')

(139, '1001', 5, '7', '2002')

 

TABLE `PrecioEspectaculoSector`

(`IdEspectaculo`, `IdFuncion`, `IdSector`, `Precio`)

VALUES

('14', '23', '2002', '80.00')

 

TABLE `SectorTeatro`

(`Id`, `Nombre`, `IdModoTeatro`, `ColorHex`, `SectorUbicacion`) 

VALUES

('2002', 'Lat. Izquierdo', '1001', 'ffb000', 'Izquierda')

 

TABLE `LocalidadEstado`

(`IdFuncion`, `IdEspectaculo`, `IdSector`, `IdTeatroAsiento`, `IdEstado`)

VALUES

('23', '14', '2002', '135', 2)

('23', '14', '2002', '136', 2)

('23', '14', '2002', '137', 2)

 

 

I try to do the following:

 

 

'TeatroAsiento.Id', 'LocalidadEstado.IdEstado'

135, 2 (Exists in table LocalidadEstado, so it shows NotAvailable.png)

136, 2 (Exists in table LocalidadEstado, so it shows NotAvailable.png)

137, 2 (Exists in table LocalidadEstado, so it shows NotAvailable.png)

138 (Doesn't exist in table LocalidadEstado, so it shows available.png)

139 (Doesn't exist in table LocalidadEstado, so it shows available.png)

Edited May 22, 2015 by mathiasc

[requinix]

Okay, so there may not be a record in LocalidadEstado (available) or there may be with IdEstado=1 (available) or 2 (not available)? I'm guessing no records means no purchase, 2 means a purchase, 1 means a purchase that was cancelled or similar so the seat is available again.

 

Try a LEFT JOIN

SELECT ...
FROM TeatroAsiento U
JOIN PrecioEspectaculoSector P ON U.Sector = P.Sector
LEFT JOIN LocalidadEstado L ON U.Id = L.IdTeatroAsiento AND P.IdFuncion = L.IdFuncion
WHERE
	U.Sector = '$Sector'
	AND P.IdFuncion = '$fn'
	AND P.IdEspectaculo = '$id'

This way you'll get a row from TeatroAsiento+PrecioEspectaculoSector regardless of whether there's a matching LocalidadEstado record, however any LocalidadEstado.* columns will be NULL if there wasn't a match.

 

So

- L.IdEstado IS NULL: Available

- L.IdEstado=1: Available

- L.IdEstado=2: Not available

and

if ($LocalidadEstado == 2) {
	// not available
} else { // ==1 or ===null
	// available
}

Edited May 22, 2015 by requinix

[mathiasc]

YES!!!
Thank you very very much!!
It worked!!

 

(I'd say 'I love you', but it seems to sound a little hard) hehehe!