X item detector PHP+SQL

[Kido]

I was talking with a friend and he said that it is possible to detect how many cards or X item currently exist in the server by using the SQL DB. He managed to show how many players have X item and at wich ID that item corresponds to.

 

Here is anexample:

 

Card: High Priest card (4363) - Players that owns this card: 1 - 

 

Existentes = how many of that cards exists on the whole server

 

so, how can i get that information?

 

it's all about php and mysql but i have tryid for hours and can not find how D:!

[Sunzuke]

$sql_ip = "".$_SERVER['SERVER_ADDR']."";
$sql_user = "ragnarok";
$sql_pass = "ragnarok";
$sql_db = array(
'ragnarok'
);

//Warning: Be sure not to use POST variables inside the query because of SQL Injection
$itemid = 4363;
$string = "WHERE `nameid` LIKE '".$itemid."' OR `card0` LIKE '".$itemid."' OR `card1` LIKE '".$itemid."' OR `card2` LIKE '".$itemid."' OR `card3` LIKE '".$itemid."'";

$sql = mysql_connect($sql_ip, $sql_user, $sql_pass);
if(!$sql) die("Failed to connect to $sql_ip SQL Server.<br>");
$query = mysql_query("SHOW DATABASES");
$dbss = array();
$db = array();
while($row = mysql_fetch_array($query)) $dbss[] = "".$row[0]."";
foreach($sql_db as $dbs) {
	if(!in_array($dbs, $dbss)) die("Database $dbs has not been found on the server.<br>");
	$query = mysql_query("SHOW TABLES FROM `$dbs`");
	while($row = mysql_fetch_array($query)) $db[$row[0]] = $dbs;
}

heres the script for the amount of items ingame

$amt = 0;
$query = mysql_query("SELECT * FROM ".$db['inventory'].".`inventory` ".$string."");
while ($row = mysql_fetch_object($query)) {$amt += $row->amount;}
$query = mysql_query("SELECT * FROM ".$db['cart_inventory'].".`cart_inventory` ".$string."");
while ($row = mysql_fetch_object($query)) {$amt += $row->amount;}
$query = mysql_query("SELECT * FROM ".$db['storage'].".`storage` ".$string."");
while ($row = mysql_fetch_object($query)) {$amt += $row->amount;}

echo "Item ID ".$itemid." exists ".$amt." times!";

heres the script for the amount of player owning the item (not counting the storage)

$ItemOwner = array();
$query = mysql_query("SELECT * FROM ".$db['cart_inventory'].".`cart_inventory` ".$string."");
while ($row = mysql_fetch_object($query)) {
	if(!in_array($row->char_id, $ItemOwner)) $ItemOwner[] = $row->char_id;
}
$query = mysql_query("SELECT * FROM ".$db['inventory'].".`inventory` ".$string."");
while ($row = mysql_fetch_object($query)) {
    if(!in_array($row->char_id, $ItemOwner)) $ItemOwner[] = $row->char_id;
}


echo "".count($ItemOwner)." Player own Item ID: ".$itemid."";

Edited October 6, 2013 by Sunzuke

[Kido]

thank you so much! it's hard to me to understand the whole script but thanks to a friend of mine now we may look at both scripts and codes so we can compare a lot of things, thank you this totally helps!

 

just a question

why you did not include storage? it is not possible o:? just asking