I need a sql query

[Sprite]

Hello,
I need a sql query:
- displays character which has more than 5 unban doll
Could someone help?

 

[tosse12]

First of all id of the unban doll?

 

[Sprite]

first of all id of the unban doll?

9019

 

[tosse12]

ok

SELECT player_id,name FROM player_items WHERE itemtype = 9019

sorry, forgot about the >=5

 

[DarkStoorM]

- displays character which has more than 5 unban doll

@up, not only one ;p

[O_O]Oops, too slow xD[/O_O]

 

[tosse12]

okey... were harder than I expected....why? because if they have that unban doll in like amu slot, then it won't be counted.
Or you could do like this (I sux at php code, so do not know how to get this to work but):

If player_id selected more or equal to 5 then display name 
else not

while using this sql query:

SELECT player_id,name FROM player_items WHERE itemtype = 9019

 

[DarkStoorM]

Dunno if it's doable, but give me few minutes

 

[Migxxx]

Is this for a script or personal use?

 

[tosse12]

I think he want this in his website, so he can see if ppls have more or equal to 5 unban dolls
This is going to become a script, if darkStoorM succeed with the php part

 

[Migxxx]

Here you go.

SELECT `player_id`, COUNT(*) FROM `player_items` WHERE `itemtype` = 2160 GROUP BY `player_id` HAVING COUNT(*) >= 5 ORDER BY COUNT(*) DESC

BTW second field is the quanty of unban scroll/doll etc... AH remember to change the itemtype to your itemid

 

[DarkStoorM]

<?php
$sql=mysql_query("SELECT player_id,name FROM player_items WHERE itemtype = 9019");
if (mysql_num_rows($sql) > 0){
	while($r = mysql_fetch_array($sql)){
		$player=$sql[0];
		$getDollNum=mysql_query("SELECT * FROM player_items WHERE player_id='$player' AND itemtype=9019");
		if(mysql_num_rows($getDollNum) > 5){
			echo $sql[0];}
	}
}
else{
	echo "None of players has an unban doll";}
?>

Can someone check this?
My XAMPP is messed and can't run it even when reinstalled.




NEVERMIND haha xD
This script is even more messed than I thought. Lol

@Mig~: great
Since it's on another page: D)

Here you go.

SELECT `player_id`, COUNT(*) FROM `player_items` WHERE `itemtype` = 2160 GROUP BY `player_id` HAVING COUNT(*) >= 5 ORDER BY COUNT(*) DESC

BTW second field is the quanty of unban scroll/doll etc... AH remember to change the itemtype to your itemid

Sry, I suck at PHP, but I tried ;P

 

[tosse12]

Okey! ALMOST! got it to work

<?php
$ots = POT::getInstance();  
$ots->connect(POT::DB_MYSQL, connection());  
$SQL = POT::getInstance()->getDBHandle();  
global $config;

$query = $SQL->query("SELECT `player_id`, COUNT(*) FROM `player_items` WHERE `itemtype` = 2160 GROUP BY `player_id` HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC");

echo 'All of the players of ' . $config['server_name'] .' with 5 or more unban dolls are listed below.<br/>
    <br />  
    <table width="100%">  
    <tbody>  
    <tr><td width="60%"><b>Name</b></td><td><b>Status</b></td></tr>';  

$aaa = 0;  
foreach($query as $info)  
    {  
    $player = $ots->createObject('Player');  
    $player->find($info['player_id']);  ;  
    echo '<tr>  
        <td width="60%"><a href="/index.php/character/view/'.$info['player_id'].'">'.$info['player_id'].'</a></td>  
        <td><font color="';  
    echo ($player->isOnline()) ? 'green' : 'red';  
    echo '">';  
    echo ($player->isOnline()) ? 'Online' : 'Offline';  
    echo '</font></td>  
    </tr>';  
    $aaa++;  
    }  
echo '</tbody>  
</table>';
?>

The problem is that the name isn't selected just the player id, will try when I come back home, if someone else haven't already solved this
Item count - A fast view how it looks like right now!

 

[Sprite]

Ok, THX REP++

 

[Migxxx]

Okey! ALMOST! got it to work

The problem is that the name isn't selected just the player id, will try when I come back home, if someone else haven't already solved this
Item count - A fast view how it looks like right now!

Ehhh, you could have said that early xP... anyway, here you go

<?php
	$ots = POT::getInstance();
	$ots->connect(POT::DB_MYSQL, connection());
	$SQL = POT::getInstance()->getDBHandle();
	global $config;

	$query = $SQL->query("SELECT `players`.`name`,`players`.`online`, COUNT(*) as `dolls` FROM `player_items`,`players` WHERE `player_items`.`itemtype` = 2160 AND `player_items`.`player_id` = `players`.`id` GROUP BY `players`.`name` HAVING COUNT(*) >= 5 ORDER BY COUNT(*) DESC");

	echo('All of the players of ' . $config['server_name'] .' with 5 or more unban dolls are listed below.<br /><br />
		<table width="100%"><tbody>
			<tr>
				<td width="60%"><b>Name</b></td>
				<td><b>Status</b></td>
				<td><b>Unban Dolls</b></td>
			</tr>');

	foreach($query as $info)
		echo('<tr>
				<td width="60%"><a href="/index.php/character/view/'.urlencode($info['name']).'">'.$info['name'].'</a></td>
				<td><font color="'.($info['online'] == 1 ? 'green">Online' : 'red">Offline').'</font></td>
				<td>'.$info['dolls'].'</td>
			</tr>');

	echo('</tbody></table>');
?>

Not tested.

 

[tosse12]

@up confirmed - See link: Itemcount

Edit:
Edited a bit so you can change item id wihout needing to change in the sql query!
btw! this maybe just work in Modern AAC!

<?php
    $ots = POT::getInstance();
    $ots->connect(POT::DB_MYSQL, connection());
    $SQL = POT::getInstance()->getDBHandle();
    global $config;
## ---Config---
	$item_id = 2160;
	$total_item = 2;
## ---End of config!---
    $query = $SQL->query("SELECT `players`.`name`,`players`.`online`, COUNT(*) as `dolls` FROM `player_items`,`players` WHERE `player_items`.`itemtype` = ".$item_id." AND `player_items`.`player_id` = `players`.`id` GROUP BY `players`.`name` HAVING COUNT(*) >=".$total_item." ORDER BY COUNT(*) DESC");

    echo('All of the players of ' . $config['server_name'] .' with '.$total_item.' or more unban dolls are listed below.<br /><br />
        <table width="100%"><tbody>
            <tr>
                <td width="60%"><b>Name</b></td>
                <td><b>Status</b></td>
                <td><b>Unban Dolls</b></td>
            </tr>');

    foreach($query as $info)
        echo('<tr>
                <td width="60%"><a href="/index.php/character/view/'.urlencode($info['name']).'">'.$info['name'].'</a></td>
                <td><font color="'.($info['online'] == 1 ? 'green">Online' : 'red">Offline').'</font></td>
                <td>'.$info['dolls'].'</td>
            </tr>');

    echo('</tbody></table>');
?>

EDIT 2:
Maybe ugly way to do this, but PHP isn't my "Thing" Just know some of the basics

EDIT 3:
Updated the script once again!