PHP - Select Most After Subtraction?

[FullmetalPride]

Hey. I've been trying to select the user with the most CFHs, but the only problem is that a user could just send abusive ones. So I want to select the user with the most CFHs AFTER subtracting the total of abusive ones. Thanks.

 

[Caustik]

I modeled your problem, here's what I've come up with.
I made a table called 'habbo_cfh' and designed it like so

I then used this query to get the user's abusive cfh requests.

SELECT COUNT(habbo_cfh.habbo_id) from habbo_cfh WHERE habbo_cfh.abusive = 0 AND habbo_cfh.habbo_id = 1

(In this case, 0 meant they were abusive, 1 did not - change it if you need to)

Result:
http://i.imgur.com/Wrd22.png

 

[FullmetalPride]

I modeled your problem, here's what I've come up with.
I made a table called 'habbo_cfh' and designed it like so

I then used this query to get the user's abusive cfh requests.

SELECT COUNT(habbo_cfh.habbo_id) from habbo_cfh WHERE habbo_cfh.abusive = 0 AND habbo_cfh.habbo_id = 1

(In this case, 0 meant they were abusive, 1 did not - change it if you need to)

Result:
http://i.imgur.com/Wrd22.png

Ahh, brilliant work, the only problem is that the users_cfh table is modeled by the count of the users CFHs, which include abusive ones, and a column displaying the total abusive ones. What needs to happen is I need to subtract the abusive CFHs from their total CFHs, so I may select the user who has the most non-abusive CFHs.

 

[Caustik]

Ahh, brilliant work, the only problem is that the users_cfh table is modeled by the count of the users CFHs, which include abusive ones, and a column displaying the total abusive ones. What needs to happen is I need to subtract the abusive CFHs from their total CFHs, so I may select the user who has the most non-abusive CFHs.

Is there no column to determine whether the cfh is abusive or not?

 

[FullmetalPride]

Is there no column to determine whether the cfh is abusive or not?

I'm selecting from User_info, but I guess I could just select from moderation_tickets.

Also, general question, what does the column 'score' do? I might be able to implement that.

 

[Caustik]

I'm selecting from User_info, but I guess I could just select from moderation_tickets.

Could you copy over the structures of the tables here?

 

[FullmetalPride]

Could you copy over the structures of the tables here?

 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `status` enum('open','picked','resolved','abusive','invalid','deleted') NOT NULL DEFAULT 'open',
  `sender_id` int(10) unsigned NOT NULL,
  `reported_id` int(10) unsigned NOT NULL,
  `moderator_id` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `room_id` int(10) unsigned NOT NULL,
  `room_name` varchar(100) NOT NULL,
  `timestamp` double NOT NULL

Can you help me find the best way to select the user with the most helpful CFHs with this system? It's getting a might dificult. Here's the code I'm using (most of it is still raw from the previous version, where I selected from user_info, and the code is messy because earlier it was being a little witch and telling me 'Resource ID [X]' and that fixed it.)

<?php
	
	
	$result = mysql_query("SELECT sender_id FROM moderation_tickets WHERE status = 'Resolved' ORDER BY sender_id DESC"); 
	$resprep = mysql_fetch_row($result); 
	$resprep = $resprep[0];
	$result2 = mysql_query("SELECT username FROM users WHERE id = $resprep");
	$username = mysql_fetch_row($result2);
	$username = $username[0];
	$lookprep = mysql_query("SELECT look FROM users WHERE username = $username");
	$look = mysql_fetch_row($lookprep);
	$look = $look[0];
	echo $username;
	echo "<img alt='%habboName%' src='http://habbo.nl/habbo-imaging/avatarimage?figure=$look&action=wav&gesture=sad' />";
?>

 

[Caustik]

I'm assuming that resolved = helpful (in the sense it isn't a phony request).
Based on that, I'd use a join query to get the user and the "helpful" cfh. Then, based on that, I'd order the results by how many helpful cfh's have come from a certain user.
(Sorry if I was vague; not at my laptop atm)

 

[FullmetalPride]

I'm assuming that resolved = helpful (in the sense it isn't a phony request).
Based on that, I'd use a join query to get the user and the "helpful" cfh. Then, based on that, I'd order the results by how many helpful cfh's have come from a certain user.
(Sorry if I was vague; not at my laptop atm)

That's fine. I don't know anything about Join statements yet, not much about MySQL except how to Insert, Update, and Select!

 

[Jordan]

I think you'd want something like

SELECT look, COUNT(id) FROM cfh_table LEFT JOIN users ON users.id = cfh_table.user_id WHERE user_id = (SELECT user_id FROM cfh_table WHERE status NOT 'abusive') ORDER BY COUNT(user_id) LIMIT 1;

Or you could look into using count distinct might work. Ugly code make sure to edit table and field names

 

[Jordan]

Any update, did the query work?

Just noticed it should be
WHERE user_id IN (

 

[FullmetalPride]

Any update, did the query work?

Just noticed it should be
WHERE user_id IN (

Sorry I've been at school. Going to try this!

E D I T:

Where do I place that code in my current statement? I posted it above.

 

[Jordan]

Probably needs some tweaks (the order by is most likely wrong)

 

[Caustik]

Sorry I've been at school. Going to try this!

E D I T:

Where do I place that code in my current statement? I posted it above.

Test the query against the database first

 

[FullmetalPride]

Test the query against the database first

Tweaked it, ran it, subquery returned more than 1 result.

SELECT look, COUNT(sender_id) FROM moderation_tickets LEFT JOIN users ON users.id = sender_id WHERE sender_id = (SELECT sender_id FROM moderation_tickets WHERE status = 'abusive') ORDER BY COUNT(sender_id)

I tried removing the limit 1, changed it to 2, but none of that worked.

 

[Jordan]

Can you post the results?

 

[FullmetalPride]

Subquery returned multiple rows.

 

[Jordan]

Subquery returned multiple rows.

Change "sender_id =" to "sender_id IN" when I get some time I'll write up a better way of doing it

Something like this may be better try them in navicat or PHPMyAdmin first

SELECT look, username, COUNT(id) FROM cfh_table LEFT JOIN users ON cfh_table.user_id = users.id WHERE status NOT 'abusive' GROUP BY (user_id) LIMIT 1