- Joined
- Nov 6, 2010
- Messages
- 1,560
- Reaction score
- 393
SELECT COUNT(habbo_cfh.habbo_id) from habbo_cfh WHERE habbo_cfh.abusive = 0 AND habbo_cfh.habbo_id = 1
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.
(In this case, 0 meant they were abusive, 1 did not - change it if you need to)Code:SELECT COUNT(habbo_cfh.habbo_id) from habbo_cfh WHERE habbo_cfh.abusive = 0 AND habbo_cfh.habbo_id = 1
Result:
You must be registered to see links
Is there no column to determine whether the cfh is abusive or not?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.
I'm selecting from User_info, but I guess I could just select from moderation_tickets.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.
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
<?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' />";
?>
That's fine. I don't know anything about Join statements yet, not much about MySQL except how to Insert, Update, and Select!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)
Any update, did the query work?
Just noticed it should be
WHERE user_id IN (
Test the query against the database firstSorry 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
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)
Subquery returned multiple rows.