How to reduce user priority field in MySQL when joining two tables left?

Question

Let's say I have a table emailsand priority:

priority table fields:

Fields: uid, priority

email table fields:

Fields: id, uid, content

Each user has many emails. In a script, I receive user emails based on their priority (by joining two tables). But the problem arises when I receive the packet, and the user with the highest priority has 1000 mail messages in the queue. The second user has priority 99. I must send no more than 2 letters from this user with the highest priority, because on the third record of the first user, his priority will be 97 and lower than the second user.

How to solve this problem? I program in PHP, so if the solution is better resolved in PHP, tell me how to do it.

This is the main request:

select * from emails e left join by priority p on e.uid=p.uid order by priority DESC

---

EDIT1: Email
Data Table:

id     uid     email_content

1      321     some example data
2      434     some other example data
3      321     another from first user
4      321     again another from 321

And the data in the priority table is shown below:

id      uid      priority

1       321      100
2       434      99

A user with a uidvalue of 321 has the highest priority at first, but not for all of his emails. When the first email is sent, the priority should be 99, and after the second priority, the email will be 98. Now the third email should not be sent, and the email address of the user with uid 434 has the highest priority.

Since I already received 10 entries, the updated priority is not retrieved!

Answer 1

. EMail, .

<?php
$mysqli = new mysqli("127.0.0.1", "root","", "stackoverflow");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}


$query = "SELECT e.id, email_content,p.uid from emails e INNER JOIN priority p ON e.uid=p.uid ORDER BY priority DESC LIMIT 1";


$updateQuery = "UPDATE priority SET priority = priority - 1 WHERE uid = ?";

while (true) {
    $result = $mysqli->query($query);
    if ($result->num_rows == 0) {
        break;
    }
    $row = $result->fetch_row();
    // @TODO send the mail
    // delete the email entry
    $mysqli->query("DELETE FROM emails WHERE id = $row[0]");
    // NOW update the PRIORITY
    $mysqli->query("UPDATE priority SET priority = priority - 1 WHERE uid = $row[2]");
}