Deleting blanks

[V_o_o_d_o_o]

We have lots of no-name character in our database, how can I delete all of them at once?

[MentaL]

[gregon13]

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

[V_o_o_d_o_o]

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.o

[Trilest]

Use this to delete them:
Put it in your rankingslist or something.

$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'")
} 

Note1: edit your hostname
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:

$query = odbc_exec($connect, "SELECT * FROM Character WHERE Name != ''"); 

Note: Im not going to spoonfed you, use this simple php sentence to edit your current ranklist code.

[V_o_o_d_o_o]

Note: Im not going to spoonfed you, use this simple php sentence to edit your current ranklist code.

I understand it perfect, didn't know if != can be used in it.

I was thinking:

SELECT TOP 50* FROM Character WHERE Name != '' ORDER BY KillCount DESC

But didn't have the balls to try it.

How would I restrict 254 and 255 grade form showing up?

That would require 2 queries, right?

[gregon13]

I didn't use != i added an extra select to it

$res = mssql_query("SELECT TOP 100 * FROM Character WHERE (DeleteFlag=0 OR DeleteName=NULL) ORDER BY Level DESC, XP DESC, KillCount DESC, DeathCount ASC");

[Trilest]

I understand it perfect, didn't know if != can be used in it.

I was thinking:

SELECT TOP 50* FROM Character WHERE Name != '' ORDER BY KillCount DESC

But didn't have the balls to try it.

How would I restrict 254 and 255 grade form showing up?

That would require 2 queries, right?

SELECT TOP 50* FROM Character WHERE Name != '' AND UGradeID != '254' AND UGradeID != '255' ORDER BY KillCount DESC 

Note: Im not sure if its UGradeID or UgradeID. You will have to check that.

[gregon13]

SELECT TOP 50* FROM Character WHERE Name != '' AND UGradeID != '254' AND UGradeID != '255' ORDER BY KillCount DESC 

Note: Im not sure if its UGradeID or UgradeID. You will have to check that.

you actually need to select UgradeID from the account not character table so you infact make a separate query

[hotgame]

DELETE FROM CharacterItem
DELETE FROM Character

[Linear88]

you actually need to select UgradeID from the account not character table so you infact make a separate query

That isn't true. You INNER JOIN the tables in the query.

DELETE FROM CharacterItem
DELETE FROM Character

There are tables dependent on those tables. (Foreign Key Constraints)

[InsaneCodeZ]

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.

<?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.";
?>

I do not take any credits for this. Like I said, this was created by Wizkid. Hope I helped a bit.

~InsaneCodeZ