I currently have the code below in a PHP page, embedded in the markup and I want to remove it to a class. I know how to instantiate an object and write a class etc., so I don’t need help with that. My question is about how to return the result set and access it.
Berliner, I wrote this small tutorial for myself and a few buddies. I changed it to OOP, a few weeks back. it may help you get started before they move it: http://rbenson.info/examples/phpmysql/phpmysql.php
rguy84, I know how to use the while loop, that’s exactly how my code is setup now… the question is how do I move this into a separate class file (the query) and pass the result set back to the object so I can loop thru them there? I think I have the login down just not the syntax:
class GetList {
function setList() {
$get_posts_list = mysql_query("SELECT * from posts WHERE post_cats =
'$cat_id' ORDER BY post_time LIMIT $offset, $rowsperpage") or
die(mysql_error());
$list = an array of $get_posts_list's data;
}
function getList() {
return $list;
}
}
then loop thru it somehow back in the markup, in the object. How do I put the data into an array (the syntax) and how do I loop thru it for display with a for each loop?
I would separate the data access into a reusable layer. Then add a method responsible for retrieving posts by a category with some basic options for fields to select, sort order and limit clause. Ideally I would also recommend separating the database adapter into a separate class and passing into the DAO via composition (not shown) rather than running the query directly in the Post data access object. If you separated the adapter into another class you could than use a method like query() to actually run the query and collect the result set. That way you achieve a single point in your application were all queries are run and all post data is accessed from.
(untested)
<?php
/*
* Post data access layer
*/
class DAOPost {
/*
* Fetch posts by category
*
* @param str categories id
* @param str columns to select
* @param str order by clause
* @param str limit clause
* @return array categories posts
*/
public function fetchPostsByCategoryId($strCatId,$strSelect='*',$strSort='post_time',$strLimit='') {
// create SQL
$strSQL = sprintf(
"SELECT %s FROM posts WHERE post_cats = '%s' %s %s"
,$strSelect
,mysql_real_escape_string($strCatId)
,empty($strSort)?'':"ORDER BY $strSort"
,empty($strLimit)?'':" LIMIT $strLimit"
);
// run query
$objResult = mysql_query($strSQL);
// on error throw expception
if(!$objResult) {
throw Exception('SQL Query Invalid');
}
// parse result set
$arrPosts = array();
while($arrPost = mysql_fetch_array( $get_posts_list )) {
$arrPosts[] = $arrPost;
}
return $arrPosts
}
}
/*
* instantiate data access layer
*/
$objDAOPost = new DAOPost();
$cat_id = 'turtles';
$offset = 0;
$rowsperpage = 10;
/*
* fetch posts
*/
try {
$arrPosts = $objDAOPost->fetchPostsByCategoryId($cat_id,'*','post_time',"$offset,$rowsperpage");
} catch(Exception $e) {
echo "<p>{$e->getMessage()}</p>";
exit;
}
/*
* print each post
*/
foreach($arrPosts as $arrPost) {
printf(
'<div class="postlist-row">
<div class="postlist-thumb"><img src="users/%s/images/%s" /></div>
<div class="postlist-info">
<p><a href="#">%s</a></p>
<p><a href="#">%1$s</a></p>
<p>Location: %s</p>
</div>
<div class="postlist-meta"></div>
</div>'
,$arrPost['post_owner']
,$arrPost['post_thumb']
,htmlentities($arrPost['post_title'])
,htmlentities($arrPost['post_location'])
);
}
?>
$result = $yourConnectionObj ->query("SELECT * from posts WHERE post_cats =
'$cat_id' ORDER BY post_time LIMIT $offset, $rowsperpage");
then set that to an array
$list = $result->fetch_assoc();
now you can say $list[“FieldName”], in a loop.
so:
class getList{
Public function setList(){
$result = $yourConnectionObj ->query("SELECT * from posts WHERE post_cats =
'$cat_id' ORDER BY post_time LIMIT $offset, $rowsperpage");
}
Public function getList(){
// I think you need to call setList()
$this -> setList();
While($list = $result->fetch_assoc()){
//Do stuff
}
}
Here is a working example with the code I posted above. You will need to change the database authentication info at the top though. I am also using placeholder data in place of actually running the query. However, to fix that you can comment out the placeholder data and uncomment out the call to query in the DAO.
<?php
/*
* Database
*/
class DB {
private
/*
* Connection resource identifier
*/
$_objLink;
/*
* Connects to database
*
* @param str host name
* @param str user name
* @param str user password
* @param str database name
*/
public function connect($strHost,$strUser,$strPwd,$strDb) {
$this->_objLink = mysql_connect($strHost,$strUser,$strPwd);
if(!$this->_objLink) {
throw new Exception('Unable to connect to database');
}
if(!mysql_select_db($strDb,$this->_objLink)) {
throw new Exception('Unable to connect to database');
}
}
/*
* Queries database returning result set as associative array
*
* @param str query
* @return array result set associative array
*/
public function query($strSQL) {
$objResult = mysql_query($strSQL,$this->_objLink);
if(!$objResult) {
throw new Exception('SQL Query invalid');
}
$arrRows = array();
while($arrRow = mysql_fetch_assoc($objResult)) {
$arrRows[] = $arrRow;
}
return $arrRows;
}
/*
* Escapes string for proper input into query statement
*
* @param str string to escape
* @return escaped string value
*/
public function escapeString($strValue) {
$strEscapedValue = mysql_real_escape_string($strValue,$this->_objLink);
if($strEscapedValue === false) {
throw new Exception('String escape failed');
}
return $strEscapedValue;
}
}
/*
* Post data access layer
*/
class DAOPost {
private
/*
* database adapter
*/
$_objDB;
public function __construct(DB $objDB) {
$this->_objDB = $objDB;
}
/*
* Fetch posts by category
*
* @param str categories id
* @param str columns to select
* @param str order by clause
* @param str limit clause
* @return array categories posts
*/
public function fetchPostsByCategoryId($strCatId,$strSelect='*',$strSort='post_time',$strLimit='') {
$strSQL = sprintf(
"SELECT %s FROM posts WHERE post_cats = '%s' %s %s"
,$strSelect
,/*this->_objDB->escapeString(*/$strCatId/*)*/
,empty($strSort)?'':"ORDER BY $strSort"
,empty($strLimit)?'':" LIMIT $strLimit"
);
// the query that would be ran
echo "<p>$strSQL</p>";
// placeholder data
return array(
array(
'post_owner'=>'Mark'
,'post_title'=>'Post 1'
,'post_location'=>'California'
,'post_thumb'=>'xxx.jpg'
)
,array(
'post_owner'=>'Tom'
,'post_title'=>'Post 2'
,'post_location'=>'Netherlands'
,'post_thumb'=>'yyy.jpg'
)
,array(
'post_owner'=>'Travis'
,'post_title'=>'Post 3'
,'post_location'=>'Florida'
,'post_thumb'=>'cdf.jpg'
)
);
//return $this->_objDB->query($strSQL);
}
}
/*
* --------------- Configuration -----------------------------------------
*/
// database authentication
$strHost = 'host';
$strUser = 'user';
$strPwd = 'password';
$strDb = 'database';
/*
* -------------- Initiation ----------------------------------------------
*/
/*
* Instantiate database connection
*/
$objDB = new DB();
/*
* Connect to database
*/
try {
$objDB->connect($strHost,$strUser,$strPwd,$strDb);
} catch(Exception $e) {
echo "<p>{$e->getMessage()}</p>";
exit;
}
/*
* ---------------- Controller --------------------------------------------
*/
/*
* instantiate data access layer
*/
$objDAOPost = new DAOPost($objDB);
/*
* Set page vars
*/
$cat_id = 'turtles';
$offset = 0;
$rowsperpage = 10;
/*
* fetch posts
*/
try {
$arrPosts = $objDAOPost->fetchPostsByCategoryId($cat_id,'*','post_time',"$offset,$rowsperpage");
} catch(Exception $e) {
echo "<p>{$e->getMessage()}</p>";
exit;
}
/*
* ---------------- Template --------------------------------------------
*/
/*
* print each post
*/
foreach($arrPosts as $arrPost) {
printf(
'<div class="postlist-row">
<div class="postlist-thumb"><img src="users/%s/images/%s" /></div>
<div class="postlist-info">
<p><a href="#">%s</a></p>
<p><a href="#">%1$s</a></p>
<p>Location: %s</p>
</div>
<div class="postlist-meta"></div>
</div>'
,$arrPost['post_owner']
,$arrPost['post_thumb']
,htmlentities($arrPost['post_title'])
,htmlentities($arrPost['post_location'])
);
}
?>