php mysql function (oop style)

[Raymien]

Hello Folks,

 

I have a particular problem that I can't for the life of me sort out. The following function submits the query to the database and returns a result.

 

 function queryDB($field, $table, $where = "", $group = "", $sort = "", $limit = "") {
$sql = "SELECT `$field` FROM `$table`" . $where . $group . $sort . $limit;
$myresult = $this->db->myQuery($sql);
$ech = ($myresult)?"Result is Good!": "Result is Bad: " .$myresult;
echo $ech;
$word = $this->db->fetchSingleValue($myresult, $field);
return $word;
}

 

And it appears to work just fine for most of the queries I"m making, but for some reason, at one point it doesn't, and gives a bad result, with no discernable error. The info that is passed to the function is the Table, Field and the Where clause.

 

What could possibly be causing the result to be bad?

 

And yes, I've output the sql, and run it in phpMyAdmin, with success, so I'm really at a loss.

 

Any help would be greatly appreciated.

 

Ray

[Jessica]

You have to capture errors. Are you using a third party DB library or is that just mysqli?

[requinix]

What are the values of the parameters? What is the $sql?

Edited January 29, 2013 by requinix

[Raymien]

Thanks for the replies, here's the submission and function as originally written, without the screen outputs I added to find out what's wrong.

 

The variables that are sent:

$this->db->queryDB(pro, vtp_members, WHERE `id` ='57962', "","","");

 

The SQL that is returned within the function:

SELECT `pro` FROM `vtp_members` WHERE `id` ='57962'

 

The original without the debug code I added:

 

function queryDB($field, $table, $where = "", $group = "", $sort = "", $limit = "") {
$sql = "SELECT `$field` FROM `$table`" . $where . $group . $sort . $limit;
$myresult = $this->db->myQuery($sql);
$word = $this->db->fetchSingleValue($myresult, $field);
return $word;
}

 

Again, this is the last function call of the script, thare are 5-10 other calls before this one that work without issue.

 

I'm beginning to wonder if i need to "wake up" the database connection, as this does happen at the end of the action, just prior to cleanup. (and yes, I checked that cleanup wasn't happening before this in error)

 

Ray

Edited January 30, 2013 by Raymien

[PFMaBiSmAd]

You need to use the error reporting method/function of your database interface to find out why the query is failing. This is why Jessica asked what underlying database library you are using - mysql, mysqli, PDO?

[Christian F.]

If this is the exact code you're using, than that's the problem. Or at least one of the problems:

$this->db->queryDB(pro, vtp_members, WHERE `id` ='57962', "","","");

 

The problem is that you're missing the quotes around the PHP strings. Causing PHP to think that it's actual PHP code, instead of text that's to be sent to a third party system for it to parse.

 

Also, don't use quotes around numbers in SQL queries. Numbers aren't strings, and by quoting them you may introduce problems. At the very least you're hurting performance.

[Raymien]

Sorry about not being specific about the db function/structure.

 

We're using straight mysqli, with a few custom functions.

 

The functions are working fine in other areas of the script, but it's just prior to cleanup where this lack of anything is occurring, that is, no error is found.

 

Christian, as for numbers and quotes, in this case ,the db is a varchar field, with a mix of numbers and characters, so quotes is needed (it was a merge of two systems some time back)

Also, the quote made here is not perfect, the where clause, and variables is properly quoted.

 

I've moved parts of this to the opening variable creation, and it is now working, it's now only the actual reward for a successfully played game that is not working.

 

Again, this call comes near the end of the process, and the sql is good, so I once again am wondering if it's a wake up call that is needed.

 

In any case, I'll report back here in a bit.

 

Ray

[PFMaBiSmAd]

echo $this->db->error;

[Raymien]

Ok guys, thanks for your patience and nudges in the right direction.

 

Finally this turned out to be a case where the database connection was being lost during the procedures that followed the initial database calls. I was able to refresh the connection and things are actually working correctly.

 

Still was a bit of a headscratcher, but I'm happy it's working correctly. Still new at oop, and I think I might have let some of that procedure process slip in.

 

All good now, thanks!

 

Ray