Ascenvia

Context

  • Ascenvia (Atlanta, GA)
  • Vice President, head programmer
head02

Case

Requirements:

  • Designing a web resource/community for developers in PHP/MySQL.
  • Becoming a portal for tutorials available on other sites and connect with other developers
  • We were also looking to find employers who would want to hire people via the site.

jobs

This is a database schema for the jobs section on the site. While this structure was implemented, it was never fully-rolled out due to a lack of resources; it would have been a precursor to a tech-focused LinkedIn.

tutorials

This is the database structure for the tutorial engine. Note: The hold and tutorials had similar structures, but the hold provided a place where we could review tutorials before adding them to the website.

This is an example of a tutorial listing from the tutorial engine part of the website created in PHP.

function list_results($form, $offset, $limit) {
global $side;

  if(empty($offset))   $offset=0;  //what tutorial we are starting this page on
  if(empty($limit))    $limit=5;   //number of tutorials per page
  if(empty($form[order]))    $form[order]='visits';  //how will we order the results

  // take form data and query the database
  $key_array=explode(" ",$form[keywords]);

  $keywordDesc="(";
  $keywordTitle="(";
  $keywordCategory="(";

  //prepare a query based on Description, title, or comments
  if(!empty($form[keywords]) && $form[keywords]!=" ") {
	  $keywordDesc=sql_text($form[keywords],'text_search','description');
  	$keywordTitle=sql_text($form[keywords],'text_search','title');
  	$keywordCategory=sql_text($key_array[$i],'text_search','comment');
  }

  //
  if($keywordDesc=="(") $keywordDesc="id != 0";
  if($keywordTitle=="(") $keywordTitle="id != 0";
  if($keywordCategory=="(") $keywordCategory="id = 0";

  $category="id != 0";

  if($form[category_id]!=0) $category=sql_text($form[category_id],'equality','category_id');

  if($form[order]=='date_created') $form[order]=$form[order]." DESC";
  if($form[order]=='rating') $form[order]=$form[order]." DESC";

  //execute query
  $sql="SELECT * FROM tutorials WHERE ($keywordDesc || $keywordTitle) && $category ORDER BY $form[order]";
  $result=sql($sql);
  $numrows=mysql_num_rows($result);

  //execute page sub selection
  $sql="SELECT * FROM tutorials WHERE ($keywordDesc || $keywordTitle) && $category ORDER BY $form[order] LIMIT $offset,$limit";
  $result2=sql($sql);
  $numrows_limit=mysql_num_rows($result2);
  $row=mysql_fetch_array($result2);

  //take result_array and determine how many pages it will take
  $pages= $numrows / $limit;
  if($numrows % $limit) $pages++;

  //figure out math for prev and next buttons
  if($offset>=$limit) $prev=true;
  else               $prev=false;

  $offsetLimit=($pages*$limit) - $limit;
  if(($offset+$limit)<=$offsetLimit) $next=true;
  else                      $next=false;

  $maxoffset=$offset+$limit;
  ($offset-$limit<0)?$minoffset=0:$minoffset=$offset-$limit;
  $start=0;

  if($offset>=(5*$limit)) $start=ceil($offset/$limit)-5;

  if(5*$limit+$offset>=$offsetLimit) {
    $max=ceil($offsetLimit/$limit);
    $start=$max-10;
  } else $max=$start+10;

  //create Prev Next links and each individual page number -- figure out pages
  $listnav="<div align=\"center\" class=\"smalltext\">";
  if($pages!=0 && $offset>0)  $listnav.="<a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=0&limit=$limit\"><<</a> &nbsp; ";
  if($prev) $listnav.=" <a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$minoffset&limit=$limit\"><</a>";
  $listnav.="  &nbsp; &nbsp;  &nbsp;  ";
  if($numrows<=$limit*10) {
    for($l=1;$l<=$pages;$l++) {
      $fix=($l*$limit)-$limit;
      if($fix==$offset) $listnav.="| $l |";
      else $listnav.="| <a class=\"links\"  href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$fix&limit=$limit\">$l</a> |";
    }
  }
  else {
    for($i=($start+1);$i<=$max;$i++) {
      $fix=($i*$limit)-$limit;
      if($fix==$offset) $listnav.="| $i |";
      else $listnav.="| <a class=\"links\"  href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$fix&limit=$limit\">$i</a> |";
    }
  }
  $listnav.=" &nbsp;  &nbsp;  &nbsp; ";
  if($next) $listnav.="<a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$maxoffset&limit=$limit\">></a> &nbsp; ";
  $offsetLimit=$offsetLimit-($offsetLimit%$limit);
  if($pages!=0 && $offset<$offsetLimit) $listnav.=" <a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$offsetLimit&limit=$limit\">>></a>";
  $listnav.=" </div> ";

  $displaying="Showing $numrows_limit of $numrows";
  $list="";

  $category_array=array();

  for($i=0;$i<$numrows_limit;$i++){

    //load category data for tutorials
    $sql="SELECT name FROM categories WHERE id=$row[category_id]";
    $result3=sql($sql);
    $row2=mysql_fetch_array($result3);

    if(!(in_array($row[category_id],$category_array))) {
         $category_array[]=$row[category_id];
    }

    //load logo data for tutorials
    $sql="SELECT logo_url FROM logos WHERE id=$row[picture_id]";
    $result4=sql($sql);
    $row3=mysql_fetch_array($result4);

    //load domain data for where the tutorial was hosted
    $sql="SELECT name, url FROM domains WHERE id=$row[picture_id]";
    $result5=sql($sql);
    $row4=mysql_fetch_array($result5);

    //display rating data
    if($row[num_votes]!=0) $rating=round($row[votes]/$row[num_votes], 1);
    else $rating="<br><b>Be the First to Rate It</b>";

    $url="http://www.ascenvia.net/engine/framemaker.php?tid=$row[id]";

    //each tutorial will be listed with Tutorial Name at top right
    //with Category and Difficulty right under it.  On left will be
    //Date with Average Rating underneath.

    $data=array( TITLE => $row[title],
                 URL => $url,
                 DESCRIPTION => $row[description],
                 DIFFICULTY => $row[difficulty],
                 RATING => $rating,
                 DATE => $row[date_created],
                 CATEGORY => $row2[name],
                 LOGO_URL => $row3[logo_url],
                 DOMAIN => $row4[name],
                 DOMAIN_URL => $row4[url] );

    //load a template data for search results and fill in the data fields
    $list.=no_display('/base/engine/row.tmp',$data);
    //put template data into a variable to load into the webpage template

    $row=mysql_fetch_array($result2);
  }

  if($numrows==0)  $list="<div class=\"header\">No matches can be found</div>";

  //add controls to change the category and ordering of tutorials
  $array=load_categories();
  $category=put_field('form[category_id]','select',$form[category_id],$array);

  $array2=array( 'rating'=>'Rating','visits'=>'Popularity','date_created'=>'Date Created','difficulty'=>'Difficulty' );
  $order=put_field('form[order]','select',$form[order],$array2);

  //load webpage
  $data=array(
               NAVIGATION=>$listnav,
               SHOWING=>$displaying,
               LIST_RESULTS=>$list  );

}

?>

Result: We created a website for web developers to come for resources on tech topics. The site included news and features, a social web-forum, a search engine and rating system for tutorials, and an admin component for adding content.

ascenvia

This is a high level breakdown of the website’s structure.

pascenvia

This was the ascenvia website when it was accessible to the public.

Click here to explore an archived version of the site.
(Note that images were not uniformly archived.)

Outcomes:

  • The site was released and incorporated.
  • We were able to convince 54 tutorial sites to register nearly 900 tutorials with our tutorial engine.
  • We also had over 1650 members who posted nearly 8500 times on our website’s forum.

Details

Dates: Jun 2001-Apr 2004

Development Skills: HTML, CSS, PHP, MySQL

My Roles: Programming, Vice President