SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using Classes for MySQL Queries.

    I'm adapting a colleagues code to incorporate finding duplicate email subscribers - he uses classes and I don't -

    I have the following code which uses classes and I don't know how to execute it (do the result):

    Code:
    		$query = "SELECT email, list_id FROM subscriber WHERE email = '$lc_email' AND list_id='$LISTID' LIMIT 1";
    		$q->query($query);
    I've got $q->query($query) - but I don't know if it has a duplicate or not.

    Any ideas would be appreciated.

    Thanks.

  2. #2
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...need to see the class to be able to help as there are many possible ways the class could have been written, unless its a known class like PEAR:B etc then its guesswork without seeing the class.

  3. #3
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this is it, I don't understand it though:

    Code:
    class DB_Sql {
    
      /* public: connection parameters */
      var $Host     = "localhost";
      var $Database = "************";
      var $User     = "************";
      var $Password = "************";
    
      /* public: configuration parameters */
      var $Auto_Free     = 0;     ## Set to 1 for automatic mysql_free_result()
      var $Debug         = 0;     ## Set to 1 for debugging messages.
      var $Halt_On_Error = "yes"; ## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
      var $Seq_Table     = "db_sequence";
    
      /* public: result array and current row number */
      var $Record   = array();
      var $Row;
    
      /* public: current error number and error text */
      var $Errno    = 0;
      var $Error    = "";
    
      /* public: this is an api revision, not a CVS revision. */
      var $type     = "mysql";
      var $revision = "1.2";
    
      /* private: link and query handles */
      var $Link_ID  = 0;
      var $Query_ID = 0;
    
    
    
      /* public: constructor */
      function DB_Sql($query = "") {
          $this->query($query);
      }
    
      /* public: some trivial reporting */
      function link_id() {
        return $this->Link_ID;
      }
    
      function query_id() {
        return $this->Query_ID;
      }
    
      /* public: connection management */
      function connect($Database = "", $Host = "", $User = "", $Password = "") {
        /* Handle defaults */
        if ("" == $Database)
          $Database = $this->Database;
        if ("" == $Host)
          $Host     = $this->Host;
        if ("" == $User)
          $User     = $this->User;
        if ("" == $Password)
          $Password = $this->Password;
    
        /* establish connection, select database */
        if ( 0 == $this->Link_ID ) {
    
          $this->Link_ID=mysql_pconnect($Host, $User, $Password);
          if (!$this->Link_ID) {
            $this->halt("pconnect($Host, $User, \$Password) failed.");
            return 0;
          }
    
          if (!@mysql_select_db($Database,$this->Link_ID)) {
            $this->halt("cannot use database ".$this->Database);
            return 0;
          }
        }
    
        return $this->Link_ID;
      }
    
      /* public: discard the query result */
      function free() {
          @mysql_free_result($this->Query_ID);
          $this->Query_ID = 0;
      }
    
      /* public: perform a query */
      function query($Query_String) {
        /* No empty queries, please, since PHP4 chokes on them. */
        if ($Query_String == "")
          /* The empty query string is passed on from the constructor,
           * when calling the class without a query, e.g. in situations
           * like these: '$db = new DB_Sql_Subclass;'
           */
          return 0;
    
        if (!$this->connect()) {
          return 0; /* we already complained in connect() about that. */
        };
    
        # New query, discard previous result.
        if ($this->Query_ID) {
          $this->free();
        }
    
        if ($this->Debug)
          printf("Debug: query = %s<br>\n", $Query_String);
    
        $this->Query_ID = @mysql_query($Query_String,$this->Link_ID);
        $this->Row   = 0;
        $this->Errno = mysql_errno();
        $this->Error = mysql_error();
        if (!$this->Query_ID) {
          $this->halt("Invalid SQL: ".$Query_String);
        }
    
        # Will return nada if it fails. That's fine.
        return $this->Query_ID;
      }
    
      /* public: walk result set */
      function next_record() {
        if (!$this->Query_ID) {
          $this->halt("next_record called with no query pending.");
          return 0;
        }
    
        $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) {
          $this->free();
        }
        return $stat;
      }
    
      /* public: position in result set */
      function seek($pos = 0) {
        $status = @mysql_data_seek($this->Query_ID, $pos);
        if ($status)
          $this->Row = $pos;
        else {
          $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");
    
          /* half assed attempt to save the day,
           * but do not consider this documented or even
           * desireable behaviour.
           */
          @mysql_data_seek($this->Query_ID, $this->num_rows());
          $this->Row = $this->num_rows;
          return 0;
        }
    
        return 1;
      }
    
      /* public: table locking */
      function lock($table, $mode="write") {
        $this->connect();
    
        $query="lock tables ";
        if (is_array($table)) {
          while (list($key,$value)=each($table)) {
            if ($key=="read" && $key!=0) {
              $query.="$value read, ";
            } else {
              $query.="$value $mode, ";
            }
          }
          $query=substr($query,0,-2);
        } else {
          $query.="$table $mode";
        }
        $res = @mysql_query($query, $this->Link_ID);
        if (!$res) {
          $this->halt("lock($table, $mode) failed.");
          return 0;
        }
        return $res;
      }
    
      function unlock() {
        $this->connect();
    
        $res = @mysql_query("unlock tables");
        if (!$res) {
          $this->halt("unlock() failed.");
          return 0;
        }
        return $res;
      }
    
    
      /* public: evaluate the result (size, width) */
      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);
      }
    
      /* public: shorthand notation */
      function nf() {
        return $this->num_rows();
      }
    
      function np() {
        print $this->num_rows();
      }
    
      function f($Name) {
        return $this->Record[$Name];
      }
    
      function p($Name) {
        print $this->Record[$Name];
      }
    
      /* public: sequence numbers */
      function nextid($seq_name) {
        $this->connect();
    
        if ($this->lock($this->Seq_Table)) {
          /* get sequence number (locked) and increment */
          $q  = sprintf("select nextid from %s where seq_name = '%s'",
                    $this->Seq_Table,
                    $seq_name);
          $id  = @mysql_query($q, $this->Link_ID);
          $res = @mysql_fetch_array($id);
    
          /* No current value, make one */
          if (!is_array($res)) {
            $currentid = 0;
            $q = sprintf("insert into %s values('%s', %s)",
                     $this->Seq_Table,
                     $seq_name,
                     $currentid);
            $id = @mysql_query($q, $this->Link_ID);
          } else {
            $currentid = $res["nextid"];
          }
          $nextid = $currentid + 1;
          $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
                   $this->Seq_Table,
                   $nextid,
                   $seq_name);
          $id = @mysql_query($q, $this->Link_ID);
          $this->unlock();
        } else {
          $this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
          return 0;
        }
        return $nextid;
      }
    
      /* public: return table metadata */
      function metadata($table='',$full=false) {
        $count = 0;
        $id    = 0;
        $res   = array();
    
        /*
         * Due to compatibility problems with Table we changed the behavior
         * of metadata();
         * depending on $full, metadata returns the following values:
         *
         * - full is false (default):
         * $result[]:
         *   [0]["table"]  table name
         *   [0]["name"]   field name
         *   [0]["type"]   field type
         *   [0]["len"]    field length
         *   [0]["flags"]  field flags
         *
         * - full is true
         * $result[]:
         *   ["num_fields"] number of metadata records
         *   [0]["table"]  table name
         *   [0]["name"]   field name
         *   [0]["type"]   field type
         *   [0]["len"]    field length
         *   [0]["flags"]  field flags
         *   ["meta"][field name]  index of field named "field name"
         *   The last one is used, if you have a field name, but no index.
         *   Test:  if (isset($result['meta']['myfield'])) { ...
         */
    
        // if no $table specified, assume that we are working with a query
        // result
        if ($table) {
          $this->connect();
          $id = @mysql_list_fields($this->Database, $table);
          if (!$id)
            $this->halt("Metadata query failed.");
        } else {
          $id = $this->Query_ID;
          if (!$id)
            $this->halt("No query specified.");
        }
    
        $count = @mysql_num_fields($id);
    
        // made this IF due to performance (one if is faster than $count if's)
        if (!$full) {
          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);
          }
        } else { // full
          $res["num_fields"]= $count;
    
          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;
          }
        }
    
        // free the result only if we were called on a table
        if ($table) @mysql_free_result($id);
        return $res;
      }
    
      /* private: error handling */
      function halt($msg) {
        $this->Error = @mysql_error($this->Link_ID);
        $this->Errno = @mysql_errno($this->Link_ID);
        if ($this->Halt_On_Error == "no")
          return;
    
        $this->haltmsg($msg);
    
        if ($this->Halt_On_Error != "report")
          die("Session halted.");
      }
    
      function haltmsg($msg) {
        $file = fopen("/usr/local/home/httpd/vhtdocs/************", "a");
        $line = date("Ymd H:i:s") . " " . $GLOBALS["REMOTE_ADDR"] . " " . $GLOBALS["SCRIPT_NAME"] . " " . $this->Errno . " " . $this->Error . " " . $msg . "\n";
        fputs($file, $line);
        fclose($file);
    
       # printf("</td></tr></table><b>We were unable to complete the Database request at this moment - please contact webmaster </b>\n");
        # 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);
      }
    
      function table_names() {
        $this->query("SHOW TABLES");
        $i=0;
        while ($info=mysql_fetch_row($this->Query_ID))
         {
          $return[$i]["table_name"]= $info[0];
          $return[$i]["tablespace_name"]=$this->Database;
          $return[$i]["database"]=$this->Database;
          $i++;
         }
       return $return;
      }
    }

  4. #4
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok,
    PHP Code:
    <?php
    $q
    ->query("SELECT * FROM $table WHERE $blah") ;
    echo 
    $q->num_rows();
    ?>
    to be fair that was guessable [img]images/smilies/smile.gif[/img] ..

    errr do you have to use that class ??

  5. #5
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As I say, I didn't write it, I didn't make it, but it was made for some business and the guy who wrote it doesn't have the time to fix it.

    Thanks for your help, hopefully I will resolve the situation quicker.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •