Trying to loop through an array element possibly multi-value

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!

Moderator: General Moderators

Post Reply
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Trying to loop through an array element possibly multi-value

Post by mjmacarty »

I am running a query that references three tables in a MySQL database, and it almost works fine. The output is a list of books from a specific category and some of their attributes. The problem is some books have more than one author, and when this is the case I get more than one result for a particular title. I am wondering if anyone sees a way to loop through authors where there are more than one, each title displayed only once. This may be a job for the MySQL board... Thanks for looking.

Code: Select all

<?php

$category = $_POST['category'];



if ($category == 'none') {
	 echo "<p>Please select a category</p>
	 		<a href=\"search_test.htm\">Return to the search page</a>";
	 	exit;
	} 
	
echo "<h2>Results for $category Books:</h2>" ;
$conn = mysql_connect('localhost', 'root', '');

mysql_select_db('iz', $conn);	


$query = "SELECT books.*, author.author FROM books, author, category WHERE books.isbn = category.isbn AND books.isbn = author.isbn AND  category.category = '$category'";

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

$num_results = mysql_num_rows($result);

echo"<p>Books matching your search: $num_results</p>";

for ( $i=0; $i<$num_results; $i++){
  $row = mysql_fetch_array($result);
  
  $title = $row['title'];
  
  echo "<p><b>". ($i+1). ". Title: <i>$title</i></b>";
  echo '<br />&nbsp; &nbsp; Author: ' . $row['author'];
  echo '<br />&nbsp; &nbsp; ISBN: ' . $row['isbn'];
  echo '<br />&nbsp; &nbsp; Price: ' . $row['price'];
	}

echo "<h3 align=\"center\"><a href=\"search_test.htm\">Return to the search page</h3>";

?>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Trying to loop through an array element possibly multi-v

Post by Christopher »

Ususally something like:

Code: Select all

$query = "SELECT books.*, author.author FROM books, author, category WHERE books.isbn = category.isbn AND books.isbn = author.isbn AND  category.category = '$category' ORDER BY author.author";

Code: Select all

$last_author = '';
for ( $i=0; $i<$num_results; $i++){
  $row = mysql_fetch_array($result);
  
  if ($row['author'] == $last_author) {
     echo "<br/>";
  } else {
    echo '<br />&nbsp; &nbsp; Author: ' . $row['author'];
    $last_title = $row['title'];
  }
  echo "<p><b>". ($i+1). ". Title: <i>{$row['title']}</i></b>";
  echo '<br />&nbsp; &nbsp; ISBN: ' . $row['isbn'];
  echo '<br />&nbsp; &nbsp; Price: ' . $row['price'];
}
(#10850)
mjmacarty
Forum Commoner
Posts: 37
Joined: Tue Feb 21, 2006 3:20 pm

Post by mjmacarty »

Thanks for the help. I am not sure if I follow the logic though. When I implement the code it seems to jostle authors around, but does not associate more than one author with a book when appropriate. So I get the same number of results, but not necessarily the right author associated with a book.
Post Reply