Solved

Empty query result

Posted on 2000-03-13
9
369 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

821 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