Need help with multidimensional arrays (I think)

[wisefool]

Hi. Hoping someone can help. From what I've researched I think I need to use a multidimensional array but I'm not getting my head around this problem so there might be another way.

I'm building a basic web app. It's a mysql database of links to county record offices nationwide. Each link is accompanied by info like who last researched the link and when, as well as a note for the next user. So this table is several rows deep, is indexed, and has two LEFT JOIN in the query to merge the user and note with the link.

For visual purposes I want to divide this list by state. So for every different state a header with the state name injects itself between the rows. How do I get my while($row = mysql_fetch_array($results) to subdivide by state? I started to look at using foreach() as a nested loop but spent hours without any luck.

Thanks for any help. First time posting, so if you need more info or anything, let me know. Here's my code below. I stripped out all the formatting stuff to make it more legible, but if I left a closing div or span tag or something somewhere please ignore it. That said, if you see anything else you think my help my code be better or simpler, please let me know.

$query = "SELECT sites.url, sites.site_id, sites.description, sites.state, sites.date, sites.appender_id, notes.comment, notes.author_id, notes.tstamp, users.user_id, users.firstName, users.lastName, users2.firstName AS authorFirstName, users2.lastName AS authorLastName, users2.user_id FROM sites LEFT JOIN users ON sites.appender_id = users.user_id LEFT JOIN notes ON sites.site_id = notes.site LEFT JOIN users AS users2 ON notes.author_id = users2.user_id ORDER BY sites.state sites.description"; 
};

$result = mysql_query($query)
or die(mysql_error());

while($row = mysql_fetch_array($result)){

	// make a layout for each result
	echo $row['state'];
	echo "<a href='".$row['url']."' target='_blank'> ".$row['url']."</a>";


	// show the site description and info for last appending.
	echo "Site Description: ".$row['description']."  | ";
	echo "Last Appended on: ".$row['date']." | by ".$row['firstName']." ".$row['lastName'];


	// show latest note for this site. If no note, skip it.
	if ($row['comment'] != null) {

	// Who wrote the last note and when
	echo "Last Note on: ".$row['tstamp']." By: ".$row['authorFirstName']." ".$row['authorLastName']."<br />";

		// the note
		echo $row['comment'];
		}
	}

[Lsmjudoka]

Seems to me like it would be easier to store the note and last user information in the same row as the link. Example of the table:

Row id | state | URL | last poster | notes | etc...
--------------------------------------------------------------------------------------
1 | CA | url.com | myuser | misc | (any other info)

Then when you query for it, use "ORDER BY state" to order the results. Now for dividing by state, how do you want it do be displayed on your page? (e.g. different pages for each state, a log list with HTML anchors at the start of each state, etc...)

You could create a table for each state with a heading OR have one table with each state heading placed as a 'merged columns' row.

This is pretty simple - no need for arrays. Can be done straight from the db output. E.g. of 'one table' solution:

1. Order your records by state
2. Set a $changestate=""; variable before your while loop.
3. In the loop: check to see if the state value of the record is equal to the $changestate variable. For the first record it will obviously be different. When values are not equal - inject a merged column row with the state's name. Then change $changestate to the name of the current state - but only if there was an inequality!

[wisefool]

You could create a table for each state with a heading OR have one table with each state heading...

Thanks ardav, that sounds exactly like what I want to do. Could I ask what would be the exact syntax for setting the initial value for $changestate ? I've taken a few stabs at it and I just get errors. Do I pull it from $result ? Do I make a separate query? Or can $changestate get its value from $query directly? I'm just confused.

Thanks in advance.

[wisefool]

Seems to me like it would be easier to store the note and last user information in the same row as the link. Example of the table:

Row id | state | URL | last poster | notes | etc...
--------------------------------------------------------------------------------------
1 | CA | url.com | myuser | misc | (any other info)

Then when you query for it, use "ORDER BY state" to order the results. Now for dividing by state, how do you want it do be displayed on your page? (e.g. different pages for each state, a log list with HTML anchors at the start of each state, etc...)

Thanks, I'll look into doing that. I did start out thinking that my notes table would keep a history of comments for all the separate county sites, but in the process of building I learned how to have the history output to a separate file instead, so you'd be right in saying the notes table really isn't necessary.