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

Re: Need Help modifying this query

Post by cdoyle »

I would be willing to try a normal mySQL function, just to see if we can get this working.

I'm just not familiar enough with how this search works, to convert it.

How would something like this, look without adodb?
$query .= ($_GET['username'] != "")?"`username` LIKE ? and ":"";
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 »

How would something like this, look without adodb?
$query .= ($_GET['username'] != "")?"`username` LIKE ? and ":"";
With adodb (and other prepared statement abstractions), such expressions are injected with parameters separately, with the parameters replacing the question mark. So in a normal SQL query you would put the actual value where the question mark is:

Code: Select all

 
$query .= ($_GET['username'] != "") ? "`username` LIKE '%" . trim($_GET['username']) . "%' AND " : "";
 
The values in your original code snippet are specified right after the query building - they are pushed into an array which is later prepared with the query ($query = $db -> execute($query,$values) ).
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

pytrin wrote:
How would something like this, look without adodb?
$query .= ($_GET['username'] != "")?"`username` LIKE ? and ":"";
With adodb (and other prepared statement abstractions), such expressions are injected with parameters separately, with the parameters replacing the question mark. So in a normal SQL query you would put the actual value where the question mark is:

Code: Select all

 
$query .= ($_GET['username'] != "") ? "`username` LIKE '%" . trim($_GET['username']) . "%' AND " : "";
 
The values in your original code snippet are specified right after the query building - they are pushed into an array which is later prepared with the query ($query = $db -> execute($query,$values) ).
So to make sure I'm understanding this right, to go to normal mySQL

$query .= ($_GET['username'] != "")?"`username` LIKE ? and ":"";
would be replaced with this
$query .= ($_GET['username'] != "") ? "`username` LIKE '%" . trim($_GET['username']) . "%' AND " : "";

Then I would have to do something similar with all of them.
Or do you know of a way to make this work with adodb?

this search thing has got me stumped
I've attached the battle.php if anyone wants to see how the whole thing looks
Attachments
battle.zip
(4.48 KiB) Downloaded 114 times
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

am I correct, that's how it's suppose to be written?
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 »

Ah yes, sorry I missed that. I looked over your battle.php script, and it doesn't look promising - it uses the adodb API throughout. I think you'd be better off changing the original query to work with adoDB so you won't have to change the rest of your script.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

pytrin wrote:Ah yes, sorry I missed that. I looked over your battle.php script, and it doesn't look promising - it uses the adodb API throughout. I think you'd be better off changing the original query to work with adoDB so you won't have to change the rest of your script.
Oh, I didn't realize the whole page would have to be changed. I thought I could update just the search part with standard mySQL, and it would work.

bummer, does anyone else on here know adodb that could help me get this working?

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

Re: Need Help modifying this query

Post by cdoyle »

Does anyone here have a little adodb experience that could help me get what we have so far working?

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

Re: Need Help modifying this query

Post by cdoyle »

Does anyone know of any adodb forums that might be able to help get this working?

It seems like what we have so far is so close, it's just something small that needs to be changed.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Hi,

I've had some more time to come back to this, and noticed something

The original search script had this line of code

Code: Select all

$query .= ($_GET['alive'] == "1")?"`hp` > 20 ":"`hp` < 20 ";
It determined someone was dead if their health was under 20 I believe.
Since I've changed the criteria of when someone was dead (using the medical ward table) I removed this line.

Removing this line is causing some of the errors I see, if I put it back the errors go away. But my search still isn't working right.

I even tried moving that line inside the If statement that we came up with earlier in this thread, and it still errors.
Any idea?

original

Code: Select all

 
            $query .= ($_GET['alive'] == "1")?"`hp` > 20 ":"`hp` < 20 ";
        $query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
        $query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
        $query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
        $query .= "limit 20";
 
test code, that didn't work.

Code: Select all

 
        $query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
        $query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
        $query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
        $query .= "limit 20";
            if($_GET['alive'] == 1) 
            {
            $query .= ($_GET['alive'] == "1")?"`hp` > 20 ":"`hp` < 20 ";
            }   
 
Either way, I need to be able to get remove that line and use my new queries to determine if someone is dead?
Does this help anyone in some new ideas?

Maybe I'm wrong but shouldn't my moving the line into the IF be the same thing as I had before, if option 1 is selected?
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 »

To help you track what is happening try this (snagged from a previous post and modified slightly to help out a bit more):

Code: Select all

<?php
$sql = 'SOME SQL STATEMENT HERE';
$rs = $db->execute($sql); 
if (!$rs) {
  die( $rs->ErrorMsg() . ' ocurred in ' . $sql);
}
 
if ($rs->recordcount() > 0) //Check if any players were found
{
...
?>
Basically this returns the error message AND the SQL string that the database saw. This will help you tremendously in your efforts to debug your situation.

PS The ADODB manual is at http://phplens.com/adodb/
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Everah wrote:To help you track what is happening try this (snagged from a previous post and modified slightly to help out a bit more):

Code: Select all

<?php
$sql = 'SOME SQL STATEMENT HERE';
$rs = $db->execute($sql); 
if (!$rs) {
  die( $rs->ErrorMsg() . ' ocurred in ' . $sql);
}
 
if ($rs->recordcount() > 0) //Check if any players were found
{
...
?>
Basically this returns the error message AND the SQL string that the database saw. This will help you tremendously in your efforts to debug your situation.

PS The ADODB manual is at http://phplens.com/adodb/
Thanks, but I'm not sure where do I put this?

Does it display the same error as this would?
echo mysql_error();

If so, this is the error I'm getting when I remove that line from the main code, and just leave it in my if statement.
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 'limit 20`hp` > 20' at line 1
Fatal error: Call to a member function recordcount() on a non-object in
If it's different, let me know where I should put it. I'm also not sure what SQL should I enter for $sql ?
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 »

The snippet I posted is where you would run the query you are struggling with. Though with the error you posted I would imagine it has something to do with you putting a limit clause before your conditional.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

Everah wrote:The snippet I posted is where you would run the query you are struggling with. Though with the error you posted I would imagine it has something to do with you putting a limit clause before your conditional.
I've been trying to use the code you gave me to test, and I keep getting the same error no matter what query I use
Call to a member function fetchrow()

could it be because my game is using Adodb?

you mentioned that you thought it was because I was putting a limit before my conditional?
I've tried moving stuff around, and still get the same error.

I do think it has something to do with the 'IF' because If I move the health check back to where it was to start, it works fine.

Here is what I have now

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
        
            if($_GET['alive'] == 1) 
            {
            $query .= ($_GET['alive'] == "1")?"`hp` > 20 ":"`hp` < 20 ";
            }
            $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 .= "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']));
        }
 
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 »

Echo your query before you execute it. Post the exact query the database is seeing.
cdoyle
Forum Contributor
Posts: 102
Joined: Wed Feb 13, 2008 7:26 pm

Re: Need Help modifying this query

Post by cdoyle »

OK,
I was able to get the stock query working with the IF with this

Code: Select all

 
        if($_GET['alive'] == 1)
             {
        $query .= ($_GET['alive'] == "1")?"`hp` > 20 ":"`hp` < 20 ";
        $query .= ($_GET['username'] != "")?"`username` LIKE  ? and ":"";
        $query .= ($_GET['fromlevel'] != "")?"`level` >= ? and ":"";
        $query .= ($_GET['tolevel'] != "")?"`level` <= ? and ":"";
        $query .= "limit 20";
    }
 
but now I need to change the 'alive == 0' to use this.
INNER JOIN medical_ward ON players.id = medical_ward.playerdead_ID

How do I echo the query, before it executes?
I added this in my page
echo $query, and after I press the button I get this with the above query

If I leave the level and player name fields blank, this is what it displays in the page

SELECT players.*, Cities.City_Name FROM players AS players Left JOIN Cities AS Cities ON ( Cities.City_ID = players.City_ID ) WHERE `id`!= ? and `hp` > 20 limit 20
Post Reply