Checkboxes checked from mysql

Question

I have run into a problem. I have a form which I am trying to add an edit page to insert into mysql. I am getting all the rooms from one table and then I have to check if they are "checked" in another table.

Table 1 (HotelRooms):
ID | HotelRoom | Hid
---------------------
1  | Standard  | 20
2  | Deluxe    | 20
2  | Basic     | 20

Table 2 (HotelPromos)
ID | Promo | Rooms | Hid
-------------------------
1  | 10%   | deluxe, standard | 20

Now in my form I first need to find all rooms belonging to Hotel with Hid 20. so here is my select statement for that:

<?php 
$getRooms = mysql_query("SELECT HotelRoom FROM HotelRooms WHERE HotelId = '$hid'") or die(mysql_error());
$rr = 1;
$numRooms = mysql_num_rows($getRooms);
if($numRooms == 0){
    echo "Lo sentimos pero no hay habitaciones registrados para este hotel. Favor de agregar habitacion para poder agregar una promocion.";
    }
while($ro = mysql_fetch_array($getRooms)){
    $roomName = $ro['HotelRoom'];
    ?>
    <input type="checkbox" name="room<?php echo $rr ?>" /><?php echo $roomName ?>
    <?php
    $rr++;
    }
?>

Does anyone know how I can check the HotelPromos table to see if it is under the column Rooms and add a checked="checked" to it???

Any help would be greatly apreciated!!!

Why do two entries in HotelRooms have the same ID? This should be unique if it's the primary key. Is the ID in this table referring to a specific room, or a type of room within a hotel? — FlyingMolga
– FlyingMolga, Commented Jun 29, 2012 at 20:32
After you retrieve HotelPromos.Rooms from the query, you can use PHP's explode function to break apart the result and compare it to that of HotelRooms.HotelRoom But if you can change the data in the table, it might be better to normalize the Rooms column so you can do the entire comparison with a query. Also, since the case of Rooms and HotelRoom is different, you might need to convert HotelRoom to lowercase in PHP. — FlyingMolga
– FlyingMolga, Commented Jun 29, 2012 at 20:45

andrewtweber · Accepted Answer · 2012-06-30 00:46:54Z

I'm assuming that Basic's ID is actually 3 not 2 and that is a typo.

First of all, in HotelPromos, you should use the room IDs (1, 2) and not the names (Deluxe, Standard).

Second, you should split HotelPromos into two tables to comply with "First Normal Form", part of Database Normalization.

Yes, this is a little more difficult to design and you will have to modify your code, but it makes complex queries much easier to write.

PromoCodes
ID | Promo
----------
1  | 10%

HotelPromos
RoomID | PromoID
----------------
1      | 1
2      | 1

Now you can get all rooms matching a hotel ID

SELECT HotelRooms.ID, HotelRooms.HotelRoom as Name, HotelPromos.PromoID
    FROM HotelRooms
    LEFT JOIN HotelPromos
        ON HotelRooms.ID = HotelPromos.RoomID
    WHERE HotelId = '$hid'

The LEFT JOIN means I want all rooms in Hotel 20 regardless of whether or not they have a promo. The PromoID will be either the ID of the promo code or NULL if no code is available, and you can use this to decide whether or not to check the checkbox.

while( $ro = mysql_fetch_assoc($getRooms) ){
    $roomName = $ro['Name'];
?>
<input type="checkbox" name="room<?= $rr ?>" <?php if( $ro['PromoID'] ) { echo 'checked="checked" '; } ?>/><?php echo $roomName ?>
<?php
    $rr++;
}

And one final thing, I think you should be using $ro['ID'] instead of a new variable $rr

user1032531 · Accepted Answer · 2012-06-29 20:27:05Z

0

Not sure about your table schema, but something like the following. Then check if id is set. PS, escape your input!

SELECT HotelRoom, hp.id
FROM HotelRooms AS hr
LEFT OUTER JOIN HotelPromos AS hp ON hp.hid=hr.id
WHERE HotelId = '$hid'"

answered Jun 29, 2012 at 20:27

user1032531

26.5k77 gold badges248 silver badges422 bronze badges

Collectives™ on Stack Overflow

Checkboxes checked from mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related