Page 1 of 1

need some help with this array sort of

Posted: Mon Feb 26, 2007 1:59 pm
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.

Posted: Mon Feb 26, 2007 2:03 pm
by feyd
It's tabular data, why would you want to use <ul> lists?

Posted: Mon Feb 26, 2007 2:28 pm
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.

Posted: Mon Feb 26, 2007 2:40 pm
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.

Posted: Mon Feb 26, 2007 2:44 pm
by Pyrite
Sorry I don't follow ya.

Can you provide some suedocode as to what you mean?

Posted: Mon Feb 26, 2007 3:12 pm
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

Posted: Sat Apr 28, 2007 5:05 pm
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>

Posted: Sat Apr 28, 2007 5:11 pm
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.