Solved

Empty query result

Posted on 2000-03-13
9
368 Views
Last Modified: 2006-11-17
When I perform an ordianry SELECT query in PHP (on a MYSQL database), I always get an error message when my query returns an empty set. Something like not valid Query. There is no such error message when I execute the same query directly in MYSQL, I just get empty set as result.

The only way I have found to work around this problem is to perform my query with a SELECT COUNT(*) ...  the first time, to check if there is a valid result and to perform the query afterward. Has anybody a better to solve this problem ?

0
Comment
Question by:frey
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 8

Expert Comment

by:us111
ID: 2612362
well use the following class from phplib (http://phplib.netuse.de)
the num_rows() function will return 0 if your query is empty.

class MySQL
                      { var $Host     = "";
                         var $Database = "";
                         var $User     = "";
                      var $Password = "";

                         var $Link_ID  = 0;
                      var $Query_ID = 0;
                         var $Record   = array();
                         var $Row;

                         var $Errno    = 0;
                         var $Error    = "";
                         var $Empty   = true;

                         var $Auto_free = 0;     ## Set this to 1 for automatic mysql_free_result()

                      function MySQL($host, $database, $user = "", $password = "")
                      { $this->Host = $host;
                      $this->Database = $database;
                      $this->User = $user;
                      $this->Password = $password;

                      $this->connect();
                      }

                      function connect()
                      { if ( $this->Link_ID == 0 )
                      { $this->Link_ID = mysql_connect($this->Host, $this->User, $this->Password);
                             if (!$this->Link_ID)
                      { $this->halt("Link-ID ====== false, pconnect failed");
                          }
                           if (!mysql_query(sprintf("use %s",$this->Database),$this->Link_ID))
                      { $this->halt("cannot use database ".$this->Database);
                           }
                          }
                      }

                      function query($Query_String)
                      { $this->connect();
                         $this->Query_ID = mysql_query($Query_String,$this->Link_ID);
                          $this->Row   = 0;

                           $this->Errno = mysql_errno();
                      if ($this-Errno <> 0)
                      $this->Empty = true;

                           $this->Error = mysql_error();

                          if (!$this->Query_ID)
                      { $this->halt("Invalid SQL: ".$Query_String);
                           }

                          return $this->Query_ID;
                      }

                      function next_record()
                      { $this->Record = mysql_fetch_array($this->Query_ID);
                           $this->Row   += 1;
                          $this->Errno = mysql_errno();
                           $this->Error = mysql_error();

                          $stat = is_array($this->Record);
                           if (!$stat && $this->auto_free)
                      { mysql_free_result($this->Query_ID);
                              $this->Query_ID = 0;
                          }
                           return $stat;
                        }

                      function seek($pos)
                      { $status = mysql_data_seek($this->Query_ID, $pos);
                           if ($status)
                            $this->Row = $pos;
                          return;
                      }

                      function metadata($table)
                      { $count = 0;
                           $id    = 0;
                          $res   = array();

                          $this->connect();
                          $id = @mysql_list_fields($this->Database, $table);
                           if ($id < 0)
                      { $this->Errno = mysql_errno();
                              $this->Error = mysql_error();
                             $this->halt("Metadata query failed.");
                          }
                           $count = mysql_num_fields($id);
                           
                          for ($i=0; $i<$count; $i++)
                      { $res[$i]["table"] = mysql_field_table ($id, $i);
                             $res[$i]["name"]  = mysql_field_name  ($id, $i);
                             $res[$i]["type"]  = mysql_field_type  ($id, $i);
                              $res[$i]["len"]   = mysql_field_len   ($id, $i);
                             $res[$i]["flags"] = mysql_field_flags ($id, $i);
                             $res["meta"][$res[$i]["name"]] = $i;
                             $res["num_fields"]= $count;
                           }
                           
                           mysql_free_result($id);
                           return $res;
                      }

                      function affected_rows()
                      { return mysql_affected_rows($this->Link_ID);
                      }

                      function num_rows()
                      { return mysql_num_rows($this->Query_ID);
                      }

                      function num_fields()
                      { return mysql_num_fields($this->Query_ID);
                      }

                      function current_row()
                      { return $this->num_rows();
                      }

                      function field($Name)
                      { return $this->Record[$Name];
                      }

                      function field_value($Name)
                      { return $this->Record[$Name];
                      }

                      function halt($msg)
                      { printf("</td></tr></table><b>Database error:</b> %s<br>\n", $msg);
                          printf("<b>MySQL Error</b>: %s (%s)<br>\n",
                             $this->Errno,
                             $this->Error);
                           die("Session halted.");
                      }
                      }
                      ?>

                      Put this class in a file mysql.php3

                      then

                      <?
                      /* HOW TO USE */
                      include("mysql.php3");

                      $Conn = new MySQL("", "mydatabase");
                      $Conn->query("Select * from Mytable");
                      while($Conn->next_record())
                      { printf("%s  -  %s<bR>",
                          $Conn->field_value("cat0_num"),
                          $Conn->field_value("Fr"));
                      }

                      $a = $Conn->metadata("Categories0");
                      print $a[0]["type"];


                      //$res[$i]["table"] = mysql
                        //     $res[$i]["name"]  = mysql_field_name  ($id, $i);
                      //       $res[$i]["type"]  = mysql_field_type  ($id, $i);
                          //    $res[$i]["len"]   = mysql_field_len   ($id, $i);
                            // $res[$i]["flags"] = mysql_field_flags ($id, $i);
                          //   $res["meta"][$res[$i]["name"]] = $i;
                             // $res["num_fields"]= $count;
                      */
                      ?>
0
 
LVL 2

Expert Comment

by:gravity
ID: 2614252
Even simpler... when you do the query, for example $query = mysql_query($sql), count($query) should return how many rows there are or 0 for none.
0
 
LVL 1

Expert Comment

by:aikon
ID: 2619470
Hi Frey,

You can also try:

//$query is your query ...
$res = mysql_query($query);

if (mysql_fetch_row($res)) {
  // You had found something
}
else {
  // You get anything ...
}

Regards.

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 2

Expert Comment

by:maxkir
ID: 2619657
hi,

If previous comments helped you ?

If no ...

What error message do you get ?

What is the query you get error with ?

0
 

Expert Comment

by:dave5050
ID: 2631039
Probably one of the easiest methods is to do the following:

$res = @mysql_db_query($db, $sql);
if($res) {
 //you havbe result
} else {
 //you don't have result
}

Note: The @ in front of the function name will supress errors being displayed on output:

0
 
LVL 8

Accepted Solution

by:
us111 earned 200 total points
ID: 2631832
It's better to do not lock the question ....
0
 

Expert Comment

by:dave5050
ID: 2631927
dave5050 changed the proposed answer to a comment
0
 
LVL 2

Expert Comment

by:maxkir
ID: 2635091
frey, i suppose you won't get anything better until you clarify your problem ...
0
 

Author Comment

by:frey
ID: 2648477
I think I found the reason of the error message I get, I am runnging PHP 4.0 beta 3 release, I do not get this error with PHP 3.0. Thanks for all your helpfull comments.

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question