PHP sql_query函数代码示例

/**
|--------------------------------------------------------------------------|
|   https://github.com/Bigjoos/                			    |
|--------------------------------------------------------------------------|
|   Licence Info: GPL			                                    |
|--------------------------------------------------------------------------|
|   Copyright (C) 2010 U-232 V5					    |
|--------------------------------------------------------------------------|
|   A bittorrent tracker source based on TBDev.net/tbsource/bytemonsoon.   |
|--------------------------------------------------------------------------|
|   Project Leaders: Mindless, Autotron, whocares, Swizzles.					    |
|--------------------------------------------------------------------------|
 _   _   _   _   _     _   _   _   _   _   _     _   _   _   _
/ \ / \ / \ / \ / \   / \ / \ / \ / \ / \ / \   / \ / \ / \ / \
( U | - | 2 | 3 | 2 )-( S | o | u | r | c | e )-( C | o | d | e )
\_/ \_/ \_/ \_/ \_/   \_/ \_/ \_/ \_/ \_/ \_/   \_/ \_/ \_/ \_/
*/
function docleanup($data)
{
    global $INSTALLER09, $queries, $mc1;
    set_time_limit(1200);
    ignore_user_abort(1);
    //== Delete inactive user accounts
    $secs = 350 * 86400;
    $dt = TIME_NOW - $secs;
    $maxclass = UC_STAFF;
    sql_query("SELECT FROM users WHERE parked='no' AND status='confirmed' AND class < {$maxclass} AND last_access < {$dt}");
    //== Delete parked user accounts
    $secs = 675 * 86400;
    // change the time to fit your needs
    $dt = TIME_NOW - $secs;
    $maxclass = UC_STAFF;
    sql_query("SELECT FROM users WHERE parked='yes' AND status='confirmed' AND class < {$maxclass} AND last_access < {$dt}");
    if ($queries > 0) {
        write_log("Inactive Clean -------------------- Inactive Clean Complete using {$queries} queries--------------------");
    }
    if (false !== mysqli_affected_rows($GLOBALS["___mysqli_ston"])) {
        $data['clean_desc'] = mysqli_affected_rows($GLOBALS["___mysqli_ston"]) . " items deleted/updated";
    }
    if ($data['clean_log']) {
        cleanup_log($data);
    }
}

function popular($skin_dir = 'basic', $pop_cnt = 7, $date_cnt = 3)
{
    global $config, $g5;
    if (!$skin_dir) {
        $skin_dir = 'basic';
    }
    $date_gap = date("Y-m-d", G5_SERVER_TIME - $date_cnt * 86400);
    $sql = " select pp_word, count(*) as cnt from {$g5['popular_table']} where pp_date between '{$date_gap}' and '" . G5_TIME_YMD . "' group by pp_word order by cnt desc, pp_word limit 0, {$pop_cnt} ";
    $result = sql_query($sql);
    for ($i = 0; $row = sql_fetch_array($result); $i++) {
        $list[$i] = $row;
        // 스크립트등의 실행금지
        $list[$i]['pp_word'] = get_text($list[$i]['pp_word']);
    }
    ob_start();
    if (G5_IS_MOBILE) {
        $popular_skin_path = G5_MOBILE_PATH . '/' . G5_SKIN_DIR . '/popular/' . $skin_dir;
        $popular_skin_url = G5_MOBILE_URL . '/' . G5_SKIN_DIR . '/popular/' . $skin_dir;
    } else {
        $popular_skin_path = G5_SKIN_PATH . '/popular/' . $skin_dir;
        $popular_skin_url = G5_SKIN_URL . '/popular/' . $skin_dir;
    }
    include_once $popular_skin_path . '/popular.skin.php';
    $content = ob_get_contents();
    ob_end_clean();
    return $content;
}

function check_bans($ip, &$reason = '')
{
    global $INSTALLER09, $mc1;
    $key = 'bans:::' . $ip;
    if (($ban = $mc1->get_value($key)) === false) {
        $nip = ip2long($ip);
        $ban_sql = sql_query('SELECT comment FROM bans WHERE (first <= ' . $nip . ' AND last >= ' . $nip . ') LIMIT 1');
        if (mysqli_num_rows($ban_sql)) {
            $comment = mysqli_fetch_row($ban_sql);
            $reason = 'Manual Ban (' . $comment[0] . ')';
            $mc1->cache_value($key, $reason, 86400);
            // 86400 // banned
            return true;
        }
        mysqli_free_result($ban_sql) || is_object($ban_sql) && get_class($ban_sql) == "mysqli_result" ? true : false;
        $mc1->cache_value($key, 0, 86400);
        // 86400 // not banned
        return false;
    } elseif (!$ban) {
        return false;
    } else {
        $reason = $ban;
        return true;
    }
}

function scan_area($sector, $range)
{
    $result = sql_query("SELECT * FROM s_sectors WHERE id={$sector}");
    $sector = sql_fetchrow($result);
    echo "<table align=center border=1>\n";
    echo "  <tr><th colspan=4>Scanning from origin " . $sector['name'] . " ( " . $sector['distance'] . " / " . $sector['angle'] . " ) @ " . $range . " lightyears</th></tr>\n";
    echo "  <tr><td>Name</td><td>Race</td><td>D / A</td><td>Range</td></tr>\n";
    $result = sql_query("SELECT * FROM g_vessels ORDER BY user_id");
    while ($vessel = sql_fetchrow($result)) {
        $result2 = sql_query("SELECT * FROM s_species WHERE user_id = " . $vessel['user_id']);
        $race = sql_fetchrow($result2);
        if ($vessel['sector_id'] == 0) {
            $distance = calc_distance($sector['distance'], $sector['angle'], $vessel['distance'], $vessel['angle']);
            if ($distance <= $range) {
                echo "<tr><td>" . $vessel['name'] . " (" . $vessel['sector_id'] . ")</td><td>" . $race['name'] . "</td><td>" . $vessel['distance'] . " / " . $vessel['angle'] . "</td><td>" . $distance . "</td></tr>\n";
            }
        } else {
            $result2 = sql_query("SELECT * FROM s_sectors WHERE id=" . $vessel['sector_id']);
            $vessel_sector = sql_fetchrow($result2);
            $distance = calc_distance($sector['distance'], $sector['angle'], $vessel_sector['distance'], $vessel_sector['angle']);
            if ($distance <= $range) {
                echo "<tr><td>" . $vessel['name'] . " (" . $vessel['sector_id'] . ")</td><td>" . $race['name'] . "</td><td>SECTOR: " . $vessel_sector['distance'] . " / " . $vessel_sector['angle'] . "</td><td>" . $distance . "</td></tr>\n";
            }
        }
    }
    print "</table>";
    print "<br><br>";
    return;
}

function show_owned_convoys($user_id)
{
    assert(is_numeric($user_id));
    echo "<table border=1 align=center>";
    echo "  <tr>";
    echo "    <th>Convoy Name</th>";
    echo "    <th>Flag Ship</th>";
    echo "    <th>Ships</th>";
    echo "    <th>Status</th>";
    echo "  </tr>";
    $result = sql_query("SELECT c.* FROM s_convoys c, s_vessels v WHERE c.vessel_id=v.id AND v.user_id=" . $user_id);
    while ($convoy = sql_fetchrow($result)) {
        // Get the flag ship of the convoy
        $vesseltype = vessel_get_vessel_type($convoy['id']);
        // Count the number of ships
        $result2 = sql_query("SELECT c.* FROM s_convoys c, s_vessels v WHERE c.vessel_id=v.id AND v.user_id=" . $user_id);
        $tmp = csl_create_array($result2, "vessel_ids");
        $shipcount = count($tmp);
        // Get the status of the convoy
        $status = $convoy['status'];
        echo "<tr>";
        echo "<td>&nbsp;" . $convoy['name'] . "&nbsp;</td>";
        echo "<td>&nbsp;" . $vesseltype['name'] . "&nbsp;</td>";
        echo "<td>&nbsp;" . $shipcount . "&nbsp;</td>";
        echo "<td>&nbsp;" . $status . "&nbsp;</td>";
        echo "</tr>";
    }
    echo "</table>";
}

 protected function getDateClassrooms($id_date)
 {
     $query = "SELECT DISTINCT classroom" . " FROM %lms_course_date_day" . " WHERE id_date = " . $id_date;
     $result = sql_query($query);
     $array_classroom = array();
     while (list($id_classroom) = sql_fetch_row($result)) {
         $array_classroom[$id_classroom] = $id_classroom;
     }
     $res = '';
     $first = true;
     if (isset($array_classroom[0])) {
         $first = false;
         $res .= Lang::t('_NOT_ASSIGNED', 'admin_date');
     }
     $query = "SELECT name" . " FROM %lms_classroom" . " WHERE idClassroom IN (" . implode(',', $array_classroom) . ")" . " ORDER BY name";
     $result = sql_query($query);
     while (list($name) = sql_fetch_row($result)) {
         if ($first) {
             $first = false;
             $res .= $name;
         } else {
             $res .= ', ' . $name;
         }
     }
     return $res;
 }

function maj_vieille_base_1927_create() {
  global $tables_principales, $tables_auxiliaires, $tables_images, $tables_sequences, $tables_documents, $tables_mime;

	// ne pas revenir plusieurs fois (si, au contraire, il faut pouvoir
	// le faire car certaines mises a jour le demandent explicitement)
	# static $vu = false;
	# if ($vu) return; else $vu = true;

	foreach($tables_principales as $k => $v)
		spip_create_vieille_table($k, $v['field'], $v['key'], true);

	foreach($tables_auxiliaires as $k => $v)
		spip_create_vieille_table($k, $v['field'], $v['key'], false);

	foreach($tables_images as $k => $v)
		sql_query("INSERT IGNORE INTO spip_types_documents (extension, inclus, titre, id_type) VALUES ('$k', 'image', '" .
			      (is_numeric($v) ?
			       (strtoupper($k) . "', $v") :
			       "$v', 0") .
			      ")");

	foreach($tables_sequences as $k => $v)
		sql_query("INSERT IGNORE INTO spip_types_documents (extension, titre, inclus) VALUES ('$k', '$v', 'embed')");

	foreach($tables_documents as $k => $v)
		sql_query("INSERT IGNORE INTO spip_types_documents (extension, titre, inclus) VALUES ('$k', '$v', 'non')");

	foreach ($tables_mime as $extension => $type_mime)
	  sql_query("UPDATE spip_types_documents
		SET mime_type='$type_mime' WHERE extension='$extension'");
}

function docleanup($data)
{
    global $INSTALLER09, $queries;
    set_time_limit(1200);
    ignore_user_abort(1);
    $sql = sql_query("SHOW TABLE STATUS FROM {$INSTALLER09['mysql_db']}");
    $oht = '';
    while ($row = mysqli_fetch_assoc($sql)) {
        if ($row['Data_free'] > 100) {
            $oht .= $row['Data_free'] . ',';
        }
    }
    $oht = rtrim($oht, ',');
    if ($oht != '') {
        $sql = sql_query("OPTIMIZE TABLE {$oht}");
    }
    if ($queries > 0) {
        write_log("Auto-optimizedb--------------------Auto Optimization Complete using {$queries} queries --------------------");
    }
    if ($oht != '') {
        $data['clean_desc'] = "MySQLCleanup optimized {$oht} table(s)";
    }
    if ($data['clean_log']) {
        cleanup_log($data);
    }
}

function preset_show_all_presets($user_id)
{
    assert(is_numeric($user_id));
    echo "<table align=center widht=75%>\n";
    echo "  <tr class=wb>\n";
    echo "    <th>Preset name</th>\n";
    echo "    <th>Distance</th>\n";
    echo "    <th>Angle</th>\n";
    echo "    <th>&nbsp;</th>\n";
    echo "  </tr>\n";
    // Get all presets
    $result = sql_query("SELECT * FROM g_presets WHERE user_id=" . $user_id);
    while ($preset = sql_fetchrow($result)) {
        echo "  <tr class=bl>\n";
        echo "    <td>&nbsp;" . $preset['name'] . "&nbsp;</td>\n";
        echo "    <td>&nbsp;" . $preset['distance'] . "&nbsp;</td>\n";
        echo "    <td>&nbsp;" . $preset['angle'] . "&nbsp;</td>\n";
        echo "    <td>&nbsp;[ <a href=vesselpreset.php?cmd=" . encrypt_get_vars("delete") . "&uid=" . encrypt_get_vars($user_id) . "&pid=" . encrypt_get_vars($preset['id']) . ">Delete</a> ]&nbsp;</td>\n";
        echo "  </tr>\n";
    }
    // And add room to create a new one...
    echo "  <tr class=bl>\n";
    form_start();
    echo "    <input type=hidden name=cmd value=" . encrypt_get_vars("create") . ">\n";
    echo "    <input type=hidden name=uid value=" . encrypt_get_vars($user_id) . ">\n";
    echo "    <td><input type=text name=ne_name size=20 maxlength=20></td>\n";
    echo "    <td><input type=text name=ne_distance size=6 maxlength=6></td>\n";
    echo "    <td><input type=text name=ne_angle size=7 maxlength=7></td>\n";
    echo "    <td><input type=submit name=name=submit value=Add></td>\n";
    form_end();
    echo "  </tr>\n";
    echo "</table>\n";
}

function HookAnnotateAllRemoveannotations()
{
    global $ref;
    sql_query("delete from annotate_notes where ref='{$ref}'");
    sql_query("update resource set annotation_count=0 where ref='{$ref}'");
    sql_query("delete from resource_keyword where resource='{$ref}' and annotation_ref>0");
}

 public static function updatePass($username, $password, $secret)
 {
     $passhash = md5($secret . $wantpassword . $secret);
     $sql = "UPDATE `users` SET  `passhash` =  '{$passhash}'  WHERE  `username` = '{$username}'";
     print $sql;
     sql_query($sql) or sqlerr(__FILE__, __LINE__);
 }

function docleanup($data)
{
    global $INSTALLER09, $queries, $bdir;
    set_time_limit(0);
    ignore_user_abort(1);
    $mysql_host = $INSTALLER09['mysql_host'];
    $mysql_user = $INSTALLER09['mysql_user'];
    $mysql_pass = $INSTALLER09['mysql_pass'];
    $mysql_db = $INSTALLER09['mysql_db'];
    $bdir = $_SERVER["DOCUMENT_ROOT"] . "/include/backup";
    $c1 = "mysqldump -h " . $mysql_host . " -u " . $mysql_user . " -p" . $mysql_pass . " " . $mysql_db . " -d > " . $bdir . "/db_structure.sql";
    $c = "mysqldump -h " . $mysql_host . " -u " . $mysql_user . " -p" . $mysql_pass . " " . $mysql_db . " " . tables("peers|messages|sitelog") . " | bzip2 -cq9 > " . $bdir . "/db_" . date("m_d_y", TIME_NOW) . ".sql.bz2";
    system($c1);
    system($c);
    $files = glob($bdir . "/db_*");
    foreach ($files as $file) {
        if (TIME_NOW - filemtime($file) > 3 * 86400) {
            unlink($file);
        }
    }
    $ext = "db_" . date("m_d_y", TIME_NOW) . ".sql.bz2";
    sql_query("INSERT INTO dbbackup (name, added, userid) VALUES (" . sqlesc($ext) . ", " . TIME_NOW . ", " . $INSTALLER09['site']['owner'] . ")") or sqlerr(__FILE__, __LINE__);
    if ($queries > 0) {
        write_log("Auto-dbbackup----------------------Auto Back Up Complete using {$queries} queries---------------------");
    }
    if (false !== mysqli_affected_rows($GLOBALS["___mysqli_ston"])) {
        $data['clean_desc'] = mysqli_affected_rows($GLOBALS["___mysqli_ston"]) . " items deleted/updated";
    }
    if ($data['clean_log']) {
        cleanup_log($data);
    }
}

function translation_statistics_category_lang($lang) {
  $list=array();

  $sql_str="select category.* from category_current left join category on category_current.version=category.version";
  $res=sql_query($sql_str);
  while($elem=pg_fetch_assoc($res)) {
    $tags=new tags(parse_hstore($elem['tags']));
    $cat_lang=coalesce($tags->get("lang"), "en");

    if(($s=$tags->get("name:$lang"))||(($s=$tags->get("name"))&&$lang==$cat_lang))
      $list["category:{$elem['category_id']}:name"]=$s;
    if(($s=$tags->get("description:$lang"))||(($s=$tags->get("description"))&&$lang==$cat_lang))
    $list["category:{$elem['category_id']}:description"]=$s;

    $sql_str="select * from category_rule where version='{$elem['version']}'";
    $res_r=sql_query($sql_str);
    while($elem_r=pg_fetch_assoc($res_r)) {
      $tags_r=new tags(parse_hstore($elem_r['tags']));

      if(($s=$tags_r->get("name:$lang"))||(($s=$tags_r->get("name"))&&$lang==$cat_lang))
	$list["category:{$elem['category_id']}:{$elem_r['rule_id']}:name"]=$s;
    }
  }

  return $list;
}

function wikipedia_streetnames_info($info_ret, $object) {
  global $data_lang;
  $text="";

  if(!$object->tags->get("highway"))
    return;

  $res=sql_query("select * from osm_polygon where osm_way && geomfromtext('{$object->data['way']}', 900913) and CollectionIntersects(osm_way, geomfromtext('{$object->data['way']}', 900913)) and osm_tags @> 'boundary=>administrative' order by parse_number(osm_tags->'admin_level') desc");
  while($elem=pg_fetch_assoc($res)) {
    $boundary=load_object($elem['osm_id']);

    $data=cache_search($boundary->id, "wikipedia:street_names:$data_lang");
    if($data) {
      $data=unserialize($data);
    }
    else {
      $data=wikipedia_get_lang_page($boundary, "wikipedia:street_names");
      $article=wikipedia_get_article($boundary, $data['page'], $data['lang']);
      $data['article']=$article;

      cache_insert($boundary->id, "wikipedia:street_names:$data_lang", 
        serialize($data), "1 hour");
    }

    if($data['article']) {
      $text.=wikipedia_streetnames_parse($data['article'], $object);
      if($text) {
	$text.="<br>".lang("source").": <a class='external' href='".wikipedia_url($boundary, $data['page'], $data['lang'])."'>Wikipedia</a>\n";
	$info_ret[]=array("head"=>"wikipedia_streetnames", "content"=>$text, "doc"=>"plugin:wikipedia_streetnames/feature");
	return;
      }
    }
  }
}

function show_users($user_id)
{
    global $_GALAXY;
    echo "<table border=0 align=center width=60%>\n";
    // Show friends
    echo "<tr><th class=white colspan=2>Friendly Races</th></tr>\n";
    $result = sql_query("SELECT gu.* FROM g_users AS gu, g_knownspecies AS gks WHERE FIND_IN_SET( gu.user_id, gks.csl_friend_id ) and gks.user_id=" . $user_id);
    while ($user = sql_fetchrow($result)) {
        echo "<tr class=bl><td><a href=user.php?cmd=" . encrypt_get_vars("showdetail") . "&uid=" . encrypt_get_vars($user['user_id']) . ">" . $user['race'] . " Race</a></td><td>&nbsp;</td></tr>\n";
    }
    echo "<tr><td colspan=2>&nbsp;</td></tr>\n";
    // Show neutral
    echo "<tr><th class=white colspan=2>Neutral Races</th></tr>\n";
    $result = sql_query("SELECT gu.* FROM g_users AS gu, g_knownspecies AS gks WHERE FIND_IN_SET( gu.user_id, gks.csl_neutral_id ) and gks.user_id=" . $user_id);
    while ($user = sql_fetchrow($result)) {
        echo "<tr class=bl><td><a href=user.php?cmd=" . encrypt_get_vars("showdetail") . "&uid=" . encrypt_get_vars($user['user_id']) . ">" . $user['race'] . " Race</a></td><td>&nbsp;</td></tr>\n";
    }
    echo "<tr><td colspan=2>&nbsp;</td></tr>\n";
    // Show enemies
    echo "<tr><th class=white colspan=2>Enemy Races</th></tr>\n";
    $result = sql_query("SELECT gu.* FROM g_users AS gu, g_knownspecies AS gks WHERE FIND_IN_SET( gu.user_id, gks.csl_enemy_id ) and gks.user_id=" . $user_id);
    while ($user = sql_fetchrow($result)) {
        echo "<tr class=bl><td><a href=user.php?cmd=" . encrypt_get_vars("showdetail") . "&uid=" . encrypt_get_vars($user['user_id']) . ">" . $user['race'] . " Race</a></td><td>&nbsp;</td></tr>\n";
    }
    echo "<tr><td colspan=2>&nbsp;</td></tr>\n";
    echo "</table>";
}