Home › Support › Load from a Local Database via PHP + MySQL [Tutorial]

Loading Data from your database

Two different example scripts have been provided to help you load news feed data from your database these are all designed based on the database created in Advanced database structure. The two files NewsList.php and Article.php work hand in hand and are linked together using two variables in each file called $individualArticlePage and $listPage respectively.

[ NewsList.php ]

The first one called NewsList.php is used to produce a list of news items and outputs each item in a HTML table with pre-configured class names for CSS styling again this can be tailored to your own needs:

<html>
<head></head>
<body>
<?php
include_once '../Classes/DB.php';

$link = mysql_connect(DB::getHost(), DB::getUser(), DB::getPassword());
@mysql_select_db(DB::getDatabaseName()) or die("Cannot find Database");

$limit = 10; //number of news items to display
$individualArticlePage = "Article.php";

$query = "Select * FROM feed ".getQueryCriteria()."ORDER BY publishedDate DESC Limit 0,".$limit;
if(!$result = mysql_query($query))echo "Invalid Query: " . $query;

$flip=false;
while($row = mysql_fetch_assoc($result)){
$tableClass=" class='alternatingTable'";
if($flip)$tableClass="";
echo "<table".$tableClass.">\n";
echo "<tr class='headline'><td><a href='".$individualArticlePage."?id=".$row['id']."'>" . $row['headline'] . "</a></td></tr>\n";
echo "<tr class='extract'><td>" . $row['extract'] . "</td></tr>\n";
echo "<tr class='publishedDate'><td>" . $row['publishedDate'] . "</td></tr>\n";
echo "</table>\n";
$flip = !$flip;
}

mysql_close($link);

function getQueryCriteria(){
$categoryID = $_GET['categoryID'];
$whereQuery = "";
if(isset($categoryID)){
$whereQuery = "WHERE id IN (SELECT feedid FROM feedcategories WHERE categoryID=" . $categoryID . ") ";
}

return $whereQuery;
}
?>
</body>
</html>

[Jump to top]

[ Article.php ]

The second one called Article.php is used to produce a full news item using a HTML table for structuring:

<?php
include_once '../Classes/DB.php';

$link = mysql_connect(DB::getHost(), DB::getUser(), DB::getPassword());
@mysql_select_db(DB::getDatabaseName()) or die("Cannot find Database");

$id = $_GET['id'];
$listPage = "NewsList.php";

//feed query
$query = "Select * FROM feed WHERE id =" . $id;
$result = mysql_query($query);
$feedrow = mysql_fetch_assoc($result);

//photo query
$subQuery = "(SELECT photoid FROM feedphotos
WHERE feedid =". $id .")";
$query = "SELECT * FROM photos WHERE id IN " . $subQuery;
$result = mysql_query($query);
$imagerow = mysql_fetch_assoc($result);

//category query
$subQuery = "(SELECT categoryid FROM feedcategories
WHERE feedid =". $id .")";
$query = "SELECT * FROM categories WHERE id IN " . $subQuery;
$categoryresult = mysql_query($query);

//comments query
$subQuery = "(SELECT commentid FROM feedcomments
WHERE feedid =". $id .")";
$query = "SELECT * FROM comments WHERE id IN " . $subQuery;
$commentresult = mysql_query($query);

echo "<table id='articleTable'>\n";
echo "<h1>" . $feedrow['headline'] . "</h1>\n";
echo getImageHtml() . $feedrow['text'] . "\n";
echo $feedrow['publishedDate'] . "\n";
echo "<h1>" . $feedrow['byLine'] . "</h1>\n";
echo getCategoriesHtml() . "\n";
echo "<h1>" . getCommentsHtml() . "</h1>\n";
echo "\n";

mysql_close($link);

function getImageHtml(){
global $imagerow;
$imageURL = $imagerow['largeURL'];
$imageAlt = htmlspecialchars($imagerow['htmlAlt'], ENT_QUOTES);
return "<img src="&quot;.$imageURL.&quot;" alt="&quot;.$imageAlt.&quot;" />"; } function getCategoriesHtml(){ global $categoryresult, $listPage; $categoriesList = ""; while($row = mysql_fetch_assoc($categoryresult)){ if(!empty($categoriesList))$categoriesList = $categoriesList . ", <a id="" href="&quot;.$listPage.&quot;?categoryID=&quot;.$row[">" . $row['name'] . "</a>\n"; else $categoriesList = "<a id="" href="&quot;.$listPage.&quot;?categoryID=&quot;.$row[">" . $row['name'] . "</a>\n"; } return $categoriesList; } function getCommentsHtml(){ global $commentresult; $commentsList = "\n"; while($row = mysql_fetch_assoc($commentresult)){ $commentsList = $commentsList . "\n". "" . $row['name'] . "\n". "" . $row['text'] . "\n". "" . $row['location'] . "\n". "" . $row['postDate'] . "\n". "\n"; } $commentsList = $commentsList . "\n"; return $commentsList;
} ?>

[Jump to top]