[SOLVED] Quick database question

[proxximo]

Hello,

I am having a problem with one of my scripts and cant figure out exactly what is goin wrong. I am trying to call a certain database table using a variable value. Here is the code I am having problems with.

if ($userrow["currentarea"] == "Outside Kings Fortress") {
$monsterdb = "townmonster1";
}


        $monsterquery = doquery("SELECT * FROM {{table}} ORDER BY RAND() LIMIT 1", "$monsterdb");

I know that this is the problem in the script as I have added the townmonster1 to the query itself and the script works,

        $monsterquery = doquery("SELECT * FROM {{table}} ORDER BY RAND() LIMIT 1", "townmonster1");

so i am guessing that calling the table using a variable is the problem. Does anyone know how to fix this? or know a snippet of code that could help?

[cunoodle2]

How about just to start don't use a variable for the table and just hard code it in.  That may help you in your troubleshooting aspect of items to determine exactly what the problem is.

[proxximo]

I did hard code it and it worked fine, thats why I know its this part of the script that is the problem. But I do need to use a variable at this stage because I have about 250 different tables that use this script and didnt want to rewrite the 500 lines of code for each of the 250 tables :(

[proxximo]

Bump

[trq]

variables start with a $ in php. Surely you meen something more like....?

[code=php:0]
"SELECT * FROM $table ORDER BY RAND() LIMIT 1"
[/code]

Otherwise, where going to need to see the doquery() function.

[dbo]

Personally I always suggest storing any dynamic-ish queries in a string before processing.

For example:

$query = "SELECT * FROM sometable";
$result = mysql_query($query);  //or in this case doquery($query);

The reason being when you are trying to debug the script you can simply do an echo $query to make sure the query you are attempting to send is in fact what is generated.

[proxximo]

Here is my doquery function

function doquery($query, $table) { // Something of a tiny little database abstraction layer.
   
    include('config.php');
    global $numqueries;
    $sqlquery = mysql_query(str_replace("{{table}}", $dbsettings["prefix"] . "_" . $table, $query)) or die(mysql_error());
    $numqueries++;
    return $sqlquery;

}


so my setup looks like this

$monsterquery = doquery("SELECT * FROM {{table}} ORDER BY RAND() LIMIT 1", "$monsterdb");

I already have the variable filled by the time it gets to this point

[dbo]

Store that query in a variable before calling doquery, echo it and make sure it's what you think it is. If it is, take the query and run it in phpMyAdmin or some similar tool to find out if the query works as expected.

[proxximo]

[quote author=dbo link=topic=120609.msg495055#msg495055 date=1167709968]
Personally I always suggest storing any dynamic-ish queries in a string before processing.

For example:

$query = "SELECT * FROM sometable";
$result = mysql_query($query);  //or in this case doquery($query);

The reason being when you are trying to debug the script you can simply do an echo $query to make sure the query you are attempting to send is in fact what is generated.
[/quote]

I store in the next line, the error that I am getting is that the table doesnt  "pod_ " (In this case it should be "pod_townmonster1")  doesnt exist, which means the function isnt in fact taking the info contained in the variable.

[proxximo]

if ($userrow["currentarea"] == "Outside Kings Fortress") {
$monsterdb = "townmonster1";
}


        $monsterquery = doquery("SELECT * FROM {{table}} ORDER BY RAND() LIMIT 1", $monsterdb);
        $monsterrow = mysql_fetch_array($monsterquery);

[proxximo]

SO ultimately I guess the question is, can you use a variable in this situation? or is the function not set up to do so?

[dbo]

Debug how you wish. But I echo line by line the variables so I can found out exactly where it goes haywire. I would do it as I suggested before the doquery call, once it gets into the doquery function, and then right before it attempts to actually execute the query, but the mysql error is helpful as well.

[proxximo]

Ok think I got it all sorted. Thanks for your help dbo!

[dbo]

My pleasure.

What was the problem?

[proxximo]

I forgot to unset the monsterquery. Everything worked fine after that