How to show these items only once in the while loop

[ketting00]

Hi guys,

I've have a table look like this:

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:

High
Medium
Low

How do I show only one loop in this query:

$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:

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:

Done
Medium
Low
High
Done
Low
High
Medium
Done

How do I show the result just like these:

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

[Cups]

I have not understood your main formatting question, but ...

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:

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:

"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?

[ketting00]

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.

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

[guido2004]

The query you posted will return only 1 row, and action will always be 0 (since you specified that in the WHERE clause).

[Cups]

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.

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.

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:

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:

Then build a query which does the equivalent of:

"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?

[ketting00]

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:

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

[Cups]

Try:

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