How to write the SQL code.....

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cookie
Forum Newbie
Posts: 3
Joined: Wed Dec 08, 2004 5:06 am

How to write the SQL code.....

Post 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!!
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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>";
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post by GhostXL »

Yeah that's what i mean..........
cookie
Forum Newbie
Posts: 3
Joined: Wed Dec 08, 2004 5:06 am

Post by cookie »

I got it . Thanks a lot. :D
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

What about DISTINCT? I thought that's what it was for, situations like this...
User avatar
GhostXL
Forum Newbie
Posts: 12
Joined: Sun Nov 28, 2004 10:58 am
Location: Netherlands

Post 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.
Post Reply