[RESOLVED] PHP Loop Query: Part II

[UrbanCondor]

Hi guys,

I am having trouble with a loop query. My initial query was resolved in this thread however I want to display the data slightly differently which is causing me some problems.

Allow me to set the scene:

I have a database with 3 tables (Library, Shelf & Book) as seen here:

http://www.alistairmahoney.com/z_sgoldie/my_tables.html

When I run a standard JOIN query against all 3 tables I get these results:

http://www.alistairmahoney.com/z_sgoldie/my_result.html

Now, I want to know what kind of PHP Loop I would need to write to get my results to look like this (or something similar) on an HTML page:

Green Library
Shelf A: Book 1, Book 7, Book 13, Book19
Shelf B: Book 2, Book 8, Book 14, Book 20
Address: 123 Green Street, Greensville
Opening Hours: 9am - 5pm

Blue Library
Shelf C: Book 3, Book 9, Book 15, Book 21
Shelf D: Book 4, Book 10, Book 16
Address: 456 Blue Road, Bluetown
Opening Hours: 8am - 6pm

Yellow Library
Shelf E: Book 5, Book 11, Book 17
Shelf F: Book 6, Book 12, Book 18
Address: 789 Yellow Lane, Yellopolis
Opening Hours: 9am - 2pm

In my previous thread someone showed me how to do it so my results would display the books at the end of each library results, but I want to know how to display them so that they are in the middle of each library result (I hope that makes sense). Cheers!

Attached is my DB code if required.

[NogDog]

The details of the query and actual column names are probably wrong, but this should give you an idea. Basically, I would build up a multi-D array from the query results, then loop on that array to generate the output:

$sql = "SELECT blah blah blah";
$result = mysql_query($sql);
// populate array with query results
$data = array();
while($row = mysql_fetch_assoc($result)) {
   $data[$row['library_name']]['shelf'][$row['shelf']][] = $row['book'];
   // these will overwrite with same values for each row for this library
   $data[$row['library_name']]['address'] = $row['address'];
   $data[$orw['library_name']]['hourse'] = $row['hours'];
}

This would build an array like:

array (
   'Green Library' => array (
      'shelf' => array (
         'A' => array (
            0 => 'Book 1',
            1 => 'Book 7',
            2 => 'Book 13',
            3 => 'Book 19'
         ),
         'B' => array (
            0 => 'Book 2',
            1 => 'Book 8',
            2 => 'Book 14',
            3 => 'Book 20'
         )
      ),
      'address' => '123 Green Street, Greensville',
      'hours' => '9am - 5pm'
   ),
   'Blue Library' => array(
      'shelf' => array (
         'C' => array (
            0 => 'Book 3',
            1 => 'Book 9',
            2 => 'Book 15',
            3 => 'Book 21'
         ),
         'D' => array (
            0 => 'Book 4',
            1 => 'Book 10',
            2 => 'Book 16',
         )
      ),
      'address' => '456 Blue Road, Bluetown',
      'hours' => '8am - 6pm'
   ),
   /* etc.... */
)

Which could then be output as:

// output array:
foreach($data as $library => $info) {
   echo "<h3>$library</h3>\n";
   foreach($info['shelf'] as $shelf => $books) {
      echo "<p>Shelf $shelf: " . implode (', ', $books) . "</p>\n";
   }
   echo "<p>Address: " . $info['address'] . "</p>\n";
   echo "<p>Opening Hours: " . $info['hours'] . "</p>\n";
}

[UrbanCondor]

Thanks mate, that's awesome. Worked a treat ;-)

[UrbanCondor]

Ok I'm not sure how to mark this as unresolved again, but I have a related query.

How would I adjust the code if I wanted to display all the books a library has regardless of what shelf they are on? i.e., what if I wanted my results to look like this:

Green Library
Books: Book 1, Book 7, Book 13, Book19, Book 2, Book 8, Book 14, Book 20
Address: 123 Green Street, Greensville
Opening Hours: 9am - 5pm

Blue Library
Books: Book 3, Book 9, Book 15, Book 21, Book 4, Book 10, Book 16
Address: 456 Blue Road, Bluetown
Opening Hours: 8am - 6pm

Yellow Library
Books: Book 5, Book 11, Book 17, Book 6, Book 12, Book 18
Address: 789 Yellow Lane, Yellopolis
Opening Hours: 9am - 2pm

I figure it has something to do with the foreach statement but a trial and error has left me stumped!

[johanafm]

Assuming you have the structure NogDog suggested (else modify as needed)

foreach ($books as $library => $shelves) {
	echo '<html tags as needed>'.$library.'</same...>';
	$allBooks = array();
	foreach ($shelves as $books) {
		$allBooks = array_merge($allBooks, $books);
	}
	echo '<tags>Books: ' . implode(', ', $allBooks) . '</tags>';
}

[UrbanCondor]

OK, given both suggestions here is what my code is looking like so far:

$q = "SELECT l.lib_id, l.lib_name as library_name, l.lib_address as address, l.lib_open as hours, 
s.s_id, s.s_name as shelf_name, b.bk_id, b.bk_name as book
FROM library l, shelf s, book b
WHERE l.lib_id = s.lib_id
AND b.bk_shelf = s.s_id
ORDER BY lib_id, s_name, bk_name";

$result = mysql_query($q) or die(mysql_error());
$num_rows = mysql_numrows($result);
$data = array(); 

while($row = mysql_fetch_assoc($result)) { 
   $data[$row['library_name']]['shelf_name'][$row['shelf_name']][] = $row['book']; 
   // these will overwrite with same values for each row for this library 
   $data[$row['library_name']]['address'] = $row['address']; 
   $data[$row['library_name']]['hours'] = $row['hours']; 
}

foreach ($book as $library_name=> $shelf_name) { 
    echo '<h1>'.$library_name.'</h1>'; 
    $allBooks = array(); 
    foreach ($shelf_name as $book) { 
        $allBooks = array_merge($allBooks, $book); 
    } 
    echo '<p>Books: ' . implode(', ', $allBooks) . '</p>';
	echo '<p>Address: ' . $address . '</p>';
	echo '<p>Hours: ' . $hours . '</p>';
}

Now I'm getting an error on the second part of the code, so there must be something wrong with this foreach:

foreach ($book as $library_name=> $shelf_name) { }

Error reads: Invalid argument supplied for foreach()

Can someone please advise what my code should look like in it's entirety? I'm just simply not familiar enough with the foreach command and with arrays. Just as a reminder, this is what I want the output to look like:

Green Library
Books: Book 1, Book 7, Book 13, Book19, Book 2, Book 8, Book 14, Book 20
Address: 123 Green Street, Greensville
Opening Hours: 9am - 5pm

Thanks in advance for your help!

[UrbanCondor]

OK, I managed to work it out eventually. Here is my solution:

QUERY & RESULT:

$q = "SELECT l.lib_id, l.lib_name, l.lib_address, l.lib_open, s.s_id, s.s_name, b.bk_id, b.bk_name
FROM library l, shelf s, book b
WHERE l.lib_id = s.lib_id
AND b.bk_shelf = s.s_id
ORDER BY lib_id, s_name, bk_name";

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

ARRAY LOOP (to put the query results into an array):

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

$library_array[$row['lib_id']]["library_id"] = $row['lib_id'];
$library_array[$row['lib_id']]["library_name"] = $row['lib_name'];
$library_array[$row['lib_id']]["library_address"] = $row['lib_address'];
$library_array[$row['lib_id']]["library_open_hours"] = $row['lib_open'];

//This is the book array which contains the BOOK ID and the BOOK NAME
$library_array[$row['lib_id']]["book_id"][$row['bk_id']] = $row['bk_name'];

}

OUTPUT LOOP (to output the array into HTML form):

foreach($library_array as $library_id => $library_details) { 
	echo "<h3>".$library_details['library_name']."</h3>\n";
	echo "<p>Books: ";
	echo implode(", ",$library_details["book_id"]);
	echo "</p>\n";
	echo "<p>Address: " . $library_details['library_address'] . "</p>\n"; 
	echo "<p>Opening Hours: " . $library_details['library_open_hours'].</p>\n"; 
}

This produces the following display of results:

Green Library
Books: Book 1, Book 7, Book 13, Book19, Book 2, Book 8, Book 14, Book 20
Address: 123 Green Street, Greensville
Opening Hours: 9am - 5pm

Please critique my code if you think it can be improved.

[johanafm]

Currently you are looping through the entire result set once to create the array, then looping through the entire array to output data. Unless you need the array elsewhere, you could do something along the lines of

$addr = '';
$hours = '';
$first = true;
$first_book = true;
while ($row = mysql_fetch_assoc()) {
	# Current book belongs to another library than the previous book.
	# Works due to "order by lib_id"
	if ($addr != $row['lib_address']) {
		# End the previous library's output with address and opening hours
		if (!$first) {
			echo "</p>\n";				# close "Books" paragraph
			echo '<p>Address: ' . $addr . "</p>\n";
			echo '<p>Opening Hours: ' . $hours . "</p>\n";
		}
		#
		$first = false;
		$addr = $row['lib_address'];
		$hours = $row['lib_open'];

	# Start the current library's output with library name and "Books: "
	echo "<h3>".$row['lib_name']."</h3>\n";
	echo "<p>Books: ";
	$first_book = true;
}
echo (!$first_book ? ', ': '') . $row['bk_name'];
$first_book = false;
}

But unless you actually need your code to be more efficient, I'd stick to what you allready have.