If Condition....

[zuu2343]

I have Hotels and Customers tables (mysql) which gives information about which customer stayed in which hotel.

Here is the info

Table: Hotels
Hotel ID
Hotel Name

Table: StayedIn
StayedInID:
CustomerID:
StayedIn: (HotelID)

What I need is to give report where each customer stayed in like below

==============================================
Customer Hotel1 Hotel2 Hotel3 Hotel4
1 X Yes X X
2 X X Yes X
3 Yes X Yes X

I can get the data from MYSQL and present it as below

===============================================
Customer
1 Hotel2
2 Hotel3
3 Hotel1 Hotel3

How can I create an if condition that would help me to create a table which lists X and Yes automatically like the first table. For example: For customer 1, from the array, it knows he did not stay in Hotel1 so put an X for it between <td></td>, For Hotel 2, well he stayed, hotel 3 put an X.

The if condition I created which works for the first data shown below, however it repeats the X's.... Could you give me a hand on this?

Thank you in advance,


if ($StayedId== 1) { echo "<td>"; echo "Yes"; echo "</td>"; } if ($StayedId> 1) { echo "<td>"; echo "X"; echo "</td>"; }
if ($StayedId== 2) { echo "<td>"; echo "Yes"; echo "</td>"; } if ($StayedId > 2) { echo "<td>"; echo "2 X"; echo "</td>"; }
if ($StayedId== 3) { echo "<td>"; echo "Yes"; echo "</td>"; } if ($StayedId > 3) { echo "<td>"; echo "3 X"; echo "</td>"; }
if ($StayedId== 4) { echo "<td>"; echo "Yes"; echo "</td>"; }

 

[KarveR]

post your sql query and I'll show you how to make mysql do it for you, the others here can show you how to make PHP do it

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[zuu2343]

<?php
$query = "SELECT * FROM Customers";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$CustomerID = $row[CustomerID];

$query2 = "SELECT * FROM StayedID WHERE CustomerID = '$CustomerID' ORDER By HotelID";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2))
{
$StayedID = $row[StayedID];
}


}
?>

 

[KarveR]

$query2 = "SELECT StayedInID,CustomerID,if(StayedIn is not null,"Yes","X") FROM StayedID WHERE CustomerID = '$CustomerID' ORDER By HotelID";

think I have your tables right, however you seem to be selecting from StayedID but show the table in the first post as StayedIN

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[zuu2343]

I tried your SQL but still did not receive it the way I needed to. Do you this if the reson could be becuase there would be no NULL data for StayedIN table because every data entered there would have a key automatically?

By the way, thank you for your replies...

Here is the needed layout again,
Customer Hotel1 Hotel2 Hotel3 Hotel4
1 X Yes X X
2 X X Yes X
3 Yes X Yes X

 

[Tarwn]

This is a little ugly, but basically what your looking for is a a table with every possible combination of customer and hotel, with some entry to indicate whether the customer stayed in that hotel. (Warning, I've been doing a lot of MS SQL today so this will need some modification)

SELECT HotelID, HotelName, CL.CustomerID, StayedID
FROM (Hotels H, (SELECT DISTINCT CustomerID FROM StayedIN) AS CL) 
   LEFT OUTER JOIN StayedIN SI ON H.HotelID = SI.HotelID AND CL.CustomerID = SI.CustomerID
ORDER BY HotelID, CL.CustomerID

If my brain isn't completely gone from working all day, then this should return a row for every possible customer/hotel combination, with the StayedID set if the cutomer stayed in the hotel (or NULL if they did not).

-T

 

[zuu2343]

Thank you so much for your reply. I am afraid the SQL statement you providied did not do the job... I am still trying...My head is about to explode