PHP SQLSelect::GetQuery方法代码示例

  function LoadFromDB() {
    global $POSTS_PER_PAGE;

    $s = new SQLSelect();
    $s->AddTable("bbs_topics");
    $s->AddWhere("bbs_topics.id=".$this->id);
    $this->topic = SQLLib::SelectRow($s->GetQuery());
    if(!$this->topic) return false;

    $s = new SQLSelect();
    $s->AddField("count(*) as c");
    $s->AddTable("bbs_posts");
    $s->AddWhere("bbs_posts.topic=".$this->id);
    $this->postcount = SQLLib::SelectRow($s->GetQuery())->c;

    $s = new BM_Query();
    $s->AddTable("bbs_posts");
    $s->AddField("bbs_posts.id as id");
    $s->AddField("bbs_posts.post as post");
    $s->AddField("bbs_posts.added as added");
    $s->attach(array("bbs_posts"=>"author"),array("users as user"=>"id"));
    $s->AddWhere("bbs_posts.topic=".$this->id);
    //$s->SetLimit( $POSTS_PER_PAGE, (int)(($this->page - 1)*$POSTS_PER_PAGE) );

    $this->paginator = new PouetPaginator();
    $this->paginator->SetData( "topic.php?which=".$this->id, $this->postcount, $POSTS_PER_PAGE, $_GET["page"] );
    $this->paginator->SetLimitOnQuery( $s );

    $this->posts = $s->perform();

    $this->title = _html($this->topic->topic);
  }

  function Load( $cached = false ) {
    $s = new SQLSelect();
    $s->AddTable("buttons");
    $s->AddOrder("rand()");
    $s->AddWhere("dead = 0");
    $s->SetLimit("1");
    $this->data = SQLLib::SelectRow($s->GetQuery());

    $this->title = $this->data->type;
  }

  function LoadFromDB() {
    $s = new SQLSelect();

    $this->group = PouetGroup::Spawn($this->id);
    $this->addeduser = PouetUser::Spawn($this->group->addedUser);

    // not to boast or anything, but this is fucking beautiful.

    $sub = new SQLSelect();
    $sub->AddField("max(comments.addedDate) as maxDate");
    $sub->AddField("comments.which");
    $sub->AddTable("comments");
    $sub->AddJoin("left","prods","prods.id = comments.which");
    //$sub->AddOrder("comments.addedDate desc");
    $sub->AddGroup("comments.which");
    $sub->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $s = new BM_Query("prods");
    $s->AddField("cmts.addedDate as lastcomment");
    $s->AddField("cmts.rating as lastcommentrating");
    $s->AddJoin("left","(select comments.addedDate,comments.who,comments.which,comments.rating from (".$sub->GetQuery().") as dummy left join comments on dummy.maxDate = comments.addedDate and dummy.which = comments.which) as cmts","cmts.which=prods.id");
    $s->attach(array("cmts"=>"who"),array("users as user"=>"id"));
    $s->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $r = !!$_GET["reverse"];
    switch($_GET["order"])
    {
      case "type": $s->AddOrder("prods.type ".($r?"DESC":"ASC")); break;
      case "party": $s->AddOrder("prods_party.name ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_year ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_place ".($r?"DESC":"ASC")); break;
      case "release": $s->AddOrder("prods.releaseDate ".($r?"ASC":"DESC")); break;
      case "thumbup": $s->AddOrder("prods.voteup ".($r?"ASC":"DESC")); break;
      case "thumbpig": $s->AddOrder("prods.votepig ".($r?"ASC":"DESC")); break;
      case "thumbdown": $s->AddOrder("prods.votedown ".($r?"ASC":"DESC")); break;
      case "avg": $s->AddOrder("prods.voteavg ".($r?"ASC":"DESC")); break;
      case "views": $s->AddOrder("prods.views ".($r?"ASC":"DESC")); break;
      case "latestcomment": $s->AddOrder("lastcomment ".($r?"ASC":"DESC")); break;
      default: $s->AddOrder("prods.name ".($r?"DESC":"ASC")); break;
    }
    $this->prods = $s->perform();
    PouetCollectPlatforms($this->prods);
    PouetCollectAwards($this->prods);

    $s = new BM_Query("affiliatedboards");
    $s->attach(array("affiliatedboards"=>"board"),array("boards as board"=>"id"));
    $s->AddWhere(sprintf_esc("affiliatedboards.group=%d",$this->id));
    $this->affil = $s->perform();
  }

<?
require_once("bootstrap.inc.php");

header("Content-type: application/json; charset=utf-8");

$sql = new SQLSelect();
$sql->AddField("id");
$sql->AddField("nickname as name");
$sql->AddField("avatar");
$sql->AddField("glops");
$sql->AddTable("users");

$r = array();
if ($_POST["search"])
{
  $sql->AddWhere(sprintf_esc("nickname like '%%%s%%'",_like($_POST["search"])));
  $sql->AddOrder(sprintf_esc("if(nickname='%s',1,2), nickname, lastLogin DESC",$_POST["search"]));
  $sql->SetLimit(10);
  $r = SQLLib::selectRows( $sql->GetQuery() );
}
else if ($_POST["id"])
{
  $sql->AddWhere(sprintf_esc("id = %d",$_POST["id"]));
  $sql->SetLimit(1);
  $r = SQLLib::selectRows( $sql->GetQuery() );
}
echo json_encode($r);
?>

  function LoadFromDB() {
    $s = new BM_Query();
    $s->AddField("comments.id as id");
    $s->AddField("comments.comment as comment");
    $s->AddField("comments.rating as rating");
    $s->AddField("comments.addedDate as addedDate");
    $s->attach(array("comments"=>"who"),array("users as user"=>"id"));
    $s->AddTable("comments");
    $s->AddOrder("comments.addedDate");
    $s->AddWhere("comments.which=".$this->id);
    $perPage = get_setting("prodcomments");
    if ($perPage != -1)
    {
      $sc = new SQLSelect();
      $sc->AddField("count(*) as c");
      $sc->AddWhere("comments.which=".$this->id);
      $sc->AddTable("comments");

      $commentCount = SQLLib::SelectRow($sc->GetQuery())->c;

      $this->paginator->SetData( "prod.php?which=".$this->id, $commentCount, $perPage, $_GET["page"] );
      $this->paginator->SetLimitOnQuery( $s );
      /*
      $this->commentCount =

      $this->numPages = (int)ceil($this->commentCount / $this->perPage);
      if (!isset($_GET["page"]))
        $this->page = $this->numPages;
      else
        $this->page = (int)$_GET["page"];

      $this->page = (int)max( $this->page, 1 );
      $this->page = (int)min( $this->page, $this->numPages );

      if ($this->numPages > 1)
        $s->SetLimit( $this->perPage, (int)(($this->page-1) * $this->perPage) );
      */
    }
    $r = $s->perform();
    $this->data = $r;
  }

function pouetAdmin_recacheTopDemos()
{
  global $timer;
  
  // this needs to be made faster. a LOT faster.
  $total = array();

  // list by views
  $timer["recache_views"]["start"] = microtime_float();
  $i=0;
  $query="SELECT id,name,views FROM prods ORDER BY views DESC";
  $result = SQLLib::Query($query);
  $content = "<ol>";
  while($tmp = SQLLib::Fetch($result)) {
    $total[$tmp->id]+=$i;
    $i++;
    if ($i<=5)
      $content .= "<li><b>"._html($tmp->name)."</b> - ".$tmp->views." views</li>\n";
  }
  $content .= "</ol>";
  $content .= "<h3>".$i." prod views loaded</h3>\n";
  $timer["recache_views"]["end"] = microtime_float();

  $i=0;
  // Get the list of prod IDs ordered by the sum of their comment ratings
  $sql = new SQLSelect();
  $sql->AddField("prods.id");
  $sql->AddField("prods.name");
  $sql->AddField("SUM(comments.rating) as theSum");
  $sql->AddTable("prods");
  $sql->AddJoin("","comments","prods.id = comments.which");
  $sql->AddGroup("prods.id");
  $sql->AddOrder("SUM(comments.rating) DESC");

  $timer["recache_votes"]["start"] = microtime_float();
  $result = SQLLib::Query( $sql->GetQuery() );
  $content .= "<ol>";
  while($tmp = SQLLib::Fetch($result)) {
    $total[$tmp->id]+=$i;
    $i++;
    if ($i<=5)
      $content .= "<li><b>"._html($tmp->name)."</b> - "._html($tmp->theSum)." votes</li>\n";
  }
  $content .= "</ol>";
  $content .= "<h3>".$i." vote counts loaded</h3>\n";
  $timer["recache_votes"]["end"] = microtime_float();

  $timer["recache_sort"]["start"] = microtime_float();
  asort($total);
  $timer["recache_sort"]["end"] = microtime_float();

  $timer["recache_update"]["start"] = microtime_float();
  $i=1;
  unset($tmp);
  unset($top_demos);
  $a = array();
  while ((list ($key, $val)=each($total))) 
  {
    $a[] = array(
      "id" => $key,
      "rank" => $i,
    );
    if (count($a) == 100)
    {
      SQLLib::UpdateRowMulti("prods","id",$a);
      $a = array();
    }
    $i++;
  }
  SQLLib::UpdateRowMulti("prods","id",$a);
  $content .= "<h3>".$i." prod rankings updated</h3>\n";
  $timer["recache_update"]["end"] = microtime_float();

  @unlink('cache/pouetbox_topalltime.cache');
  @unlink('cache/pouetbox_topmonth.cache');
  return $content;
}