12 Replies - 2492 Views - Last Post: 19 February 2013 - 03:56 AM

#1 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Getting table data based on previous key

Posted 16 February 2013 - 12:15 PM

Need some help finding out the best way to get results from this kind of query efficiently, I have a table of messages and this table of messages has comments for each of them. I need to retrieve each message and each subsequent comment for the message. At first I was thinking to grab all the messages and their PK, then within a loop use this to get the comments, but I am wondering if there is a more efficient way to do that. I want each message to be displayed along with all subsequent comments for that message. Message1 -> all comments Message2 -> all comments. This is an example of how I wanted the output to be like:

eg.    Work Site Maintenance.  //Message
               At 1pm          //comments
               At 2pm          //comments
               At 3pm          //comments
         Work Site Offline.
               Foundation work
               Layng blocks
               Placing steel


My current query to get the list of messages but not comments for each

SELECT MS.status_id,
       MS.member_id,
       MS.status_text,
      MS.status_time
    FROM message_status MS
    WHERE MS.member_id = memberId //variable
    ORDER BY MS.status_id DESC 
    LIMIT 20


**message_status**
status_id PK
member_id
status_text
status_time

**comments_status**
status_id FK
member_id FK
comm_text
comm_time


Is This A Good Question/Topic? 0
  • +

Replies To: Getting table data based on previous key

#2 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: Getting table data based on previous key

Posted 16 February 2013 - 12:46 PM

You need to study joins. An example:

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

Was This Post Helpful? 1
  • +
  • -

#3 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 16 February 2013 - 01:18 PM

Thanks for the reply andrewsw, but would a join print out each comment for the message titles? I tried a join and it printed one message title for each comment it had causing dozens of duplicates being printed.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: Getting table data based on previous key

Posted 16 February 2013 - 01:25 PM

View Postkabuto178, on 16 February 2013 - 01:18 PM, said:

Thanks for the reply andrewsw, but would a join print out each comment for the message titles? I tried a join and it printed one message title for each comment it had causing dozens of duplicates being printed.

MySql will always return the duplicates, you need to use your server-side code to only display the first occurrence of the title.
Was This Post Helpful? 0
  • +
  • -

#5 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 16 February 2013 - 01:39 PM

So it can be done with one query, then as you said I use PHP in my case to provide the right output?
This is the query I have now
SELECT MS.status_id,
   MS.member_id,
   MS.status_text,
   MS.status_time,
   CS.comm_text,
   CS.status_id,
   CS.comm_time
FROM message_status MS
  left join comments_status CS on CS.status_id = MS.status_id
WHERE MS.member_id = memberId //variable
ORDER BY MS.status_id DESC, CS.comm_time DESC
LIMIT 20

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: Getting table data based on previous key

Posted 16 February 2013 - 01:46 PM

$previous = "";
loop through all rows
    if $title != $previous
        print the new title
    else
        don't print the title - leave a gap
    print everything else
    $previous = title

This post has been edited by andrewsw: 16 February 2013 - 01:47 PM

Was This Post Helpful? 1
  • +
  • -

#7 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 16 February 2013 - 01:52 PM

Ahh, I see. I had just set all the records to print out in the usual while($row) loop system. I will edit it and report back?
Was This Post Helpful? 0
  • +
  • -

#8 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 17 February 2013 - 04:32 AM

Ok made some some progress, although this method slightly messed up my design but its better than before. :smartass:
Was This Post Helpful? 0
  • +
  • -

#9 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 17 February 2013 - 07:10 AM

Thanks again for the help, I appreciate it :)
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: Getting table data based on previous key

Posted 17 February 2013 - 07:10 AM

View Postkabuto178, on 17 February 2013 - 07:10 AM, said:

Thanks again for the help, I appreciate it :)/>

No problem :)
Was This Post Helpful? 0
  • +
  • -

#11 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 18 February 2013 - 04:53 PM

would setting values to an array within a loop, then use possibly a foreach to print out these in a formatted way be wasting resources?
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: Getting table data based on previous key

Posted 18 February 2013 - 05:14 PM

View Postkabuto178, on 18 February 2013 - 04:53 PM, said:

would setting values to an array within a loop, then use possibly a foreach to print out these in a formatted way be wasting resources?

Do you mean collecting all the row-data into an array of strings and then looping through this to create your page-output? It is wasteful if you suddenly have two loops, but it is more to do with the fact that you shouldn't generally need to do this.

An alternative would be to collect the various bits and pieces in some string variables (or an array) and then echo your output at the bottom of the current loop, before it loops to the next row. Or it might be just a single (large) string that you build.

I generally resort to HEREDOCs when inserting blocks of HTML - or includes of course - it is easier to see what your intended output should be:

            echo <<< TABLE2
    </td>
    </tr>
    <tr>
        <td class="justicon">$updown_image</td>
        <td id="bgText{$rowh['blog_id']}" colspan="2">$html_blog</td>
    </tr>
    <tr class="coded">
        <td></td>
        <td id="bgCode{$rowh['blog_id']}" colspan="2">$html_code</td>
    </tr>
    <tr class="thecode"><td></td><td colspan="2"><!-- for the coded display -->
TABLE2;

This post has been edited by andrewsw: 18 February 2013 - 05:15 PM

Was This Post Helpful? 0
  • +
  • -

#13 kabuto178  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 130
  • Joined: 29-January 11

Re: Getting table data based on previous key

Posted 19 February 2013 - 03:56 AM

Ahh I got you. Thanks again.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1