Page 1 of 1

Displaying a title only once

Posted: Thu Jul 13, 2006 4:47 am
by kathrynd
Hi

I really hope someone can help but I'm trying to display data from my database in order of the particular craft a person is associated with. I can display all the necessary data in the database but I only want to display the craft once. So if there are two people who come under the craft 'Crafts' I only want to display the heading once and then feature both artits details underneath that heading. If I manually put in the heading it displays it even when you on the next page where there are no artists for that craft. This is the code I have for the page.

Code: Select all

<?php 

// Set the page title and include the HTML header.
$page_title = 'Staffordshire Open Studios - Artists Listings';
include_once ('includes/header_directory.html');

require_once ('../mysql_connect.php'); // Connect to the database.

$display = 4;

// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
	$num_pages = $_GET['np'];
	} else { // Need to determine.
	$query = "SELECT craft, name, description, address, tel, mobile, email, website FROM artists ORDER by craft ASC"; // Standard query.
	$query_result = mysql_query ($query);
	
	$num_records = @mysql_num_rows ($query_result);
	
	if ($num_records > $display) { // More than 1 page.
		$num_pages = ceil ($num_records/$display);
		} else {
			$num_pages = 1;
		}
	}
	
	// Determine where in the database to start returning results.
	if (isset($_GET['s'])) { // Already been determined.
		$start = $_GET['s'];
	} else {
		$start = 0;
	}
	
	// Make the query.
	$query = "SELECT * FROM artists ORDER by craft ASC LIMIT $start, $display";
	$result = mysql_query ($query); // Run the query.
	$num = mysql_num_rows ($result); // How many artists are there?
	
	if ($num > 0) { // If it ran OK, display the records.
	
		echo "<h1>ARTISTS LISTINGS</h1><span class=\"small\">Details of other artists operating in the County of Staffordshire.<br><br></span>";
		
					// Make the links to other pages, if necessary.
		if ($num_pages > 1) {
			
			echo '<table width="100%" align="right"><tr><td align="right">';
			// Determine what page the script is on.
			$current_page = ($start / $display) + 1;
			
			echo'<table align="right"><tr>';
			// If it's not the first page, make a Previous button.
			if ($current_page !=1) {
				echo '<td align="right"> <a href="artists_listings.php?s=' . ($start - $display) . '&np=' . $num_pages . '"><img src="images/arrow_left.jpg" width="28" height="34" border="0"></a> </td>';
			}
			
			// Make all the numbered pages.
			for ($i = 1; $i <= $num_pages; $i++) {
				echo '<td align="center">';
				if ($i != $current_page) {
					echo ' <a class="small_link_underline" href="artists_listings.php?s=' . (($display*($i - 1))) . '&np=' . $num_pages . '">'.$i.'</a> ';
				} else {
					echo $i.'';
				}
				echo'</td>';
			}
			
			// It it's not the last page, make a next button.
			if ($current_page != $num_pages) {
				echo '<td align="right"><a href="artists_listings.php?s=' . ($start + $display) . '&np=' . $num_pages . '"><img src="images/arrow_right.jpg" width="28" height="34" border="0"></a> </td></tr></table>';
			}
			
			echo '</td></tr></table><br /><br>';
			
		} // End of links section.
		
		// Table header.
		echo '<table align="left" cellspacing="0" cellpadding="0">';
		
				// Fetch and print all the records		
					while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
					echo '<tr><td align="left" class="small"><strong>',stripslashes($row['craft']), '</strong><br><br></td></tr></tr><tr><td align="left" class="small"><strong>',$row['name'],'</strong></td></tr><tr><td align="left" class="small">',$row['description'],'</td></tr>';
			if ($row['address']) {
			echo '<tr><td align="left" class="small">',$row['address'],'</td></tr>';
			}
			if ($row['tel']) {
			echo '<tr><td align="left" class="small">T: ',$row['tel'],'</td></tr>';
			}
			if ($row['mobile']) {
			echo '<tr><td align="left" class="small">M: ',$row['mobile'],'</td></tr>';
			}
			if ($row['email']) {
			echo '<tr><td align="left" class="small">E: <a href="mailto:',$row['email'],'" class="small_link">',$row['email'],'</a></td></tr>';
			}
			if ($row['website']) {
			echo'<tr><td align="left" class="small">W: <a href="http://',$row['website'],'" class="small_link" target="blank">',$row['website'],'</a></td></tr>';
			}
			echo'<tr><td><br></td></tr>';
		}

			
		echo '</table>'; // Close the table
		
		mysql_free_result ($result); // Free up the resources.
	
	} else { // If there are no registered artists.
		echo '<strong>There are currently no artists listings.</strong>';
	}
		
	mysql_close(); // Close the database connection.

include_once ('includes/footer.html'); // Require the HTML footer.
?>
Thank you in advance I hope I've made sense in my description of the problem

Posted: Thu Jul 13, 2006 6:47 am
by thomas777neo
Hi There

What I normally do is first get a list of the crafts, then loop though the list.

Then in that loop get the artists by each craft.

Posted: Thu Jul 13, 2006 8:39 am
by kathrynd
Hi

I tried something like that before but the only problem is if I loop through the crafts it will produce two results or more so it would keep repeating the title. I only want the craft to appear once no matter how many were in the database but all the artists associated with that craft to appear individually underneath like.

Calligraphy

John Smith

Smith John

Dave Stubbs


Pottery

Joe Bloggs

Posted: Thu Jul 13, 2006 8:52 am
by thomas777neo
You would just need to select a distinct set of crafts.

Code: Select all

SELECT DISTINCT crafts FROM artists ORDER BY crafts ASC
then loop through and get the artist by craft

The best solution would to have a crafts table

and in your artists table have a crafts_id which would represent the id of the craft in the craft table. So you would just need to select the records in the crafts table, then select the artists by each craft found.

But the example above should work fine, unless you have a large amount of data.

Posted: Thu Jul 13, 2006 10:04 am
by kathrynd
Hi

This might seem like a stupid question but i've got my while loop

Code: Select all

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	echo'<tr><td>',stripslashes($row['craft']), '</td></tr>';}
Which displays the only one craft title thanks to the Distinct select statment that you suggested, but how would I look through the artists to get them to display under each heading?

Thanks for your help

Posted: Thu Jul 13, 2006 1:10 pm
by Ollie Saunders
you would just need to select a distinct set of crafts.

Code: Select all

SELECT DISTINCT crafts FROM artists ORDER BY crafts ASC
Nope that won't work.
The most sensible solution is to use a separate database table.

Code: Select all

CREATE TABLE Craft (
    craftId            INT UNSIGNED NOT NULL AUTO_INCREMENT,
                       PRIMARY KEY(craftId),
    title              VARCHAR(255) NOT NULL
);

CREATE TABLE Artists (
    artistId           INT UNSIGNED NOT NULL AUTO_INCREMENT,
                       PRIMARY KEY(artistId),
    name               VARCHAR(255) NOT NULL,
    _craftId           INT UNSIGNED NOT NULL
);
And some data like this:

Code: Select all

+---------+------------------+
| craftId | title            |
+---------+------------------+
|       1 | Sewing           |
|       2 | Painting         |
|       3 | Drawing          |
+---------+------------------+
+----------+--------------------+----------+
| artistId | name               | _craftId |
+----------+--------------------+----------+
|        1 | John Smith         |        1 |
|        2 | Bob Smith          |        2 |
|        3 | Arnie Smith        |        2 |
|        4 | Maggie Jones       |        2 |
|        5 | Caterine Tate      |        3 |
+----------+--------------------+----------+
Combined with some PHP like this:

Code: Select all

$q = 'SELECT craftid AS id,title FROM Craft';
$result['craft'] = mysql_query($q);
while ($craft = mysql_fetch_object($result['craft'])) {
    $q = 'SELECT artistid,name FROM Artist WHERE _craftId = '.(int)$craft->id;
    $result['artist'] = mysql_query($q);
    echo '<h1>' . $craft->title . '</h1>';
    if (mysql_num_rows($result['artist'])) {
        echo '<ul>';
        while ($artist = mysql_fetch_object($result['artist'])) {
            echo '<li>' . $artist->name . '</li>';
        }
        echo '</ul>';
    }
}
Should produce this:

Code: Select all

<h1>Sewing</h1>
<ul>
    <li>John Smith</li>
</ul>
<h1>Painting</h1>';
<ul>
    <li>Bob Smith</li>
    <li>Arnie Smith</li>
    <li>Maggie Jones</li>
</ul>
<h1>Drawing</h1>
<ul>
    <li>Caterine Tate</li>
</ul>
and wouldn't that make you happy :)

Posted: Thu Jul 13, 2006 1:11 pm
by Ollie Saunders
Oh yeah I didn't test any of that PHP or SQL and also you should escape all your output with htmlentities(). I didn't bother there.

Posted: Thu Jul 13, 2006 4:47 pm
by RobertGonzalez
Two queries, one that selects a distinct list of crafts, one that selects all records. Read both queries into a result array, then loop the craft array and at each iteration, loop the all record array.

Code: Select all

<?php
$craft_array = array();
$artist_array = array();

$sql = 'SELECT DISTINCT(`craft`) AS craft FROM artists ORDER BY `craft` ASC';
if (!$result = mysql_query($sql))
{
	die('Could not fetch the craft list: ' . mysql_error());
}

while ($craft_array[] = mysql_fetch_array($result))
{
	$craft_count = count($craft_array);
}

$sql = 'SELECT `craft`, `name`, `description`, `address`, `tel`, `mobile`, `email`, `website` 
		FROM `artists` 
		ORDER BY `name` ASC';
if (!$result = mysql_query($sql))
{
	die('Could not fetch the artist list: ' . mysql_error());
}

while ($artist_array[] = mysql_fetch_array($result))
{
	$artist_count = count($artist_array);
}

// Run the loops, first craft, then artist
if ($craft_count)
{
	for ($i = 0; $i < $craft_count; $i++)
	{
		echo '<h1>' . $craft_array[$i]['craft'] . '</h1>';
		
		for ($j = 0; $j < $artist_count; $j++)
		{
			if ($artist_count[$j]['craft'] == $craft_array[$i]['craft'])
			{
				echo '<p>The artist is ' . $artist_count[$j]['name'] . '...</p>';
			}
		}
	}
}
?>

Posted: Thu Jul 13, 2006 5:23 pm
by thomas777neo
hence my other suggestion:
The best solution would to have a crafts table

and in your artists table have a crafts_id which would represent the id of the craft in the craft table. So you would just need to select the records in the crafts table, then select the artists by each craft found.

Posted: Thu Jul 13, 2006 5:50 pm
by RobertGonzalez
I was thinking as a solution to the current setup. I would agree that two tables are better than the one. But to help the original poster with their current setup, I would do it the way I posted (which can also be easily adapted to a second table).

Posted: Fri Jul 14, 2006 5:19 am
by kathrynd
There is a thank you for the last bit of help I asked for but now I've got a problem with my page numbers. I only want to display three results per page and it was working until I started messing round with the display of the artists details and craft titles. I think it's because I've got two new select statments where I'm calling my data from so it's ignoring the records code? Not sure how to rectify the problem
If anyone can help it would be greatly appreciated. Here's the code:

Code: Select all

<?php 

// Set the page title and include the HTML header.
$page_title = 'Staffordshire Open Studios - Artists Listings';
include_once ('includes/header_directory.html');

require_once ('../mysql_connect.php'); // Connect to the database.

// Number of records to show per page:
$display = 3;

// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
	$num_pages = $_GET['np'];
	} else { // Need to determine.
	$query = "SELECT * FROM artists ORDER by _craftId ASC"; // Standard query.
	$query_result = mysql_query ($query);
	
	$num_records = mysql_num_rows ($query_result);
	
	if ($num_records > $display) { // More than 1 page.
		$num_pages = ceil ($num_records/$display);
		} else {
			$num_pages = 1;
		}
	}
	
	// Determine where in the database to start returning results.
	if (isset($_GET['s'])) { // Already been determined.
		$start = $_GET['s'];
	} else {
		$start = 0;
	}
	
	// Make the query.
	$query = "SELECT * FROM artists ORDER by _craftId ASC LIMIT $start, $display";
	$result = mysql_query ($query); // Run the query.
	$num = mysql_num_rows ($result); // How many artists are there?
	
	if ($num > 0) { // If it ran OK, display the records.
	
		echo "<h1>ARTISTS LISTINGS</h1><span class=\"small\">Details of other artists operating in the County of Staffordshire.<br><br></span>";
		
					// Make the links to other pages, if necessary.
		if ($num_pages > 1) {
			
			echo '<table width="100%" align="right"><tr><td align="right">';
			// Determine what page the script is on.
			$current_page = ($start / $display) + 1;
			
			echo'<table align="right"><tr>';
			// If it's not the first page, make a Previous button.
			if ($current_page !=1) {
				echo '<td align="right"> <a href="artists_listings.php?s=' . ($start - $display) . '&np=' . $num_pages . '"><img src="images/arrow_left.jpg" width="28" height="34" border="0"></a> </td>';
			}
			
			// Make all the numbered pages.
			for ($i = 1; $i <= $num_pages; $i++) {
				echo '<td align="center">';
				if ($i != $current_page) {
					echo ' <a class="small_link_underline" href="artists_listings.php?s=' . (($display*($i - 1))) . '&np=' . $num_pages . '">'.$i.'</a> ';
				} else {
					echo $i.'';
				}
				echo'</td>';
			}
			
			// It it's not the last page, make a next button.
			if ($current_page != $num_pages) {
				echo '<td align="right"><a href="artists_listings.php?s=' . ($start + $display) . '&np=' . $num_pages . '"><img src="images/arrow_right.jpg" width="28" height="34" border="0"></a> </td></tr></table>';
			}
			
			echo '</td></tr></table><br /><br>';
			
		} // End of links section.
		
		
		$craft_query = 'select * from craft order by title';
		$craft_result = mysql_query($craft_query);
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		while ($craft = mysql_fetch_array($craft_result)) {
		
		$artists_query = "select * from artists where _craftId = '".$craft['craftId']."' order by name";
		$artists_result = mysql_query($artists_query);
		print'<h1>'.$craft['title'].'</h1>';
		print'<table>';
		if (mysql_num_rows($artists_result)) {
        while ($artists = mysql_fetch_array($artists_result)) {
            echo ''.$artists['name'].'<br>';
        }
    } 
			
			
		}
		
		

	} else { // If there are no registered artists.
		echo '<strong>There are currently no artists listings.</strong>';
	}
		
	mysql_close(); // Close the database connection.

include_once ('includes/footer.html'); // Require the HTML footer.
?>



Thank you all for your help I've got it working now and decided to go for the two tables the only reason I didn't in the first place was out of shear laziness. Thank you Everah for taking the time to look at a code which would fit my poor database structure. Just in case anyone needs here's my code I used in the end.

Code: Select all

$craft_query = 'select * from craft order by title';
		$craft_result = mysql_query($craft_query);
		
		while ($craft = mysql_fetch_array($craft_result)) {
		
		$artists_query = "select * from artists where _craftId = '".$craft['craftId']."' order by name";
		$artists_result = mysql_query($artists_query);
		print'<h1>'.$craft['title'].'</h1>';
		if (mysql_num_rows($artists_result)) {
			        echo '<ul>';
        while ($artists = mysql_fetch_array($artists_result)) {
            echo '<li>'.$artists['name'].'</li>';
        }
        echo '</ul>';
    }
Thanks again everyone.