EternalSorrow Posted November 20, 2008 Share Posted November 20, 2008 I had previously asked a question about DISTINCT fields in this thread. Now, however, I have an even bigger challenge in the form of multiple tables being accessed with the same, and different, fields. The task I'm trying to perform is showing all information for stories, whose information is archived in three separate tables: rating, stories, and fanfiction. The trick is I need to make distinct the field chapter_id from the fanfiction table because it is returning all results found, when I only want a single return. Below is the code so far (and here is a view of the current page). <?php if (!is_numeric($_GET["series"]) && !empty($_GET["series"]) && $_GET["series"]!="") { $series = $_GET["series"]; } mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query="SELECT a.*, b.rating, c.chapter_id, c.date from stories a, rating b, fanfiction c WHERE a.series = '$series' AND a.rating_id=b.rating_id AND a.story = c.story ORDER by a.story asc, c.chapter_id desc "; $result = mysql_query( $query ) or die(mysql_error()); // Insert Code A Here // dynamic navigation variables $screen = $_GET['screen']; $PHP_SELF = $_SERVER['PHP_SELF']; $rows_per_page=8; $total_records=mysql_num_rows($result); $pages = ceil($total_records / $rows_per_page); $last = $pages -1; if (!isset($screen)) $screen=0; $start = $screen * $rows_per_page; $query .= "LIMIT $start, $rows_per_page"; $result= mysql_query($query) or die ("Could not execute query : $query." . mysql_error()); $i = 1; echo '<div class="head">'.$series.'</div>'; while ($row = mysql_fetch_array($result)) { extract($row); $contents_here = '<li><div class="font"><a href="fanfiction.php?story='.$story.'" >'.$story.'</A></div> '.$summary.' <div class="fan">Universe: '.$universe.' | Rating: '.$rating.' | Status: '.$status.' | Chapters: '.$chapter_id.' | Pairing: '.$pairing.' | Genre: '.$genre.' | Updated: '.$date.'</div>'; if ($i==1) { echo '<div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</ul></div> Link to comment https://forums.phpfreaks.com/topic/133440-another-distinct-field-problem/ Share on other sites More sharing options...
EternalSorrow Posted November 20, 2008 Author Share Posted November 20, 2008 A bump, I'm afraid Link to comment https://forums.phpfreaks.com/topic/133440-another-distinct-field-problem/#findComment-694348 Share on other sites More sharing options...
EternalSorrow Posted November 20, 2008 Author Share Posted November 20, 2008 Another bump for this thread. I also have an update on the code (since the modify button has vanished on my first and second posts): <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <?php include("banner.php"); ?> <ul class="fanfiction"><?php if (!is_numeric($_GET["series"]) && !empty($_GET["series"]) && $_GET["series"]!="") { $series = $_GET["series"]; } mysql_connect(localhost,user,pw); @mysql_select_db(db) or die( "Unable to select database"); $query="SELECT a.*, b.rating from stories a, rating b WHERE `series` = '$series' AND a.rating_id=b.rating_id ORDER BY story asc "; $result = mysql_query( $query ) or die(mysql_error()); // Insert Code A Here $screen = $_GET['screen']; $PHP_SELF = $_SERVER['PHP_SELF']; $rows_per_page=8; $total_records=mysql_num_rows($result); $pages = ceil($total_records / $rows_per_page); $last = $pages -1; if (!isset($screen)) $screen=0; $start = $screen * $rows_per_page; $query .= "LIMIT $start, $rows_per_page"; $result= mysql_query($query) or die ("Could not execute query : $query." . mysql_error()); $i = 1; echo '<div class="head">'.$series.'</div>'; while ($row = mysql_fetch_array($result)) { extract($row); $select_story = mysql_query("SELECT DISTINCT chapter_id, date FROM fanfiction WHERE 'a.series'='$series' ORDER by chapter_id desc ") or die (mysql_error()); $contents_here = '<li><div class="font"><a href="fanfiction.php?story='.$story.'" >'.$story.'</A></div> '.$summary.' <div class="fan">Universe: '.$universe.' | Rating: '.$rating.' | Status: '.$status.' | Chapters: '.$chapter_id.' | Pairing: '.$pairing.' | Genre: '.$genre.' | Updated: '.$date.'</div>'; if ($i==1) { echo '<div class="even">'.$contents_here.'</div>'; } else if ($i==0) { echo '<div class="odd">'.$contents_here.'</div>'; } $i++; $i=$i%2; } echo '</ul></div> Link to comment https://forums.phpfreaks.com/topic/133440-another-distinct-field-problem/#findComment-694618 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.