Need Help modifying this query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

is the 'alive' querystring var always passed?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Yes, the user selects either alive or dead from a list box.

I'm not at that PC right now, I believe alive gives a value of 1 and dead is 0.
I might have that backwards, but it's something like that.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

And can you explain your query in simple english? I think the query now is a little bloated and there are a ton of conditionals within it that might be able to be handled differently/better.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

The form has optional fields that the user can select:
It has a level search, where the user selects a start and ending level.
It also has a field to search by user name,

And then it also has the Alive/Dead selection, it defaults to alive.

The way the search works out of the box is, for the alive/dead portion. A player is dead, if their HP is < then 20 and then considered alive if it's >20.

I've changed my game, and now have a medical_ward table that holds if a player is dead. So I need to scrap the HP part of the query, and then have it look at the medical_ward table. If the players ID# is listed in the ward, he's dead. If not, then he's alive.

That's what I just can't seem to figure out how to do.

Here is the original code that ezRPG uses.

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;
 
So I've been trying to modify it, using some of the suggestions here but just can't seem to get it right. That's probably why it seems there are too many IFs.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

And you are trying to join players on medical_ward?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Yes,

The field in the medical_ward is
playerdead_ID

Originally I thought something like this to find the dead.
LEFT JOIN medicalward ON players.id = medicalward.playerdead_ID

but I couldn't figure out how to include that in my query.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

That logic is going to kick you in the rump.

Player id should be player id, not pleayer dead id. Handle joins based on logic so that the data tells you what is getting selected.

Can you do a show create table query for both tables and post them back here?

[sql]SHOW CREATE TABLE `players`;[/sql]
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

The medical ward table, also has a column that stores who killed the player.
So I needed to be able to tell which columns were what.

In this type of situation, how would you name the columns?
I have playerdead_ID
and Killed_By_ID

I'm not familiar with show table, I'm trying to lookup how to display it but it's not working.
I found this example, and change the name of the table but I get an error.

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 />
';
}
 
?>
Last edited by RobertGonzalez on Fri Aug 15, 2008 12:05 pm, edited 1 time in total.
Reason: Edited to add code tags
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

Do you have a phpMyAdmin interface to your database, or some other client tool you can use? If not those, can you access it from the command line? The query I showed you above is what you need to run. The syntax is simple:

[sql]SHOW CREATE TABLE `tableName`;[/sql]
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Yes I do have phpmyadmin, but where do I run this? In the Query tab?
I've been trying, in the SQL Query on Database box, I put this
SHOW CREATE TABLE `players`;

and it comes back with an error of 'You have to choose at least one column to display'
I've tried searching the phpmyadmin help section, but can't find anything on show create table.

Chris.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

That is odd. When I run from the command line like this (a table in a database of mine):

Code: Select all

mysql> show create table `persons`;
I get:

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)
 
I haven't used phpMyAdmin in years so I wouldn't even know if it can handle a query like this, though I suspect it should.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

OK, I found another area where I can run queries, and got it to work.

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
 
Medical Ward

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'
 
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Did the show create table results help?

Thanks again
Chris.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Need Help modifying this query

Post by RobertGonzalez »

Not exactly. I think I am getting hung up on the relationship and what you are after. Let me reread this thread tonight or tomorrow to see if I can actually offer some solid help to you.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Let me see if I can explain it better.

I want my users to be able to search for other players, and be able to filter out ones that are alive and ones that are dead.

If a player is dead, their user ID gets entered into the medical ward table (along with some other info like what player killed them, and what weapon was used) the dead players id is stored playerdead_ID.

So basically, when a person searches.
If they select alive, it will bring back all the players not listed in the medical ward.
If they select dead, it will bring back those only listed in the medical ward.
Post Reply