We have lots of no-name character in our database, how can I delete all of them at once?
Printable View
We have lots of no-name character in our database, how can I delete all of them at once?
if you need to know this so the blank names don't show in ranking you don't need to delete them you just need to modify the rankings page
How do I change the query to exclude blanks? o.oQuote:
Originally Posted by gregon13
Use this to delete them:
Put it in your rankingslist or something.
Note1: edit your hostnamePHP Code:$hostname = "mssql database name";
$username = "sa";
$password = "mssql database password";
$database = "GunzDB";
$connect = odbc_connect("Driver={SQL Server};Server={$hostname};Database={$database}",$username,$password) or die ("Error while connecting");
$one = odbc_exec($connect, "SELECT CID FROM Character WHERE Name=''");
while($two = odbc_fetch_object($one)) {
$empty = $two->CID;
odbc_exec($connect, "DELETE FROM CharacterItem WHERE CID='$empty'")
odbc_exec($connect, "DELETE FROM Friend WHERE FriendCID='$empty' OR CID='$empty'")
odbc_exec($connect, "DELETE FROM Character WHERE CID='$empty'")
}
Note2: edit your password
Note3: There might be a mistake in my script since I typed this out of my head..
Use this to exclude them:
Note: Im not going to spoonfed you, use this simple php sentence to edit your current ranklist code.PHP Code:$query = odbc_exec($connect, "SELECT * FROM Character WHERE Name != ''");
I understand it perfect, didn't know if != can be used in it.Quote:
Originally Posted by Trilest
I was thinking:
But didn't have the balls to try it.Code:SELECT TOP 50* FROM Character WHERE Name != '' ORDER BY KillCount DESC
How would I restrict 254 and 255 grade form showing up?
That would require 2 queries, right?
I didn't use != i added an extra select to it
Code:$res = mssql_query("SELECT TOP 100 * FROM Character WHERE (DeleteFlag=0 OR DeleteName=NULL) ORDER BY Level DESC, XP DESC, KillCount DESC, DeathCount ASC");
Quote:
Originally Posted by V_o_o_d_o_o
Note: Im not sure if its UGradeID or UgradeID. You will have to check that.PHP Code:SELECT TOP 50* FROM Character WHERE Name != '' AND UGradeID != '254' AND UGradeID != '255' ORDER BY KillCount DESC
you actually need to select UgradeID from the account not character table so you infact make a separate queryQuote:
Originally Posted by Trilest
DELETE FROM CharacterItem
DELETE FROM Character
That isn't true. You INNER JOIN the tables in the query.Quote:
Originally Posted by gregon13
There are tables dependent on those tables. (Foreign Key Constraints)Quote:
Originally Posted by hotgame
This has already been released by Wizkid. I will post the script below. Just save as a .php, and then but it in your web, and browse to the file with your internet browser.
I do not take any credits for this. Like I said, this was created by Wizkid. Hope I helped a bit.PHP Code:<?php
//Written by Wizkid
//Copyright 2008 - 2009
//Let us connect the databases.
//Yeah yeah, ODBC this time.
$host = ""; //The host.
$user = "SA"; //The username.
$pass = ""; //The password. I hope it's unique for you.
$dbname = "GunzDB"; //The dbname. Most likely GunzDB.
$connect = odbc_connect("Driver={SQL Server};Server={$host}; Database={$dbname}", $user, $pass) or die("Can't connect the MSSQL server.");
//The num_rows() function for ODBC since the default one always returns -1.
function num_rows(&$rid) {
//We can try it at least, right?
$num= odbc_num_rows($rid);
if ($num >= 0) {
return $num;
}
if (!odbc_fetch_row($rid, 1)) {
odbc_fetch_row($rid, 0);
return 0;
}
if (!odbc_fetch_row($rid, 2)) {
odbc_fetch_row($rid, 0);
return 1;
}
$lo= 2;
$hi= 8192000;
while ($lo < ($hi - 1)) {
$mid= (int)(($hi + $lo) / 2);
if (odbc_fetch_row($rid, $mid)) {
$lo= $mid;
} else {
$hi= $mid;
}
}
$num= $lo;
odbc_fetch_row($rid, 0);
return $num;
}
//Query time.
$query = odbc_exec($connect,"SELECT CID FROM Character WHERE Name = ''");
$count = num_rows($query);
while(odbc_fetch_row($query))
{
$cid = odbc_result($query, 1);
odbc_exec($connect,"DELETE FROM CharacterItem WHERE CID = '" . $cid . "'");
odbc_exec($connect,"DELETE FROM Character WHERE CID = '" . $cid . "'");
echo "Removed the character with CID " . $cid . ". <br />";
}
echo $count . " characters have been totally removed out of the database.";
?>
~InsaneCodeZ