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

cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Need Help modifying this query

Post by cdoyle »

Hi,

I'm trying to modify a search function within a ezRPG game, and I'm just not sure how this search works so not sure how to make it do what I want.

The search form out of the box, has a drop down where the players can select 'alive' or 'dead' and it brings back results if hp>0 you're alive, hp=0 you're dead.

Well I've changed my game some, and now I have a new table that when you're dead the players ID is listed in the table for a set amount of time depending on what weapon was used against them.

So I need to change how the alive/dead search works.

I think I have the queries needed to actually pull the data, or at least close to having it. Just not sure how to incorporate them into this search.


Here is the search code that comes with the game.
This is the part where I need to modify
$query .= ($_GET['alive'] == "1")?"`hp` > 0 ":"`hp` = 0 ";

I need to change what alive==1 is looking at,
and I believe I need to add a $query .= ($_GET['dead'] == "0")

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 `id`, `username`, `hp`, `maxhp`, `level` from `players` 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 "<fieldset>\n";
        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</fieldset>\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";
 
I think the query that is needed would be something like this
for finding the dead players
SELECT medicalward.playerdead_ID, players.username, players.id
FROM players LEFT JOIN medicalward ON players.id = medicalward.playerdead_ID;

Then to find those alive, it would be anyone not listed in the medicalward table.

Does this sound right, and if so how do I add these queries so that 1 is ran when dead is selected and another ran when alive is selected?

Thanks
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Anyone have any suggestions?

Would it be better to try and not modify this search, and start over and create something else?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Anyone have any suggestions?

Am I on the right track with the query?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Nobody has any suggestions on how to do it?
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Need Help modifying this query

Post by WebbieDave »

I've read your post and must admit that it's difficult to understand your quandary and what you're trying to accomplish. My guess is others feel the same and are wary to venture a guess as to what you should do. Would you be willing to break this down even more, or perhaps restate the problem in a different way? It may help clear things up for some.

I read it and am left with many questions, such as, but not limited to:

Is medicalward your new table where dead players are temporarily stored? Is this table currently implemented and properly being updated?

Are you trying to change this code so that it searches the medicalward table instead of the players table?
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Hi,
thank you for replying and I'll try and answer the questions best I can.

Yes the medical ward table is where the dead players are kept, and the table is updated.
Each player is dead for a set amount of time, depending on what weapon was used.

What I'm trying to do is, the standard search that came with ezRPG looks at the hp of the player. If it's 0 the player is considered dead, if it's >0 he's alive.

But now that I have this medical ward table, being dead is not determined by the HP of the player. So I need to find a way so if a user wants to search for players. If they are searching for alive, it filters out anyone not listed in the medical ward table, if they are searching for dead it only lists those who are in the table.

I just can't figure out how to make the search do that. I mean, I can make a query that gives me these results, but not sure how to get the search to use these queries, depending on what option the user selected in the dropdown.

Does that help?
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Need Help modifying this query

Post by WebbieDave »

Can you post the queries that you've successfully composed?

This is the dead one, yes?

Code: Select all

SELECT medicalward.playerdead_ID, players.username, players.id
FROM players 
LEFT JOIN medicalward ON players.id = medicalward.playerdead_ID
Can we see the alive one?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need Help modifying this query

Post by Eran »

I just can't figure out how to make the search do that. I mean, I can make a query that gives me these results, but not sure how to get the search to use these queries, depending on what option the user selected in the dropdown.
Use the value received from the the select input to make up the query in two separate cases, like this:

Code: Select all

 
//Construct query
$query = "select `id`, `username`, `hp`, `maxhp`, `level` from `players` ";
 
if($_GET['alive'] == 0) { //Assuming a value of 0 means that the 'dead' option was selected
$query .= ' INNER JOIN medicalward ON players.id = medicalward.playerdead_ID ';
} 
 
$query .= " where `id`!= ? and ";
$query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
$query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
$query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
 
if($_GET['alive'] == 1) { // Check alive players
$query .= "`id` NOT IN (SELECT playerdead_ID FROM medicalward) ";
}
 
$query .= "limit 20";
 
I am using an inner join to filter the query through the medicalward table (left join would still return results).

This is untested and could fail for a couple of reasons:
1. You have similar named columns in both the 'players' table and 'medicalward' table. If that's the case, you'll need to add the table prefix to the appropriate column names.
2. You are using adoDB for preparing your SQL. I have no experience working with adoDB, so I'm not sure if it will accept my direct SQL query building. I don't think it will be a problem though
3. Since it is untested I might have typos, so check for those

Hope this helps
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Thanks again for your help on this, I think we are getting closer,

Here is what I have now

Code: Select all

 
        //Construct query
 
        if($_GET['alive'] == 0) { //Assuming a value of 0 means that the 'dead' option was selected
        $query .= ' INNER JOIN medical_ward ON players.id = medical_ward.playerdead_ID ';
        }
        
        $query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
        $query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
        $query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
        
        if($_GET['alive'] == 1) { // Check alive players
        $query .= "`id` NOT IN (SELECT playerdead_ID FROM medical_ward) ";
         }
 
I corrected the name of the medical ward table, but everything else looks OK.
When I try and search I get this error

Fatal error: Call to a member function on a non-object in /home/caraudi/public_html/CAC_Mafia_Test_Site/battle.php on line 382

Line 382 is within this section
Line 382 is $bool = 1;

Code: Select all

 
        $query = $db->execute($query, $values); //Search!
        if ($query->recordcount() > 0) //Check if any players were found
        {
            [b]$bool = 1;[/b]
            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;
            }
        }
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need Help modifying this query

Post by Eran »

There is some problem with the query. You need to modify this part:

Code: Select all

 
$query = $db->execute($query, $values); //Search!
if ($query->recordcount() > 0) //Check if any players were found
{
...
 
And add the following lines to know what the error is:

Code: Select all

 
 $query = $db->execute($query, $values); //Search!
 
 if (!$query) 
        die( $db->ErrorMsg() );
 
if ($query->recordcount() > 0) //Check if any players were found
{
...
 
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

It's now giving me this message

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''11'' at line 1'
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Need Help modifying this query

Post by jayshields »

It would be much easier to debug if you changed your mysql_query() calls to something like:

Code: Select all

mysql_query($query) or die(mysql_error() . '<br /> On ' . __LINE__ . ' in ' . __FILE__ . '<br />Offending query: ' . $query);
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

pytrin wrote:There is some problem with the query. You need to modify this part:

Code: Select all

 
$query = $db->execute($query, $values); //Search!
if ($query->recordcount() > 0) //Check if any players were found
{
...
 
And add the following lines to know what the error is:

Code: Select all

 
 $query = $db->execute($query, $values); //Search!
 
 if (!$query) 
        die( $db->ErrorMsg() );
 
if ($query->recordcount() > 0) //Check if any players were found
{
...
 
I just realized I didn't copy paste what you had, correctly.

The error now reads for the alive option:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id` NOT IN (SELECT playerdead_ID FROM medical_ward) limit 50'11'' at line 1

and this error when dead selected
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN medical_ward ON players.id = medical_ward.playerdead_ID limit 50'11'' at line 1
Last edited by cdoyle on Sat Jul 12, 2008 9:32 am, edited 1 time in total.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

jayshields wrote:It would be much easier to debug if you changed your mysql_query() calls to something like:

Code: Select all

mysql_query($query) or die(mysql_error() . '<br /> On ' . __LINE__ . ' in ' . __FILE__ . '<br />Offending query: ' . $query);
Where would I put this?
Do I use this with each query?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need Help modifying this query

Post by Eran »

The syntax jayshields suggested would have worked if you were using plain mysql functions. You are using adoDB so that's not an option.
I'm not sure how adoDB is preparing your query and I haven't found any information on how it handles joins. Maybe someone else here has more experience with adoDB and could help you out - or you move to regular mysql functions.
Post Reply