Problem sorting/grouping/displaying MySQL data

Hi everybody,

I have a rather (hopefully ) simple question, but I can't figure this out even though I've worked on it way over an hour.
I'm pretty good at PHP and MySQL (by the way the data is ok), but like I said I just can't figure this out.
I've tried for's,while's, and other things but no luck.

We have a newsletter table.
Each issue of the newsletter has 1 or more tips, and each tip is stored in a separate row.

Here's the sample table:

id,newsletter_id,title,content
-----------------------------------------------------
1,1,item 1,tip1
2,1,item 2,tip2
3,2,item 1,tip1
4,3,item 1,tip1
5,3,item 2,tip2
6,3,item 3,tip3
7,3,item 4,tip4
..........
-----------------------------------------------------

I would like to display the page like this:

Newsletter 1
--item 1
--item 2

Newsletter 2
--item 1

Newsletter 3
--item 1
--item 2
--item 3
--item 4
..........

Sample code:


Can I please get some help?!?!
I'm about to go crazy!
Thanks a lot!

Well, a couple of tips. Hope they help.

Tip 1:

Why don't you store the data in two tables?
Table 1 (newsletters)

newsletter_id, id

Table 2 (news_items)

newsletter_id, title, content

PHP Code:

 $query = "SELECT id, newsletter_id FROM newsletters ORDER BY newsletter_id;"; 
$result = mysql_query($query) or die ("error");
 
while ($myrow = mysql_fetch_array($result) {
  $news_temp = $myrow["newsletter_id"];
  echo $news_temp;
 
  $query2 = "SELECT title, content FROM news_items WHERE newsletter_id='$news_temp';";
  $result2 = mysql_query($query2) or die ("error");
  
  while ($myrow2 = mysql_fetch_array($result2) {
    $title_temp = $myrow2["title"];
    $content_temp = $myrow2["content"];
 
    echo "--$title_temp";
  }
} 


Yeah it's f-ugly, but hey, it works. Well maybe not. I didn't test this code but I think you understand the concept and I've used this concept before.

Tip 2:

If you're intent on using one table try this.

PHP Code:

 $query = "SELECT DISTINCT newsletter_id FROM newsletters ORDER BY newsletter_id;"; 
$result = mysql_query($query) or die ("error");
 
while ($myrow = mysql_fetch_array($result) {
  $news_temp = $myrow["newsletter_id"];
  
  $query2 = "SELECT title, content FROM newsletters WHERE newsletter_id='$news_temp';";
  $result2 = mysql_query($query2) or die ("error");
 
  while ($myrow2 = mysql_fetch_array($result2) {
    $title_temp = $myrow2["title"];
    $content_temp = $myrow2["content"];
 
    echo "--$title_temp";
  }
} 


Let me know how you make out.

tobycloud,
I tried to use your grouping code listed above but cannot seem to get it to work without parse errors. I modified it a littel to match what we need. Please take a look and tell me what I am missing.


Thank you ahead of time.

G

Here's the updated code I use.
Not very efficient but it works.

Table "tips":
id,newsletter_id,title,content

Each newsletter tip is stored in the tips table.
There's a "newsletter_id" field because not all the tips in the table are newsletters.

Confusing?
Yes - but here's the finished product (similar not exact code): http://www.acctadv.com/newsletters.php



Basically, the total number of newsletters are retrieved, then a reverse "for" loop gets the specific newsletter and prints it out.

Hi. I am pondering, why not just use the "group by" function in the mysql_query(); ??

- Pheifel

Pheifel,
I was doing some reading and wondered the same thing, but I am just a novice at php.