SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    How to show these items only once in the while loop

    Hi guys,

    I've have a table look like this:
    Code:
    id     receiver     sender    urgency_level     action
    1      Adam        John       Medium              0
    2      Adam        Doe        High                   0
    3      Adam        Smith     Low                    0
    And the values of the urgency_level are:
    Code:
    High
    Medium
    Low
    How do I show only one loop in this query:
    Code:
    $receiver = 'Adam';
    $sender   = 'xxx';
    "SELECT * FROM urgent_message WHERE receiver = '$receiver' AND sender = '$sender' AND action = '0' GROUP BY 
    
    receiver ORDER BY id LIMIT 3"
    Here are the codes of my loops:
    Code:
    while($row = mysql_fetch_array()) {
         if($row[' urgency_level'] == 'High' && $row['action'] >= 1) {
              echo "Done.";
         }elseif($row[' urgency_level'] == 'High' && $row['action'] == 0) {
              echo "High";
         }
         if($row[' urgency_level'] == 'Medium' && $row['action'] >= 1) {
              echo "Done.";
         }elseif($row[' urgency_level'] == 'Medium' && $row['action'] == 0) {
              echo "Medium";
         }
         if($row[' urgency_level'] == 'Low' && $row['action'] >= 1) {
              echo "Done.";
         }elseif($row[' urgency_level'] == 'Low' && $row['action'] == 0) {
              echo "Low";
         }
    }
    If the action is taken the result looks like this:
    Code:
    Done
    Medium
    Low
    High
    Done
    Low
    High
    Medium
    Done
    How do I show the result just like these:
    Code:
    Done     or     Done        or     High        or      High     or    Not repeat too many loops
    Done             Medium            Medium            Done
    Done             Done                Low                  Done
    Additionaly, I also want to count High alert messages Adam received from John too, but that's just in case.

    Thanks

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I have not understood your main formatting question, but ...


    PHP Code:
    while($row mysql_fetch_array()) {
         if(
    $row[' urgency_level'] == 'High' && $row['action'] >= 1) {
              echo 
    "Done.";
         }elseif(
    $row[' urgency_level'] == 'High' && $row['action'] == 0) {
              echo 
    "High";
         }
         if(
    $row[' urgency_level'] == 'Medium' && $row['action'] >= 1) {
              echo 
    "Done.";
         }elseif(
    $row[' urgency_level'] == 'Medium' && $row['action'] == 0) {
              echo 
    "Medium";
         }
         if(
    $row[' urgency_level'] == 'Low' && $row['action'] >= 1) {
              echo 
    "Done.";
         }elseif(
    $row[' urgency_level'] == 'Low' && $row['action'] == 0) {
              echo 
    "Low";
         }

    That could be simplified with:

    PHP Code:
    while($row mysql_fetch_array()) {

         if(
    $row['action'] >= 1) {
              echo 
    "Done.";
         }else(
              echo 
    $row['urgency_level'];
         }

    Additionally, I also want to count High alert messages Adam received from John too, but that's just in case.
    Then build a query which does the equivalent of:
    Code:
    "SELECT count(id) as count FROM urgent_message 
    WHERE receiver = 'Adam' 
    AND sender = 'John' 
    AND urgency_level = 'High'";
    This brings back all messages of course, whether they are flagged 0 or 1 accessed as $row['count']

    This sounds as if it could potentially contain a lot of data, have you set some INDEXES on this table?

  3. #3
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thank for response.

    I want to get specific values, which are High, Medium, Low, for each receiver who has taken messages sent form a sender. If there is no message from this sender there will be another message displayed. The code maybe confusing, because I not yet know how to code. I don't want to create 3 tables for each level of urgency needs. I have no idea, but have to figure it out.

    Quote Originally Posted by Cups View Post
    This sounds as if it could potentially contain a lot of data, have you set some INDEXES on this table?
    I don't know what this mean?

    Thanks

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    The query you posted will return only 1 row, and action will always be 0 (since you specified that in the WHERE clause).

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Try: how to set up indexes in mysql.

    If you still do not understand then come back here or ask on the SQL forum, but as you are a new coder you can probably afford to leave this issue for a while. Sorry to muddy the waters.

    Quote Originally Posted by guido2004
    The query you posted will return only 1 row, and action will always be 0 (since you specified that in the WHERE clause).
    Oh yeah, I hadn't really noticed that -- pretty obvious really. I think I probably imagined that was one of many different queries the OP would be putting in.

    Quote Originally Posted by ketting00
    I don't want to create 3 tables for each level of urgency needs.
    You don't need to, there is basically nothing wrong with your table as far as I can see.

    I was a bit confused by what you were getting vs what you wanted to display in Post #1 (which looks like you want some kind of table).

    Why not spell out one question at a time, and we'll help you devise the sql query to get that, and then if you need it, some strategies for how to have PHP build those SQL queries.

    I mean you asked this:

    Quote Originally Posted by ketting00
    Additionally, I also want to count High alert messages Adam received from John too, but that's just in case.
    Which was clear enough and I replied:

    Quote Originally Posted by Cups
    Then build a query which does the equivalent of:
    Code:
    "SELECT count(id) as count FROM urgent_message 
    WHERE receiver = 'Adam' 
    AND sender = 'John' 
    AND urgency_level = 'High'";
    ... but did you understand that? Did you try it, did it work? Did it behave as you expected?

  6. #6
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Your code works great.
    But it isn't what I expected.
    I want to show how many urgency request John has sent to Adam. The result would look like this:
    Code:
    High       10
    Medium    5
    Low        0
    In the code above the result 0 would not show up and if I want it show up it would run too many loops.

    Anyway, I wrote three separate queries for each type of urgency request. It's not effective though, but I get what I want.

    Thanks

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Try:

    Code MySQL:
    SELECT urgency_level, count(id) as count FROM urgent_message 
    WHERE receiver = 'Adam' 
    AND sender = 'John' 
    GROUP BY urgency_level;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •