Hi everyone,
I have a slight issue that I have been unable to resolve.

Firstly a little explanation, What this function does is pull off results from a table(tenants), specifically using 1 field(tenants_roomnum).

It should loop through the while statement and build a list of all rooms for the property and if there is a tenant assigned to that property append Room # with 'occupied'.

I have narrowed down the problem to the while loop itself, in that I cant transfer the variable $db_field['tenants_roomnum'] for EVERY tenant into the while loop, even if i set $roomNum = $db_field.... It only uses the highest value, in our examples, room 4.

In the examples I am using for testing there is 3 tenants assigned to room 4, room 3 and room 2.

The while loop only executes correctly on the highest room value, i.e. 4

I have commented the code with notes and feel free to ask questions because I must be missing something and I am totally stuck. Thanks for your help.

Neil

function roomsTaken($room, $property) 
{
	include "../includeDbDetails.php";

// roomCount() returns the amount of rooms that a property
// has. - Working. In our example, it returns the value 5.
$roomCounter = roomCount($property);

// To be used by our loop, starts the room counting at 1, because
// obviously we do not have a room 0.
$i = 1;

$tenantsdb = mysql_connect($host, $user, $pass); // Connect
$db_connect = mysql_select_db($name, $tenantsdb); // Select which db

if ($db_connect) // Have we connected? If yes:
{ 
	$SQL = "SELECT * FROM tenants where property_id = '$property'"; // Our SQL statement, works fine. $property = 1
	$tenantsresult = mysql_query($SQL);
	{
		while ($db_field = mysql_fetch_assoc($tenantsresult))
		{
			while($i <= $roomCounter) // In our testing, $roomCounter = 5
			{
				echo "room: $i"; // Display the room number

				if($db_field['tenants_roomnum'] == $i) // In our testing there is 3 tenants assigned to $property
					echo " occupied";

				echo "<br />"; // For neatness.
				$i++; // Increment the room counting.
			}
			/* The above while statement returns:

			room: 1
			room: 2
			room: 3
			room: 4 occupied
			room: 5

			This is incorrect, it should return:

			room: 1
			room: 2 occupied
			room: 3 occupied
			room: 4 occupied
			room: 5
			*/

			echo $db_field['tenants_roomnum'] . "<br />";
			/* If we list all entry' room numbers we are provided with:

			4
			3
			2

			This proves that there are three tenants in rooms 4,3 and 2.
			*/
		}
	}
	mysql_close($tenantsdb); // Close off the connection
} // Uh oh, our database is either wrong or its down.
else 
{ 
	print "Database NOT Found ";
	mysql_close($tenantsdb); // Close the connection.
}
}

    There does indeed seem to be an issue with variables since you never reset the variable $i. Not quite a scope issue in my books. $i exist for the entire script you posted.

    while ($db_field = mysql_fetch_assoc($tenantsresult))
{
	$i = 1;
	while($i <= $roomCounter) // In our testing, $roomCounter = 5

    Also, while it's always possible to use either a for or a while loop, you will generally encounter for loops when you need variable initialization, use a simple increment and have a simple end case statement:

    while ($db_field = mysql_fetch_assoc($tenantsresult))
{
	for($i = 1; $i <= $roomCounter; ++$i) // In our testing, $roomCounter = 5

    As your code works now:
    1a. Get the next row from the result set
    1b. Go over all room numbers and check for matches
    2a. Get the next row from the result set
    2b $i = 6. do nothing
    3a. Get the next row from the result set
    3b $i = 6. do nothing
    ...

    The reason it matches for room number 4, is simple that the first returned row lives in room 4. If you order your result set any other way, you would get different results.

      What kind of data is tenants_roomnum? Is it an integer or multiple values separated with commas? And do you get all the data right if you run print_r($db_field) in the first while loop?

      Some improvements:
      - Singlequotes is more preferable as default quotes since PHP don't have to search the strings for variables (as in your 25th row - I'm never attaching variables like that for some reasons).
      - If you use mysql_select_db($name, $tenantsdb) or die(mysql_error()); you'll have the MySQL error printed out if there appears any, and you don't have to check if the database selection was successful or not (row 16).
      - Why not use constants instead of variables in includeDbDetails.php? I guess they'll never change.

        Also, I'm guessing that a tenant is only occupying one room at a time. The two nested loops are saying "Is this tenant staying in room 1? Room 2? Room 3...?" Presumably only one of those questions will have a "yes" answer. "Okay; now what about this tenant...?"

        $rooms = array_fill(1, 5, false); // Five rooms, numbered 1..5
while ($db_field = mysql_fetch_assoc($tenantsresult)) 
    $rooms[(int)$db_field['tenants_roomnum']] = true;

foreach($rooms as $roomnum=>$occupied)
{
    echo $roomnum.": ";
    if($occupied) echo "occupied";
    echo "<br />";
}

          Also, I'm guessing that a tenant is only occupying one room at a time. The two nested loops are saying "Is this tenant staying in room 1? Room 2? Room 3...?" Presumably only one of those questions will have a "yes" answer. "Okay; now what about this tenant...?"

          $rooms = array_fill(1, 5, false); // Five rooms, numbered 1..5
while ($db_field = mysql_fetch_assoc($tenantsresult)) 
    $rooms[(int)$db_field['tenants_roomnum']] = true;

foreach($rooms as $roomnum=>$occupied)
{
    echo $roomnum.": ";
    if($occupied) echo "occupied";
    echo "<br />";
}
            Write a Reply...