need some help with this array sort of

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

need some help with this array sort of

Post by Pyrite »

Ok, I am trying to generate this page (here is a plain html example, http://www.joshuaproject.net/peoplestree.php) from a database.

My SQL query is:

Code: Select all

SELECT rop1, AffinityBloc, rop2, PeopleCluster, rop3, PeopNameAcrossCountries, rog3, Ctry FROM jppeoples ORDER BY AffinityBloc, PeopleCluster, PeopNameAcrossCountries, Ctry
Here is an image of what the result looks like:
http://www.pysquared.com/sqloutput.jpg



Which returns about 16,000 records. I need to output it in nested ul lists to make the page like the example above. This kind of thing hurts my head, but I think I need like some nested if statements comparing

There are 4 levels. Affinity Block, People Cluster, People Group Name, and finally the Country. Here is something like the code I have so far, but I am just not going far enough I guess, can someone help me out with this, or perhaps know a simpler way to do this.

Code: Select all

$sql = "SELECT rop1, AffinityBloc, rop2, PeopleCluster, rop3, PeopNameAcrossCountries, rog3, Ctry FROM jppeoples ORDER BY AffinityBloc, PeopleCluster, PeopNameAcrossCountries, Ctry";
		$rs  = $db->Execute($sql) or die("Query Failure: Turn Debug On to Find Out Why");
		$last_affinity = "";
		$last_pplclust = "";
		$last_pplgroup = "";
		
		while (!$rs->EOF) {

			$r0 = $rs->fields[0];
			$r1 = $rs->fields[1];
			$r2 = $rs->fields[2];
			$r3 = $rs->fields[3];
			$r4 = $rs->fields[4];
			$r5 = $rs->fields[5];
			$r6 = $rs->fields[6];
			$r7 = $rs->fields[7];
			
			if ($last_affinity != $r1) {
				// Start a New Affinity Element
				echo "<li>";
				echo "<a href=\"affinitybloc.php?rop1=$r0\" title=\"Click for detailed listing of this Affinity Bloc\">$r1</a>\n";
				
				if ($last_pplclust != $r3) {
					// Start a New People Cluster
					echo "<ul>";
					echo "<li>";
					echo "<a href=\"peopcluster.php?rop2=$r2\" title=\"Click for detailed listing of this People Cluster\">$r3</a>";
					if ($last_pplgroup != $r5) {
						// Start a New People Group
						echo "<ul>";
						echo "<li>";
						echo "<a href=\"peoples.php?rop3=$r4\" title=\"Click for detailed listing of this People Group\">$r5</a>";

						// Start a New People/Crty
						echo "<ul>";
						echo "<li>";
						echo "<a href=\"peopctry.php?rop3=$r4&rog3=$r6\" title=\"Click for People profile in this Country\">$r7</a>";
						echo "<li>";
					} else {
						echo "<li>";
						echo "<a href=\"peopctry.php?rop3=$r4&rog3=$r6\" title=\"Click for People profile in this Country\">$r7</a>";
						echo "<li>";
					}
				}
			}

			$rs->MoveNext();
		}
Much appreciated if you can help.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It's tabular data, why would you want to use <ul> lists?
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

Because of poor design of the database (not on my part), data is repeated for each record.

If you look at the example, each subset can be expanded.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Okay. I'm not seeing logic that closes a previous list if they are not apart of the same pool, let alone updates the previous record information.

Basically what it appears you need is an array to keep the closing bits required when a section switches. I would probably use the length of the array as an indication of how many elements must be popped off to get the to next level. For example, when shifting to another people cluster the array must be one and only one entry.
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

Sorry I don't follow ya.

Can you provide some suedocode as to what you mean?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

initialize array for closing bits
initialize previous record variables
loop: records
  is affinity different from previous record?
  yes:
    pop elements from closing bits until it's empty
    output new affinity heading
    add affinity footing to closing bits
    reset the previous record variables
  
  is people cluster different from previous record?
  yes:
    pop elements from the closing bits until only the affinity element remains
    output new people cluster heading
    add people cluster footing to closing bits
    reset the previous record variables

  is people group different from previous record?
  yes:
    pop elements from the closing bits until only the affinity and people cluster elements remain
    output new people group heading
    add people group footing to closing bits
    reset the previous record variables

  is country different from previous record?
  yes:
    pop elements from the closing bits until only the affinity and people cluster and people group elements remain
    output new country heading
    add country footing to closing bits
  no:
    output country list item
  
  store current row information to previous record variables
  restart loop
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

feyd, admittedly I really don't follow your suedo code there, but I took another atempt at this, my idea is to close the previous elements before starting new ones. This actually is starting to work, it generates the first block correctly, but seems to fail after that. I don't know if anyone is willing to have a look at this for me, but if so, I really appreciate.

Here is my latest attempt at this:

Code: Select all

<ul id="designtree">
		<?php
		// Brandon's Code to Generate the Tree
		$sql = "SELECT rop1, AffinityBloc, rop2, PeopleCluster, rop3, PeopNameAcrossCountries, rog3, Ctry FROM jppeoples ORDER BY AffinityBloc, PeopleCluster, PeopNameAcrossCountries, Ctry LIMIT 2000";
		$rs  = $db->Execute($sql) or die("Query Failure: Turn Debug On to Find Out Why");

		$last_affinity = "";
		$last_pplclust = "";
		$last_pplgroup = "";

		$first_record = true;
		
		while (!$rs->EOF) {

			$r0 = $rs->fields[0];
			$r1 = $rs->fields[1];
			$r2 = $rs->fields[2];
			$r3 = $rs->fields[3];
			$r4 = $rs->fields[4];
			$r5 = $rs->fields[5];
			$r6 = $rs->fields[6];
			$r7 = $rs->fields[7];

			
			if ($last_affinity != $r1) {

				if (!$first_record) {
					// Close Out the Last Affinity Bloc First
					echo "</li>\n";
				}

				// Start a New Affinity Element
				echo "<li>\n";
				echo "<a href=\"affinitybloc.php?rop1=$r0\" title=\"Click for detailed listing of this Affinity Bloc\">$r1</a>\n";
			}


			if ($last_pplclust != $r3) {
				
				if (!$first_record) {
					// Close Out the Last People Cluster First
					echo "</li>\n";
					echo "</ul>\n";
				}

				echo "<ul>\n";
				echo "<li>\n<a href=\"peopcluster.php?rop2=$r2\" title=\"Click for detailed listing of this People Cluster\">$r3</a>\n";
			}
			
			if ($last_pplgroup != $r5) {
				
				if (!$first_record) {
					// Close Out the Last People Group First
					echo "</ul>\n";
					echo "</li>\n";
					echo "</ul>\n";
				}

				echo "<ul>\n";
				echo "<li>\n<a href=\"peoples.php?rop3=$r4\" title=\"Click for detailed listing of this People Group\">$r5</a>\n";
					echo "<ul>\n";
			}

			// There will be a unique country printed every time
			echo "<li><a href=\"peopctry.php?rop3=$r4&rog3=$r6\" title=\"Click for People profile in this Country\">$r7</a></li>";

			
			$first_record  = false;
			$last_affinity = $r1;
			$last_pplclust = $r3;
			$last_pplgroup = $r5;
			$rs->MoveNext();
		}

		?>
		</ul>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

A general, although more basic approach to the pseudo code I posted can be found in the first (and potentially second) links found in Useful Posts.
Post Reply