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 inAdvanced 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 calledNewsList.phpis 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:




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";

美元,ry = "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 "\n";
echo "" . $row['headline'] . "\n";
echo "" . $row['extract'] . "\n";
echo "" . $row['publishedDate'] . "\n";
echo "\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;
}
?>

[Jump to top]

[Article.php ]

The second one calledArticle.phpis used to produce a full news item using a HTML table for structuring:

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
美元,ry = "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 .")";
美元,ry = "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 .")";
美元,ry = "SELECT * FROM categories WHERE id IN " . $subQuery;
$categoryresult = mysql_query($query);

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

echo "\n";
echo "

" . $feedrow['headline'] . "

\n";
echo getImageHtml() . $feedrow['text'] . "\n";
echo $feedrow['publishedDate'] . "\n";
echo "

" . $feedrow['byLine'] . "

\n";
echo getCategoriesHtml() . "\n";
echo "

" . getCommentsHtml() . "

\n";
echo "\n";

mysql_close($link);

function getImageHtml(){
global $imagerow;
$imageURL = $imagerow['largeURL'];
$imageAlt = htmlspecialchars($imagerow['htmlAlt'], ENT_QUOTES);
return "".$imageAlt.""; } function getCategoriesHtml(){ global $categoryresult, $listPage; $categoriesList = ""; while($row = mysql_fetch_assoc($categoryresult)){ if(!empty($categoriesList))$categoriesList = $categoriesList . ", " . $row['name'] . "\n"; else $categoriesList = "" . $row['name'] . "\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]