PHP + Mysql for select negative value in database

fatepower

Hi i just have one question.
How can i select only the rows in database who has a negative value? ("-").
Im using a code that punish a user with a - minus points. Like UPDATE users SET points='-1' WHERE uid='$uid'. When the points turn into -1. I want only those to be selected from DB so i can add a code to auto ban them.

Now in use:

$autowarn=mysql_query("SELECT id, warns FROM users WHERE id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND disabled='no' AND awarn='no'");

Instead i want to select users that have a negative vaule at uploaded/downloaded even if they not have downloaded more than 5368709120.
Becuase if they have a "-" (negative) uploaded/downloaded they should be punished. Even if they not have downloaded>5368709120.

Hope you understand what i mean.

Cheerz

laserlight

Add a: AND points<0

fatepower

laserlight;10894631 wrote:
Add a: AND points<0

Yes i know that, but they still be affected of downloaded>5368709120.
Do i have to add multi querrys with some sort of IF.

Like:

$hejsan=mysql_query("SELECT * FROM users WHERE downloaded/uploaded<0.00");
if (mysql_num_rows($hejsan)>0) {
//run select for warns 1
$autowarn=mysql_query("SELECT id, warns FROM users WHERE id_level>=3 AND id_level<=6 AND disabled='no' AND awarn='no'"); 
}
else {
//run select for warns 2
$autowarn=mysql_query("SELECT id, warns FROM users WHERE id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND disabled='no' AND awarn='no'");
}

laserlight

In that case, use OR.

fatepower

laserlight;10894655 wrote:
In that case, use OR.

How? Can u please specifie mate? 🙂

laserlight

Something like this:

$autowarn = mysql_query("SELECT id, warns FROM users
    WHERE (id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND disabled='no' AND awarn='no') OR points<0");

fatepower

So i can use this instead of the long code i posted:

$autowarn = mysql_query("SELECT id, warns FROM users
    WHERE (id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND disabled='no' AND awarn='no') OR id_level>=3 AND id_level<=6 AND uploaded/downloaded<0 AND disabled='no' AND awarn='no'");

This will select the negative from DB? Only if they have a "-" in DB?

laserlight

fatepower wrote:
This will select the negative from DB? Only if they have a "-" in DB?

Um, what are you trying to do now? You changed my example such that the OR becomes useless since the expression on its left and that on its right is the same, and the points column is now excluded from the WHERE clause.

fatepower

Ahh no sorry. . . That should be included.

$autowarn = mysql_query("SELECT id, warns FROM users
    WHERE (id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND points<0 AND disabled='no' AND awarn='no') OR id_level>=3 AND id_level<=6 AND uploaded/downloaded<0 AND points<0 AND disabled='no' AND awarn='no'");

This two are the important:
uploaded/downloaded<0 AND points<0

laserlight

You need to be very clear on what is your criteria. At the moment, because these clauses are common:

id_level>=3 AND id_level<=6
points<0 AND disabled='no' AND awarn='no'

... we can simplify your SQL statement to:

SELECT id, warns FROM users
WHERE (id_level>=3 AND id_level<=6 AND disabled='no' AND awarn='no' AND points<0)
    AND ((downloaded>5368709120 AND uploaded/downloaded<0.50) OR uploaded/downloaded<0)

fatepower

Yes becuase it shall take the users who have uploaded/downloaded bellow 0.50. If they have points less than 0 they shall be warned. They should also be warned if they have uploaded/downloaded less than 0 (and then the downloaded>5368709120 shall be exluded) same with points. Seems like your privoded code is doing that mate.

Weedpacket

Could you clarify how uploaded/downloaded<0 could happen? The ratio could only be negative if exactly one of uploaded or downloaded is negative. From the numbers used I'm assuming that they measure how many bytes were uploaded or downloaded. Under what circumstances would either of these quantities be negative?

Incidentally, what data type are you using to store these numbers?

fatepower

Yes if they use a tool to fast upload and get more stats they will be punished. They will also be punished if they not are connected for X numbers of hours in a week after download. Anyway, if they get minus upload they get minus uploaded/downloaded (Ratio). This because they get punished as a H&R.

The uploaded/downloaded is getted negative but in the correct it is the uploaded who are negative. As the users not have downloaded more than 5368709120. They might just have downloaded a small amount but not keeped the seed up and then get punished and gotten the uploaded to negative value.

Anyway i need in the sql line so it select first as usual, and then also select the users who have a negative ratio (downloaded/uploaded).

You see in this orginal sql line:

$autowarn=mysql_query("SELECT id, warns FROM users WHERE id_level>=3 AND id_level<=6 AND downloaded>5368709120 AND uploaded/downloaded<0.50 AND disabled='no' AND awarn='no'");

That now, users with id_level bigger or is 3 AND id_level smaller or is 6 will be selected. Theey will also be included if they have downloaded more than 5368709120 AND have a ratio lower than 0.50. Now in here i want just that i should be the same, but the users who have a negative ratio will be included even if they not have downloaded>5368709120. Users who have example a ratio of 0.30 and only have downloaded like 2 GIG should be exluded. Then when they have reech the limit 5 GIG (5368709120) they shall be included to the Awarn system.

Data type is bigint(20) for both.