Page 2 of 4
Re: Need Help modifying this query
Posted: Sun Jul 13, 2008 9:40 am
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 ":"";
Re: Need Help modifying this query
Posted: Sun Jul 13, 2008 2:52 pm
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) ).
Re: Need Help modifying this query
Posted: Mon Jul 14, 2008 6:18 pm
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
Re: Need Help modifying this query
Posted: Tue Jul 15, 2008 6:20 pm
by cdoyle
am I correct, that's how it's suppose to be written?
Re: Need Help modifying this query
Posted: Tue Jul 15, 2008 7:13 pm
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.
Re: Need Help modifying this query
Posted: Tue Jul 15, 2008 7:17 pm
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
Re: Need Help modifying this query
Posted: Sat Jul 19, 2008 3:55 pm
by cdoyle
Does anyone here have a little adodb experience that could help me get what we have so far working?
Thanks
Chris.
Re: Need Help modifying this query
Posted: Thu Jul 24, 2008 5:31 pm
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.
Re: Need Help modifying this query
Posted: Mon Aug 04, 2008 7:55 pm
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?
Re: Need Help modifying this query
Posted: Mon Aug 04, 2008 8:08 pm
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/
Re: Need Help modifying this query
Posted: Mon Aug 04, 2008 9:08 pm
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 ?
Re: Need Help modifying this query
Posted: Tue Aug 05, 2008 12:33 am
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.
Re: Need Help modifying this query
Posted: Thu Aug 07, 2008 7:44 pm
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']));
}
Re: Need Help modifying this query
Posted: Thu Aug 07, 2008 11:52 pm
by RobertGonzalez
Echo your query before you execute it. Post the exact query the database is seeing.
Re: Need Help modifying this query
Posted: Wed Aug 13, 2008 6:57 pm
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