Need help modifying this search query

[cdoyle]

Hi,

I'm working on my ezRPG game, and on the battle.php they have a search, to locate other players.

Right now, the search form has a dropdown, where you can select 'alive' or 'dead', this is determined by if the HP of the player = 0 dead  >0 alive.

Well I've changed how my game works, and now have a new table 'medical_ward' and if the user ID is in the table, they are dead.   So I need to modify the search query to take this into account, but I can't figure out how the search works.

Here is the current code.

Code: [Select]

case "search":

//Check in case somebody entered 0
$_GET['fromlevel'] = ($_GET['fromlevel'] == 0)?"":$_GET['fromlevel'];
$_GET['tolevel'] = ($_GET['tolevel'] == 0)?"":$_GET['tolevel'];

//Construct query

$query = "SELECT players.*, Cities.City_Name FROM players AS players Left JOIN Cities AS Cities ON ( Cities.City_ID = players.City_ID ) WHERE `id`!= ? and ";
$query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
$query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
$query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
$query .= ($_GET['alive'] == "1")?"`hp > 0 ":"`hp` =  0 ";
$query .= "limit 50";


//Construct values array for adoDB
$values = array();
array_push($values, $player->id); //Make sure battle search doesn't show self
if ($_GET['username'] != "")
{
array_push($values, "%".trim($_GET['username'])."%"); //Add username value for search
}
//Add level range for search
if ($_GET['fromlevel'])
{
array_push($values, intval($_GET['fromlevel']));
}
if ($_GET['tolevel'])
{
array_push($values, intval($_GET['tolevel']));
}

include("templates/private_header.php");

//Display search form again

echo "<legend><b>Search for a player</b></legend>\n";
echo "<form method=\"get\" action=\"battle.php\">\n<input type=\"hidden\" name=\"act\" value=\"search\" />\n";
echo "<table width=\"100%\">\n";
echo "<tr>\n<td width=\"40%\">Username:</td>\n<td width=\"60%\"><input type=\"text\" name=\"username\" value=\"" . stripslashes($_GET['username']) . "\" /></td>\n</tr>\n";
echo "<tr>\n<td width=\"40%\">Level</td>\n<td width=\"60%\"><input type=\"text\" name=\"fromlevel\" size=\"4\" value=\"" . stripslashes($_GET['fromlevel']) . "\" /> to <input type=\"text\" name=\"tolevel\" size=\"4\" value=\"" . stripslashes($_GET['tolevel']) . "\" /></td>\n</tr>\n";
echo "<tr>\n<td width=\"40%\">Status:</td>\n<td width=\"60%\"><select name=\"alive\" size=\"2\">\n<option value=\"1\"";
echo ($_GET['alive'] == 1)?" selected=\"selected\"":"";
echo ">Alive</option>\n<option value=\"0\"";
echo ($_GET['alive'] == 0)?" ":"";
echo ">Dead</option>\n</select></td>\n</tr>\n";
echo "<tr><td></td><td><br /><input type=\"submit\" value=\"Search!\" /></td></tr>\n";
echo "</table>\n";
echo "</form>\n";
echo "<br /><br />";

echo "<table width=\"100%\">\n";
echo "<tr><th width=\"30%\">Username</th><th width=\"20%\">Level</th><th width=\"20%\">Location</th><th width=\"30%\">Battle</a></th></tr>\n";



$query = $db->execute($query, $values); //Search!
if ($query->recordcount() > 0) //Check if any players were found
{
$bool = 1;
while ($result = $query->fetchrow())
{
echo "<tr class=\"row" . $bool . "\">\n";
echo "<td width=\"25%\"><a href=\"profile.php?username=" . $result['username'] . "\">" . $result['username'] . "</a></td>\n";
echo "<td width=\"10%\">" . $result['level'] . "</td>\n";
echo "<td width=\"28%\">" . $result['City_Name'] ."</td>\n";
echo "<td width=\"37%\"><a href=\"battle.php?act=attack&username=" . $result['username'] . "\">Attack</a></td>\n";
echo "</tr>\n";
$bool = ($bool==1)?2:1;
}
}
else //Display error message
{
echo "<tr>\n";
echo "<td colspan=\"3\">No players found. Try changing your search criteria.</td>\n";
echo "</tr>\n";
}
echo "</table>\n";

Here is where it looks at the HP
$query .= ($_GET['alive'] == "1")?"`hp > 0 ":"`hp` =  0 ";

I'm thinking I need to make some queries to check to see if the ID is in the medical ward table
something like this for dead.

Code: [Select]

SELECT medicalward.playerdead_ID, players.username, players.id
FROM players LEFT JOIN medicalward ON players.id = medicalward.playerdead_ID;

Then maybe another query, if the ID is not found in the medical ward table, they are alive.

but I just can't figure out how to add something like this, to the search.

Any thoughts?
Or would it be better to not try and modify this search, and start over with a different method?

Thanks

[cdoyle]

Anyone have any suggestions?

Am I on the right track with my query?