Re: Need Help modifying this query
Posted: Thu Aug 14, 2008 12:48 pm
is the 'alive' querystring var always passed?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
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 20";
//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)?" selected=\"selected\"":"";
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=\"50%\">Username</th><th width=\"20%\">Level</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=\"50%\"><a href=\"profile.php?username=" . $result['username'] . "\">" . $result['username'] . "</a></td>\n";
echo "<td width=\"20%\">" . $result['level'] . "</td>\n";
echo "<td width=\"30%\"><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";
include("templates/private_footer.php");
break;
Code: Select all
<?php
$result = mysql_query("SHOW TABLE STATUS FROM test;");
while($array = mysql_fetch_array($result)) {
$total = $array[Data_length]+$array[Index_length];
echo '
Table: '.$array[Name].'<br />
Data Size: '.$array[Data_length].'<br />
Index Size: '.$array[Index_length].'<br />
Total Size: '.$total.'<br />
Total Rows: '.$array[Rows].'<br />
Average Size Per Row: '.$array[Avg_row_length].'<br /><br />
';
}
?>Code: Select all
mysql> show create table `persons`;Code: Select all
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| persons | CREATE TABLE `persons` (
`person_id` int(11) NOT NULL auto_increment,
`person_type` int(11) NOT NULL default '0',
`person_login` varchar(20) default NULL,
`person_password` varchar(64) default NULL COMMENT 'SHA256 hash of the password string',
`person_salt` varchar(20) default NULL COMMENT 'Password salt string',
PRIMARY KEY USING BTREE (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='People that have something to do with this app' |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
Code: Select all
Table Create Table
players CREATE TABLE `players` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) collate latin1_general_ci NOT NULL default '',
`password` varchar(255) collate latin1_general_ci NOT NULL default '',
`email` varchar(255) collate latin1_general_ci NOT NULL default '',
`rank` varchar(255) collate latin1_general_ci NOT NULL default 'Member',
`registered` int(11) NOT NULL default '0',
`last_active` int(11) NOT NULL default '0',
`ip` varchar(255) collate latin1_general_ci NOT NULL default '',
`level` int(11) NOT NULL default '1',
`stat_points` int(11) NOT NULL default '5',
`gold` int(11) NOT NULL default '100',
`bank` int(11) NOT NULL default '0',
`CAC_First_International` int(11) NOT NULL default '0',
`hp` int(11) NOT NULL default '50',
`maxhp` int(11) NOT NULL default '50',
`exp` int(11) NOT NULL default '0',
`maxexp` int(11) NOT NULL default '50',
`energy` int(11) NOT NULL default '10',
`maxenergy` int(11) NOT NULL default '10',
`fat` int(11) NOT NULL default '0',
`maxfat` int(11) NOT NULL default '100',
`strength` int(11) NOT NULL default '1',
`vitality` int(11) NOT NULL default '1',
`agility` int(11) NOT NULL default '1',
`interest` tinyint(1) NOT NULL default '0',
`kills` int(11) NOT NULL default '0',
`deaths` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=70 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
Code: Select all
medical_ward CREATE TABLE `medical_ward` (
`HOS_ID_PK` int(11) NOT NULL auto_increment,
`Killed_By_ID` int(11) NOT NULL default '0',
`Weapon_Used_ID` int(11) NOT NULL default '0',
`playerdead_ID` int(11) NOT NULL default '0',
`Time_Left` int(11) NOT NULL default '2',
PRIMARY KEY (`HOS_ID_PK`)
) ENGINE=MyISAM AUTO_INCREMENT=222 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='people in medical ward'