Posts Tagged ‘ database ’

Web Programming Lab 10: A News System

This weeks lab was a little more interesting. We needed to create a new page visible to everyone visiting the site with news articles displayed from registered users. This had an extra credit stipulation for paginating the articles. Also, there needed to be an admin interface for registered users to write and edit news articles. This part had an extra credit option for using TinyMCE. TinyMCE is a useful way for modifying things like text areas to have additional controls for formatting text, much like Word Press’ blog posting system. Each article needed a date, an author, a title, and the actual content. To start, I added the necessary table in my database to support news articles. The admin interface is composed of the post writing area above a list of editable previous posts. If the title of any of these is clicked, the page will pull the information into the writing form for editing. This is fairly easy, submitting new posts simply as form content to the databases, and updating the database for editing. To check it out, create an account on my site, and visit the News Admin section. The other part of the site was the actual news display page. To see it, look here. The main display for the page is a simple smarty block that gets displayed as content.
To handle the back end, the code assigning the page is more like this:

if($_GET['move']=='prev'){
$smarty->assign('news', getPaginatedNews($_GET['page_id']-1));
$smarty->assign('page_id', $_GET['page_id']-1);
if($_GET['page_id']-1>0){
$smarty->assign('show_prev', true);
}
$smarty->assign('show_next', true);
}
elseif($_GET['move']=='next'){
$smarty->assign('news', getPaginatedNews($_GET['page_id']+1));
$smarty->assign('page_id', $_GET['page_id']+1);
$smarty->assign('show_prev', true);
if(sizeof(getPaginatedNews($_GET['page_id']+2))!=0){
$smarty->assign('show_next', true);
}
}
else{
$smarty->assign('news', getPaginatedNews(0));
$smarty->assign('page_id', 0);
if(sizeof(getPaginatedNews($_GET['page_id']+1))!=0){
$smarty->assign('show_next', true);
}
}
$smarty->assign('image', 'images/villains.jpg');
$smarty->assign('title', 'News');

This handles the navigation as well as the pagination based on page ids that I have assigned. The paginating news retrieval looks like this:

function getPaginatedNews($page_id){
$news = array();
$index = $page_id*3;
$query = "SELECT * FROM ssm_articles ORDER BY date desc LIMIT ".$index.",3";
$result = mysql_query($query);
while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
$news[] = $row;
}
return $news;
}

This is a nice little function, and was probably the most interesting thing to come out of the assignment. It took me a couple seconds to realize that if I used a 0 based set of page ids, then I could pass them in, multiply by three, and use them as the base for what i wanted to display as I wanted to display three posts per page. This was an interesting lab that makes me feel like with some more refinement, I could write my own blogging software.

Web Programming Lab 9: Databases

In this weeks lab, we finnaly moved onto the section that I have been waiting for. We finally are moving our sites to teh proper storage system of a mySQL database instead of text files for storing our users, and tracking history. To set up the connection so that it could be called on any page, I placed the connection code in my common.php file which is prepended to all other files in the directory. To set up a mySQL connection, it should look something like this:
$_GLOBALS['DB'] = mysql_connect('servername','username','password') or die('Error connecting to mysql'.mysql_error());
mysql_select_db('databasename', $_GLOBALS['DB']) or die('Error connecting to database'.mysql_error());

For using the database, query structure can look something like this to get all users:
function getAllUsers(){
$query = 'SELECT * FROM users ORDER BY username asc';
$users = array();
$result = mysql_query($query);
while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
$users[] = $row;
}
return $users;
}

This example runs the query to retrieve all of the users in my users table, and then loops through, turning each returned entry into an associative array that is then appended to the users array that is returned. This is a much better storage measure than text files. Check out the site here.