PHP unknown_type::query方法代码示例

 function tearDown()
 {
     // Remove created beans.
     foreach ($this->to_remove as $object) {
         if (is_a($object, 'SugarBean')) {
             $table = $object->getTableName();
             $object->mark_deleted($object->id);
             $this->db->query("DELETE FROM {$table} WHERE id = '{$object->id}' ");
             $this->db->query("DELETE FROM {$table}_audit WHERE parent_id = '{$object->id}' ");
             unset($object);
         }
     }
     $this->to_remove = array();
 }

/**
 * 
 * Enter description here ...
 * @param unknown_type $db
 * @param array $bp start ende chr ens_species 
 */
function getSyntenyRegionIDs($db, $bp)
{
    $species = strtolower($bp[3]);
    if ($species == 'mus_musculus') {
        $genome_db_id = 57;
    } elseif ($species == 'rattus_norvegicus') {
        $genome_db_id = 3;
    } elseif ($species == 'homo_sapiens') {
        $genome_db_id = 90;
    }
    $sqlDnafrag = 'SELECT dfr.synteny_region_id FROM dnafrag_region as dfr INNER JOIN 
	dnafrag as df ON (dfr.dnafrag_start <= ' . $bp[1] . ' AND 
	dfr.dnafrag_end >= ' . $bp[0] . ' AND 
	dfr.dnafrag_id = df.dnafrag_id AND 
	df.name = "' . $bp[2] . '" AND 
	df.genome_db_id = ' . $genome_db_id . ');';
    $fragQuery = $db->query($sqlDnafrag) or fatal_error('Query failed: ' . $db->error);
    //$frag_table = $fragQuery->fetch_all();
    $str = "";
    $first = true;
    while ($row = $fragQuery->fetch_assoc()) {
        if ($first) {
            $str .= $row['synteny_region_id'];
            $first = false;
        } else {
            $str .= "," . $row['synteny_region_id'];
        }
    }
    $fragQuery->close();
    return $str;
}

 /**
  * (non-PHPdoc)
  * @see IBrowserService::searchAll()
  */
 public function searchAll($search, $columns, $offset, $num, $sort, $sortDirection = 'asc', $isCount = false)
 {
     $whereSqlParts = array();
     foreach ($columns as $column) {
         // get db column name
         $columnName = $this->propertyToColumnMapping[$column];
         $whereSqlParts[] = "{$column} LIKE '%{$search}%'";
     }
     // build where clause for all columns we are searching against
     $whereSql = implode($whereSqlParts, ' OR ');
     // check if we just need a count of results
     if ($isCount) {
         $query = "SELECT count(*) as count FROM browsers WHERE {$whereSql}";
         $statement = $this->connection->query($query);
         $result = $statement->fetch();
         return $result['count'];
     } else {
         $sort = $this->propertyToColumnMapping[$sort];
         $query = "SELECT *\n  \t\t\t\tFROM\n  \t\t\t\t  browsers\n  \t\t\t  \tWHERE\n  \t\t\t      {$whereSql}\n  \t\t\t  \tORDER BY \n  \t\t\t      {$sort} {$sortDirection}\n  \t\t\t  \tLIMIT\n  \t\t\t      {$offset}, {$num}";
         $statement = $this->connection->query($query);
         $results = $statement->fetchAll();
         $browsers = $this->hydrateResults($results);
         return $browsers;
     }
 }

 /**
  * Actualiza el perfil del usuario registrado en el sitio. Los datos
  * del perfil actualizados son: correo e idioma.
  */
 public function updateProfile()
 {
     try {
         // se obtiene el correo del usuario
         $email = JUser::getInstance(JFactory::getUser()->id)->email;
         // se establece el valor del idioma
         $language = JFactory::getUser()->getParam('language');
         if (strcmp($language, 'ca-ES') == 0) {
             $language = 'catalan';
         } else {
             if (strcmp($language, 'es-ES') == 0) {
                 $language = 'spanish';
             } else {
                 $language = 'english';
             }
         }
         // se obtiene el nombre del usuario
         $username = $this->currentUser;
         // se actualiza el idioma
         $query = str_replace('%language%', $language, getQuery('updateLanguage'));
         $query = str_replace('%username%', $username, $query);
         $resultQuery = $this->proxyMySql->query($query);
         // se actualiza el correo
         $query = str_replace('%email%', $email, getQuery('updateEmail'));
         $query = str_replace('%username%', $username, $query);
         $resultQuery = $this->proxyMySql->query($query);
     } catch (Exception $e) {
     }
 }

/**
 * @name function generarUbicacion
 * @param unknown_type $db  coneccion
 * @param entero $codigo  
 * @param entero $bandera
 * @return String
 */
function generarUbicacion($db, $codigo, $bandera)
{
    $query = "select SGD_EIT_NOMBRE,SGD_EIT_CODIGO,SGD_EIT_COD_PADRE,CODI_DPTO,CODI_MUNI from SGD_EIT_ITEMS where SGD_EIT_CODIGO= '{$codigo}' order by SGD_EIT_NOMBRE";
    $rs = $db->query($query);
    $son = explode(" ", $rs->fields["SGD_EIT_NOMBRE"]);
    if ($rs->fields["SGD_EIT_COD_PADRE"] == 0) {
        $query = "select distinct(m.MUNI_NOMB),m.MUNI_CODI FROM MUNICIPIO m , SGD_EIT_ITEMS i WHERE m.MUNI_CODI=i.CODI_MUNI and DPTO_CODI='" . $rs->fields['CODI_DPTO'] . "'  and MUNI_CODI='" . $rs->fields['CODI_MUNI'] . "'  ORDER BY MUNI_NOMB";
        $rs1 = $db->query($query);
        $queryDpto = "select distinct(d.DPTO_NOMB),d.DPTO_CODI FROM DEPARTAMENTO d, SGD_EIT_ITEMS i WHERE d.DPTO_CODI=i.CODI_DPTO and i.CODI_DPTO=" . $rs->fields['CODI_DPTO'] . "ORDER BY DPTO_NOMB";
        $rs2 = $db->query($queryDpto);
        $depto = "<TABLE width='100%' border=0><tr><td width='25%' class='titulos2'>DEPARTAMENTO</td> <td width='25%' class='titulos2'>" . $rs2->fields["DPTO_NOMB"] . "</td>";
        $muni = "<td width='25%' class='titulos2'>MUNICIPIO</td> <td width='25%' class='titulos2'>" . $rs1->fields["MUNI_NOMB"] . "</td></TR>";
        $edificio = "<tr><td width='25%' class='titulos2'>EDIFICIO</td> <td width='25%' class='titulos2'> <input type=\"hidden\" name=\"exp_edificio2\" id=\"exp_edificio2\" value='" . $rs->fields["SGD_EIT_CODIGO"] . "'>" . $rs->fields["SGD_EIT_NOMBRE"] . " \n\t\t\n\t\t\n\t\t</td>";
        $option[0] = $depto . $muni . $edificio;
        $option[1] = 2;
        return $option;
    } else {
        $ban = $bandera + 1;
        $option = generarUbicacion($db, $rs->fields["SGD_EIT_COD_PADRE"], $ban);
        //$son=explode(" ",$rs->fields["SGD_EIT_NOMBRE"]);
        //substr('abcdef', 0, 4);
        if (substr($son[0], 0, 4) == 'PISO' || substr($son[0], 0, 6) == 'SOTANO') {
            $option[0] .= "<td width='25%' class='titulos2'>PISO</td> <td width='25%' class='titulos2'>" . $rs->fields["SGD_EIT_NOMBRE"] . "<input type=\"hidden\" name=\"exp_piso2\" id=\"exp_piso2\" value='" . $rs->fields["SGD_EIT_CODIGO"] . "' /></td></tr> \n";
            $option[1] = 1;
            return $option;
        } else {
            if ($option[1] == 1) {
                $option[0] .= "<tr>";
                $opt = 2;
            }
            $option[0] .= "<td width='25%' class='titulos2'>" . $son[0] . "</td> <td width='25%' class='titulos2'> " . $rs->fields["SGD_EIT_NOMBRE"] . "</td>";
            if ($option[1] == 2) {
                $option[0] .= "</tr>";
                $opt = 1;
            }
            if ($bandera == 0) {
                if ($option[1] == 1) {
                    $option[0] .= "</tr>";
                }
                return $option[0] . "</table>";
            } else {
                $option[1] = $opt;
                return $option;
            }
        }
    }
}

 /**
  * 获取$sql查询中结果的总条数
  * @param unknown_type $sql
  * @return number
  */
 public function count_query($sql)
 {
     $res = $this->db->query($sql)->result();
     if (empty($res)) {
         return 0;
     }
     return count($res);
 }

 /**
  * Create Term and Taxonomy Relationships.
  *
  * Relates an object (post, link etc) to a term and taxonomy type. Creates the
  * term and taxonomy relationship if it doesn't already exist. Creates a term if
  * it doesn't exist (using the slug).
  *
  * A relationship means that the term is grouped in or belongs to the taxonomy.
  * A term has no meaning until it is given context by defining which taxonomy it
  * exists under.
  *
  * @package NXTClass
  * @subpackage Taxonomy
  * @since 2.3.0
  *
  * @param int $object_id The object to relate to.
  * @param array|int|string $term The slug or id of the term, will replace all existing
  * related terms in this taxonomy.
  * @param array|string $taxonomy The context in which to relate the term to the object.
  * @param bool $append If false will delete difference of terms.
  * @return array|nxt_Error Affected Term IDs
  */
 function set_object_terms($object_id, $terms, $taxonomy, $append = false)
 {
     $object_id = (int) $object_id;
     if (!$this->is_taxonomy($taxonomy)) {
         return new nxt_Error('invalid_taxonomy', __('Invalid Taxonomy'));
     }
     if (!is_array($terms)) {
         $terms = array($terms);
     }
     if (!$append) {
         $old_tt_ids = $this->get_object_terms($object_id, $taxonomy, array('fields' => 'tt_ids', 'orderby' => 'none'));
     }
     $tt_ids = array();
     $term_ids = array();
     foreach ((array) $terms as $term) {
         if (!strlen(trim($term))) {
             continue;
         }
         if (!($id = $this->is_term($term, $taxonomy))) {
             $id = $this->insert_term($term, $taxonomy);
         }
         if (is_nxt_error($id)) {
             return $id;
         }
         $term_ids[] = $id['term_id'];
         $id = $id['term_taxonomy_id'];
         $tt_ids[] = $id;
         if ($this->db->get_var($this->db->prepare("SELECT term_taxonomy_id FROM {$this->db->term_relationships} WHERE object_id = %d AND term_taxonomy_id = %d", $object_id, $id))) {
             continue;
         }
         $this->db->insert($this->db->term_relationships, array('object_id' => $object_id, 'term_taxonomy_id' => $id));
     }
     $this->update_term_count($tt_ids, $taxonomy);
     if (!$append) {
         $delete_terms = array_diff($old_tt_ids, $tt_ids);
         if ($delete_terms) {
             $in_delete_terms = "'" . implode("', '", $delete_terms) . "'";
             $this->db->query($this->db->prepare("DELETE FROM {$this->db->term_relationships} WHERE object_id = %d AND term_taxonomy_id IN ({$in_delete_terms})", $object_id));
             $this->update_term_count($delete_terms, $taxonomy);
         }
     }
     $t = $this->get_taxonomy($taxonomy);
     if (!$append && isset($t->sort) && $t->sort) {
         $values = array();
         $term_order = 0;
         $final_tt_ids = $this->get_object_terms($object_id, $taxonomy, 'fields=tt_ids');
         foreach ($tt_ids as $tt_id) {
             if (in_array($tt_id, $final_tt_ids)) {
                 $values[] = $this->db->prepare("(%d, %d, %d)", $object_id, $tt_id, ++$term_order);
             }
         }
         if ($values) {
             $this->db->query("INSERT INTO {$this->db->term_relationships} (object_id, term_taxonomy_id, term_order) VALUES " . join(',', $values) . " ON DUPLICATE KEY UPDATE term_order = VALUES(term_order)");
         }
     }
     do_action('set_object_terms', $object_id, $terms, $tt_ids, $taxonomy, $append);
     return $tt_ids;
 }

 /**
  * The construct get all allowedsites in the beginn..
  * @param unknown_type $connection
  */
 public function __construct($connection)
 {
     $query = $connection->query('SELECT * FROM `config_loginneedlesssites`');
     echo $query;
     if ($query != "") {
         foreach ($query as $siterow) {
             $allowedSite = new allowedSite();
             $allowedSite->setId($siterow['id']);
             $allowedSite->setName($siterow['site']);
             array_push($this->allAllowedSites, $allowedSite);
         }
     }
 }

/**
 * SELAA 12 UUSINTA PROJEKTIA!
 * Hakee annetusta kannasta enintään 12 uusinta projektia. 
 * @param unknown_type $DBH
 * @return $projektit taulukko
 */
function getNewProjects($DBH)
{
    try {
        //Haetaan 12 uusinta projektia
        $projektit = array();
        //Taulukko projekti-olioille
        $STH = $DBH->query("SELECT projekti.nimi, projekti.kuva \n\t\t\t\t            FROM projekti\n\t\t\t\t            ORDER BY projekti.pvm DESC LIMIT 12");
        //DESC,ASC
        $STH->setFetchMode(PDO::FETCH_OBJ);
        //yksi rivi objektina
        return $projektit;
    } catch (PDOException $e) {
        //Kirjoitetaan mahdollinen virheviesti tiedostoon
        file_put_contents('log/DBErrors.txt', 'selaa.php: ' . $e->getMessage() . "\n", FILE_APPEND);
        return false;
    }
}

 /**
  * Show columns of current table
  *
  * @return Array
  */
 public function queryColumns()
 {
     if (empty(self::$cache[static::$table])) {
         // temporaire...
         if ($this->db->getDriver() == 'mysql') {
             $s = $this->db->query("SHOW COLUMNS FROM " . static::$table);
         } elseif ($this->db->getDriver() == 'pgsql') {
             $s = $this->db->prepare("SELECT column_name AS field FROM information_schema.columns WHERE table_name=?");
             $s->execute(array(static::$table));
         }
         if ($s->rowCount() <= 0) {
             throw new LiException('La table ' . $table . ' ne contient aucune colonne');
         }
         $rs = $s->fetchAll();
         $fields = array();
         foreach ($rs as $row) {
             $fields[] = $row['field'];
         }
         self::$cache[static::$table] = $fields;
     }
     return self::$cache[static::$table];
 }

/**
 * get the homologue ensemble ids of the target species to a given set of ensebl ids.
 *
 * @param unknown_type $compara
 * @param unknown_type $unique_ids
 * @param $target_species_name the name of the target species for filtering (speed up)
 * @author g 2011.04.26
 */
function get_homologue_ens_ids($compara, $unique_ids, $target_species_name)
{
    $sql = 'SELECT m.stable_id, m2.stable_id, hom.description
		FROM member AS m 
		INNER JOIN homology_member AS h ON (
			m.member_id = h.member_id
			AND m.genome_db_id = (SELECT genome_db_id FROM genome_db WHERE name="' . $target_species_name . '") 
		) INNER join homology AS hom ON (
			h.homology_id = hom.homology_id 
		) INNER join homology_member AS h2 on (
			h.homology_id = h2.homology_id
		) INNER join member AS m2 on (
			m2.member_id = h2.member_id AND m2.stable_id IN ("' . implode('","', $unique_ids) . '")
		) GROUP BY m.stable_id, m2.stable_id;';
    $result = $compara->query($sql) or fatal_error($compara->error);
    $homology = array_combine($unique_ids, array_fill(0, count($unique_ids), array()));
    while ($row = $result->fetch_row()) {
        $homology[$row[1]][$row[0]] = $row[2];
    }
    $result->close();
    return $homology;
}

 /**
  * Resolve a username with a id..
  * @param unknown_type $userid
  * @param unknown_type $connection
  */
 public static function getUserById($userid, $connection)
 {
     foreach ($connection->query('SELECT * FROM user WHERE uid="' . $userid . '";') as $userrow) {
         return $userrow['username'];
     }
 }

 /**
  * Get all the roles
  * @param unknown_type $connection
  */
 public static function getRoles($connection)
 {
     $tmp = $connection->query("SELECT * FROM role;");
     return $tmp->fetchAll();
 }