Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Empty query result

Posted on 2000-03-13
9
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 800 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
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.

661 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