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!
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.
<?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
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.
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.
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?
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
);
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.
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.
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).
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:
<?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.
$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>';
}