where to look when mysql_query returns false?

[lkrubner]

www.php.net says:

>>>>>>>> Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query()
returns a resource identifier or FALSE if the query was not executed
correctly. For other type of SQL statements, mysql_query() returns TRUE
on success and FALSE on error. A non-FALSE return value means that the
query was legal and could be executed by the server. It does not
indicate anything about the number of rows affected or returned. It is
perfectly possible for a query to succeed but affect no rows or return
no rows.>>>>>>>>>

So if zero rows come back the function will still return true? It only
returns false if something much more serious happened? My script below
is failing somewhere and I'm trying to trouble shoot.



// 11-16-04 - Costin set it up so we do one set of actions for
SELECT, SHOW, EXPLAIN, and DESCRIBE
// queries, and another for all others. For these 4 we return a
link id, otherwise we simply
// return true or false.
if (stristr($query, 'SELECT') || stristr($query, 'SHOW') ||
stristr($query, 'EXPLAIN') || stristr($query, 'DESCRIBE')) {
$this->pp_queryid = @ mysql_query($query, $this->pp_linkid);
if ($this->pp_queryid) {
$this->notifyObject->notify("McDatastoreConnector",
"databaseQuerySuccess");
$this->pp_firstquery = 1;
return $this->pp_queryid;
} else {
$this->resultsObject->error("In query(), in
McDatastoreConnectorMySql, we were not able to run our query.",
"McDatastoreConnectorMySql");
$this->notifyObject->notify("McDatastoreConnector",
"databaseQueryError");
$this->error();
}
} else {
$queried = @ mysql_query($query, $this->pp_linkid);
if ($queried) {
$this->notifyObject->notify("McDatastoreConnector",
"databaseQuerySuccess");
return true;
} else {
$this->resultsObject->error("In query(), in
McDatastoreConnectorMySql, we were not able to run our query.",
"McDatastoreConnectorMySql");
$this->notifyObject->notify("McDatastoreConnector",
"databaseQueryError");
$this->error();
}
}

[Paul Barfoot]

Hi

It would help if you could tell us which part of the code was failing. What
is the value of $query you are using and what results are you expecting for
that query?

--
Paul Barfoot
<lk******@geocities.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

www.php.net says:

>>>>>>>>> Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query()
returns a resource identifier or FALSE if the query was not executed
correctly. For other type of SQL statements, mysql_query() returns TRUE
on success and FALSE on error. A non-FALSE return value means that the
query was legal and could be executed by the server. It does not
indicate anything about the number of rows affected or returned. It is
perfectly possible for a query to succeed but affect no rows or return
no rows.>>>>>>>>>>

So if zero rows come back the function will still return true? It only
returns false if something much more serious happened? My script below
is failing somewhere and I'm trying to trouble shoot.



// 11-16-04 - Costin set it up so we do one set of actions for
SELECT, SHOW, EXPLAIN, and DESCRIBE
// queries, and another for all others. For these 4 we return a
link id, otherwise we simply
// return true or false.
if (stristr($query, 'SELECT') || stristr($query, 'SHOW') ||
stristr($query, 'EXPLAIN') || stristr($query, 'DESCRIBE')) {
$this->pp_queryid = @ mysql_query($query, $this->pp_linkid);
if ($this->pp_queryid) {
$this->notifyObject->notify("McDatastoreConnector",
"databaseQuerySuccess");
$this->pp_firstquery = 1;
return $this->pp_queryid;
} else {
$this->resultsObject->error("In query(), in
McDatastoreConnectorMySql, we were not able to run our query.",
"McDatastoreConnectorMySql");
$this->notifyObject->notify("McDatastoreConnector",
"databaseQueryError");
$this->error();
}
} else {
$queried = @ mysql_query($query, $this->pp_linkid);
if ($queried) {
$this->notifyObject->notify("McDatastoreConnector",
"databaseQuerySuccess");
return true;
} else {
$this->resultsObject->error("In query(), in
McDatastoreConnectorMySql, we were not able to run our query.",
"McDatastoreConnectorMySql");
$this->notifyObject->notify("McDatastoreConnector",
"databaseQueryError");
$this->error();
}
}

[Pedro Graca]

lk******@geocities.com wrote:
<snip>

So if zero rows come back the function will still return true? It only
returns false if something much more serious happened? My script below
is failing somewhere and I'm trying to trouble shoot.

First thing you should do is indent your code and remove the '@'s from
function calls.

Second thing is using the mysql_error() function after (almost) all other
mysql_*() calls, for example:

$results = mysql_query();
if ($results) {
/* query was ok -- may have returned zero rows */
} else {
$errmsg = 'Query error: ' . mysql_error();
/* now do something with $errmsg */
}

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[Norman Peelman]

<lk******@geocities.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

www.php.net says:

>>>>>>>>> Only for SELECT,SHOW,EXPLAIN or DESCRIBE statements mysql_query()
returns a resource identifier or FALSE if the query was not executed
correctly. For other type of SQL statements, mysql_query() returns TRUE
on success and FALSE on error. A non-FALSE return value means that the
query was legal and could be executed by the server. It does not
indicate anything about the number of rows affected or returned. It is
perfectly possible for a query to succeed but affect no rows or return
no rows.>>>>>>>>>>

So if zero rows come back the function will still return true? It only
returns false if something much more serious happened? My script below
is failing somewhere and I'm trying to trouble shoot.

The errors that MySQL returns has nothing to do with what rows and how many
rows are returned. If 'mysql_errno()' returns 0 there was no problem
performing the query (no problems with the query syntax). If it returns
other than 0 then there was. You must use 'mysql_num_rows' or
'mysql_affected_rows':

http://us2.php.net/manual/en/functio...l-num-rows.php
"mysql_num_rows() returns the number of rows in a result set. This command
is only valid for SELECT statements. To retrieve the number of rows affected
by a INSERT, UPDATE or DELETE query, use mysql_affected_rows()."

You should always do something like so:

....
$result = mysql_query($query,$database);
if ((mysql_errno($database) > 0)
{
// query error - do stuff here
}
else
{
// query good - check for results
$num_results = mysql_num_results($database);
if ($num_results == 0)
{
// no results returned - do stuff here
}
else
{
// there are results - do stuff here
}
}
....

Basic, I know but you should get the idea. If you are programming for the
web then you'll see you're errors on the web pages. If you want you can
create a log for informational purposes... try this:

// set these lines at the start (top) of your script
define('LOGFILE',true); // set to false to turn off logging.
$u_id = uniqid('uid');
define('UID',$u_id);

function logfile($txt)
{
if (LOGFILE)
{
$txt = date("G:i:s - ").UID.' - '.$txt.chr(13);
$lf = 'path\\to\\logfile'.date(' D M j - Y').'.txt';
$fp = fopen($lf,'a');
fwrite($fp,$txt,1024);
fclose($fp);
}
}
// ---

then try:

logfile("\n\r--- start of request ---");
....
$result = mysql_query($query,$database);
if ((mysql_errno($database) > 0)
{
// query error - do stuff here
logfile('MySQL: There was an error with the query ->
'.mysql_errno($database).': '.mysql_error($database));
}
else
{
// query good - check for results
$num_results = mysql_num_results($database);
logfile('INFO: Query has been run.');
if ($num_results == 0)
{
// no results returned - do stuff here
logfile('INFO: No results found from query,');
logfile("QUERY: $query");
// by logging the query you can check your values
}
else
{
// there are results - do stuff here
logfile("INFO: Results found -> $num_results");
}
}
....

Norman
---
Avatar Hosting at www.easyavatar.com

[lkrubner]

That is a very good tip. As near as I can see, there was no real error,
I was creating a false one by the way I'd written my error test in PHP.
Please tell me if my theory is false about this. I now take your advice
and try to capture the mysql_error and I put that in the error message.
It comes up blank, suggesting that there has been no mysql error. My
test was this line:

if (is_resource($this->pp_queryid)) {

Can I assume this fails if there were zero rows returned? You can see
the line in context here:

if (stristr($query, 'SELECT') || stristr($query, 'SHOW') ||
stristr($query, 'EXPLAIN') || stristr($query, 'DESCRIBE')) {
$this->pp_queryid = @ mysql_query($query, $this->pp_linkid);
if (is_resource($this->pp_queryid)) {
$this->notifyObject->notify("McDatastoreConnector",
"databaseQuerySuccess");
$this->pp_firstquery = 1;
return $this->pp_queryid;
} else {
$errmsg = 'Query error: ' . mysql_error();
$this->resultsObject->error("In query(), in
McDatastoreConnectorMySql, we were not able to run our query. $errmsg
.. The query was: '$query' .", "McDatastoreConnectorMySql");
$this->notifyObject->notify("McDatastoreConnector",
"databaseQueryError");
$this->error();
}
} else {

[Pedro Graca]

lk******@geocities.com wrote:

if (is_resource($this->pp_queryid)) {

Can I assume this fails if there were zero rows returned?
No.
You can see the line in context here: if (stristr($query, 'SELECT') || stristr($query, 'SHOW') ||
stristr($query, 'EXPLAIN') || stristr($query, 'DESCRIBE')) {
$this->pp_queryid = @ mysql_query($query, $this->pp_linkid);

^^^
Remove this '@' from there and everywhere else it appears in your code.

If the mysql_connect() failed for some reason, you will not know about
it (if it also has an '@') and then you can't expect the mysql_query()
to work.
$conn = mysql_connect() or die('Connection error: ' . mysql_error());

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!