Page 1 of 1

How to write the SQL code.....

Posted: Wed Dec 08, 2004 6:06 am
by cookie
How can I modify the SQL code if I don't want to display duplicate records, such as below:

year_______month_______book name
--------------------------------------------------------
2002_________3_________book1 book2 book3
2002_________6_________book4 book5
2002_________8_________book6 book7 book8

Instead of displaying everything like this:

year_______month_______book name
--------------------------------------------------------
2002_________3_________book1
2002_________3_________book2
2002_________3_________book3
2002_________6_________book4
2002_________6_________book5
2002_________8_________book6
2002_________8_________book7
2002_________8_________book8

the existing code is:

Code: Select all

$query_rslist = "SELECT ID, year, month, book_name
FROM tbbook ORDER BY year ASC month ASC";
$query_limit_rslist = sprintf("%s LIMIT %d, %d", $query_rslist, $startRow_rslist, $maxRows_rslist);
Please help!!

Posted: Wed Dec 08, 2004 6:32 am
by GhostXL
I don't think u can do this by changing the query allone, u allso need to alter the php code that takes care of generating the html to display the result.
Can u post the entire code here?

Posted: Wed Dec 08, 2004 7:01 am
by timvw
actually you are willing to write table rows with each row a certain year - month combination. the query is right, just change your code a little ;)

Code: Select all

echo "<table>";
$row = @mysql_fetch_assoc($result);
while ($row)
{
  $current_year = $row['year']; 
  $current_month = $row['month'];
  
  echo "<tr><td>{$row['year']}</td><td>{$row['month']}</td><td>";

  while ($row && $row['year'] == $current_year && $row['month'] == $current_month)
  {
    echo "{$row['bookname']}";
     $row = @mysql_fetch_assoc($result);
  }

   echo "</td></tr>";
}
echo "</table>";

Posted: Wed Dec 08, 2004 10:07 am
by GhostXL
Yeah that's what i mean..........

Posted: Thu Dec 09, 2004 7:22 am
by cookie
I got it . Thanks a lot. :D

Posted: Fri Dec 10, 2004 9:10 am
by Steveo31
What about DISTINCT? I thought that's what it was for, situations like this...

Posted: Fri Dec 10, 2004 9:31 am
by GhostXL
I thought about DISTICT too but, the code above seems to work without making a very dificult query..., but i think u could do it using DISTICT too, still u have to ajust your "dislay-code". So the solution above seems simpler.