Solved

Empty query result

Posted on 2000-03-13
9
365 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Expert Comment

by:maxkir
Comment Utility
hi,

If previous comments helped you ?

If no ...

What error message do you get ?

What is the query you get error with ?

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:dave5050
Comment Utility
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
Comment Utility
It's better to do not lock the question ....
0
 

Expert Comment

by:dave5050
Comment Utility
dave5050 changed the proposed answer to a comment
0
 
LVL 2

Expert Comment

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

Author Comment

by:frey
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now