Challenging organization problems

BennyBrown

I have just started redoing my site into PHP & MySQL tables. That said, I am having a real tough time getting a few things to work. :queasy:

Test Page
Desired Look Page

So I want my test page to stream (from mysql tables) the info and make everything like my desired page. I have two tables:

TABLE 2: vacancies
building_name | type | unit | sq_ft | annual_rate

My question is this: How do I, through a combination of PHP & MySQL, get my desired result? Desired result is like
-Location Name
--List of buildings in alphabetical order within that location, accompanied by vacancy information for each unit listed at that property, and that property only. Must list all vacant units, not just 1.

If you want to see the code I have so far, let me know and I will post it. I really really need help on this one, it's starting to drive me nuts.

matchew

$query = "SELECT * FROM table2 JOIN table1 ON table1.building_name=table2.building_name ORDER BY table2.building_name ASC";
$query = mysql_query($query);
while($row = mysql_fetch_array($query)){
echo $row['location'];
echo $row['unit'];
echo $row['sq_ft'];
}

BennyBrown

Well that is kinda close: here just part of the formatting done with your script on this page. :queasy:

The problem with an inner join in this way is that if 1 building has 5 vacant units in it, then the query outputs that buildings information from table 1 5 times, which is why some of the buildings on that page I linked to are repeated several times in a row. Instead of this, I want each building once, with all the vacant units listed next to it in tabular form, like this page here

So...any other suggestions? :queasy:

Roger_Ramjet

It is called loop control and has been asked and answered untold times in these forums. Use the search, top menu, and you will find all the code you ever dreamed of to make this happen for you.

Basic solution is to track the current building, in your case, and only output the building details when it changes, otherwise you just output the other data.

BennyBrown

Did the search on here and google, and found a few things that started me off in the right direction. Loop control is really interesting, do you have some links where I could read up some more and try to understand it better?

I have used loop control on ordering building locations correctly, as well as sorting and ordering the buildings by location and name almost correctly. I have some problems before I move on to sorting and ordering individual suites correctly.

$curr_loc = ' ';
$curr_building = ' ';
while ($row = mysql_fetch_array($result)) {

$loc = $row['location'];
$building = $row['building_name'];
if ($loc != $cur_loc) {
	echo '<h5><a name="' . $loc . '">' . $loc . '</a></h5>' . "\n";
	echo '<table class="listings" cellpadding="0" cellspacing="0">' . "\n";
	echo '<thead>' . "\n";
	echo '<tr>' . "\n";
	echo '<th style="padding: 0px 0px 0px 5px;" width="155px">Picture</th>' . "\n";
	echo '<th>Building Name &amp; Address</th>' . "\n";
	echo '<th>Type</th>' . "\n";
	echo '<th>Unit</th>' . "\n";
	echo '<th>Sq. Ft.</th>' . "\n";
	echo '<th>Annual Rate</th>' . "\n";
	echo '</tr>' . "\n";
	echo '</thead>' . "\n";
		$cur_loc = $loc; 
		$curr_building = ' ';
	}
	if ($building != $cur_building) {
 		echo '<tbody class="' . $color_switch . '">' . "\n"; 
 		echo '<tr>' . "\n"; 
 		echo '<td rowspan="6" width="155px"><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information"><img src="' . $ROOT . '/images/' . $row['picture'] . '" class="listingpic" width="140" height="105"></a></td>' . "\n"; 
 		echo '<td rowspan="6">' . "\n"; 
 		echo '<dl>' . "\n"; 
 		echo '<dt><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information">' . $row['building_name'] . '</a></dt>' . "\n"; 
 		echo '<dd>' . "\n"; 
 		echo '<address>' . $row['building_address_1'] . '<br>' . $row['building_address_2'] . '<br>&nbsp;<br>&nbsp;<br>&nbsp;<br><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information">View Complete Information</a></address>' . "\n"; 
 		echo '</dd>' . "\n"; 
 		echo '</dl>' . "\n"; 
		echo '</td>' . "\n"; 
		echo '</tbody>' . "\n";
				 if ($color_switch == 'first') {
    				  $color_switch = 'second';
				 } else {
  					    $color_switch = 'first';
      }
			$cur_building = $building;
	}
echo '</table>'; 		
 	}

This is output on this page. I want the </table> to be after every completed location segment, so:

<table>
LOCATION NAME:
-Building 1
--Building 1 units
-Building 2
--Building 2 units
ETC...
</table>
<table>
LOCATION 2 NAME: ...

Where do I put </table> in my code for it to do this correctly?

Roger_Ramjet

OK, you have it starting the new table and just need to close any prior tables.

Now the only time you do not need to close a table on location change is the first time into the loop when $curr_loc is still an empty string: or you can initiate it with any value you can test for that will not later match a location.
So you test for that and when $curr_loc is not empty you close the table and then continue with your existing code.

The same applies for any level of nested loops that you want to generate code blocks, be they tables, or divs etc. Set your loop-control to NULL or empty and test for that to signify first time through the loop.

Remember, you also have to close all outstanding tables, divs etc outside the loop once all the data is displayed: that is outside the while loop that processes the query results. Again you can use the loop-control var to test if a table was opened to begin with.

BennyBrown

Ok that makes sense. However, when I try it I seem to have it backwards. Arg im so close! For some reason, my anchors will appear before a table has opened, and correctly in other places, but will not appear on the last spot.

Instead of:
-no
-yes
-yes
-yes
-yes

I have made it
-yes
-yes
-yes
-yes
-no

Once again here is the code now. What am I doing wrong with the placement of the </table> and </tbody> anchors?

$curr_loc = ' ';
$curr_building = ' ';
$curr_unit = ' ';

while ($row = mysql_fetch_array($result)) {
	$loc = $row['location'];
	$building = $row['building_name'];
	$unit = $row['unit'];

if ($loc != $cur_loc) {
	echo '</table>' . "\n"; 		
}
if ($loc != $cur_loc) {
	echo '<p>&nbsp;</p>' . "\n";
	echo '<h5><a name="' . $loc . '">' . $loc . '</a></h5>' . "\n";
	echo '<table class="listings" cellpadding="0" cellspacing="0">' . "\n";
	echo '<thead>' . "\n";
	echo '<tr>' . "\n";
	echo '<th style="padding: 0px 0px 0px 5px;" width="155px">Picture</th>' . "\n";
	echo '<th>Building Name &amp; Address</th>' . "\n";
	echo '<th>Type</th>' . "\n";
	echo '<th>Unit</th>' . "\n";
	echo '<th>Sq. Ft.</th>' . "\n";
	echo '<th>Annual Rate</th>' . "\n";
	echo '</tr>' . "\n";
	echo '</thead>' . "\n";
		$cur_loc = $loc; 
		$curr_building = ' ';
	}
	if ($building != $cur_building) {
 		echo '</tr>' . "\n";
 		echo '</tbody>' . "\n";
	}
	if ($building != $cur_building) {
 		echo '<tbody class="' . $color_switch . '">' . "\n"; 
 		echo '<tr>' . "\n"; 
 		echo '<td rowspan="6" width="155px"><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information"><img src="' . $ROOT . '/images/' . $row['picture'] . '" class="listingpic" width="140" height="105"></a></td>' . "\n"; 
 		echo '<td rowspan="6">' . "\n"; 
 		echo '<dl>' . "\n"; 
 		echo '<dt><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information">' . $row['building_name'] . '</a></dt>' . "\n"; 
 		echo '<dd>' . "\n"; 
 		echo '<address>' . $row['building_address_1'] . '<br>' . $row['building_address_2'] . '<br>&nbsp;<br>&nbsp;<br>&nbsp;<br><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information">View Complete Information</a></address>' . "\n"; 
 		echo '</dd>' . "\n"; 
 		echo '</dl>' . "\n"; 
		echo '</td>' . "\n"; 

				 if ($color_switch == 'first') {
    				  $color_switch = 'second';
				 } else {
  					    $color_switch = 'first';
      }
			$cur_building = $building;
	}
	if ($unit != $cur_unit) {
 		echo '<td>' . $row['type'] . '</td>' . "\n"; 
 		echo '<td>' . $row['unit'] . '</td>' . "\n"; 
 		echo '<td>' . $row['sq_ft'] . '</td>' . "\n"; 
 		echo '<td>' . $row['annual_rate'] . '</td>' . "\n"; 
 		echo '</tr>' . "\n";
 		echo '<tr>' . "\n";
 		$cur_unit = $unit;
	}
}

Roger_Ramjet

Well your logic is wrong in places. It is at times like this that you need to stop looking at code and go back to defining the basic algorithm.

If it is a new location: start a new table
If there is a prior table then close that first

$cur_loc = NULL;

while ($row = mysql_fetch_array($result)) {
    $loc = $row['location']; 
// is it a new location?
if ($loc != $cur_loc) {
// is there a prior table? then close it first
   if (!is_null($curr_loc)) {
      echo '</table>';
   }
// then start the new table
}

I've used NULL to initialise the loop control var, but it can be anything that you can test for: first time through the loop there is no table to close so you need to know if it is the first time or not = close table or not

BennyBrown

Wow, that's totally right. I sometimes do get caught up in the code and lose track of what I am actually saying in my algorithm. Thanks for the help understanding that! Loop control is hard for me to understand for some reason...

Ok, I think I have one last problem on this page that I don't even know the correct operation to complete. I need to make a variable controlled rowspan for the cells that contain just building information so that it can span the whole vertical distance that is taken up by the list of vacant units. The lowest i want the rowspan is 6, and if there are more vacant units in a building than 6, I want the rowspan to equal that. So I think I have this part right so far:

$mysqlcounter = ?????????
if ($mysqlcounter <= '6') {
 $counter = '6';
} else {
 $counter = $mysqlcounter;
}

Then I can call it at:

if ($building != $curr_building) {
	 		if (!is_null($curr_building)) {
			echo '</tr>' . "\n";
	 		echo '</tbody>' . "\n";
			}
	 		echo '<tbody class="' . $color_switch . '">' . "\n"; 
	 		echo '<tr>' . "\n"; 
	 		echo '[COLOR=Red]<td rowspan="6"[/COLOR] width="155px"><a href="' . $ROOT . '/' . $row['href'] . '" title="View complete information"><img src="' . $ROOT . '/images/' . $row['picture'] . '" class="listingpic" width="140" height="105"></a></td>' . "\n"; 
	 		echo '[COLOR=Red]<td rowspan="6">[/COLOR]' . "\n";

So now my problem seems to be: How do I count the number of vacant units that belongs to a building, and then call that as a variable, and still have it repeat for each building and keep everything straight? I have been trying to pour over the PHP and MySQL manuals, so don't scream RTFM quite yet 😃, but there are so many operators and functions that it is hard to find anything even remotly resembling what I think I need.