Context
- Ascenvia (Atlanta, GA)
- Vice President, head programmer
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.
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.
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> ";
if($prev) $listnav.=" <a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$minoffset&limit=$limit\"><</a>";
$listnav.=" ";
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.=" ";
if($next) $listnav.="<a class=\"links\" href=\"http://www.ascenvia.net/engine/search_tutorial.php?a=search&offset=$maxoffset&limit=$limit\">></a> ";
$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.
This is a high level breakdown of the website’s structure.
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
