- Moderated by:
- Support
looping through db results
-
- rank:
-
Freshman
- registered:
- February 2006
- Status:
- offline
- last visit:
- 09.03.06
- Posts:
- 10
All,
I am writing my first module for PN. I am having few problems so far, but I am wondering how to loop through DB results.
I am familiar with doing it normally (normally meaning the way I usually write code).
Here's how I normally go about it:
Code
$query="select * from table";
while($result=mssql_fetch_assoc($query)){
//loop through results
}
There doesn't seem to be a list of all the functions available, I've tried taking a look at some of the other module files in PN, but they weren't a lot of help.
Here's the code I'm working with:
Code
function cellname_user_view(){
$content=new pnHTML();
$content->Title(_VIEWTITLE);
//Get dbconnection
list($dbconn)=pnDBGetConn();
//Get array of table names
$pntable=pnDBGetTables();
//Get users' id
$uid=pnUserGetVar('uid');
//Prepare sql statement
$sql='SELECT DISTINCT date,cellName,centNameNew,centNameOld,owner,explanation FROM '.$pntable['CellName'].' WHERE OWNER=\''.$uid.'\'';
//Execute sql statement.
$result =& $dbconn->Execute($sql);
//Here's where I want to loop through the results...
/* //Check for error
if($dbconn->ErrorNo() !=0){
//Set an error message
pnSessionSetVar('errormsg',_CELLNAME_DBQUERYERROR.mysql_error());
return false;
}*/
//If we've made it here, the query exited cleanly
//Set table headers
return $content->GetOutput();
}
Please help.
Thanks in advance.
-
- rank:
-
Freshman
- registered:
- February 2006
- Status:
- offline
- last visit:
- 09.03.06
- Posts:
- 10
OK,
I've been looking around online and at some other PN code and here's what I've come up with. I'm sure there is a more efficient and elegant way to accomplish what I want (my request is very common). Take a look and let me know what you think.
Code
function cellname_user_view(){
$content=new pnHTML();
$content->Title(_VIEWTITLE);
//Get dbconnection
list($dbconn)=pnDBGetConn();
//Get array of table names
$pntable=pnDBGetTables();
//Get users' id
$uid=pnUserGetVar('uid');
//Prepare sql statement
$sql='SELECT DISTINCT date,cellName,centNameNew,centNameOld,explanation FROM '.$pntable['CellName'].' WHERE OWNER=\''.$uid.'\'';
/* //Check for error
if($dbconn->ErrorNo() !=0){
//Set an error message
pnSessionSetVar('errormsg',_CELLNAME_DBQUERYERROR.mysql_error());
return false;
}*/
//If we've made it here, the query exited cleanly
//Set table headers
$tHeader=array(_DATE,_CELLNAME,_CENTNAMENEW,_CENTNAMEOLD,_EXPLANATION);
$content->TableStart('',$tHeader,0);
//Execute sql statement.
$result =& $dbconn->Execute($sql);
for(;!$result->EOF;$result->MoveNext()){
list($date,$cellName,$centNameNew,$centNameOld,$explanation) = $result->fields;
$out=array($date,$cellName,$centNameNew,$centNameOld,$explanation);
$content->TableAddRow($out,'','top');
}
$content->TableEnd();
return $content->GetOutput();
}
-
- rank:
-
Freshman
- registered:
- February 2006
- Status:
- offline
- last visit:
- 09.03.06
- Posts:
- 10
Do any of you know where I should look to find out more about the functions available in PN?
For example, I have just figured out how to loop through DB results, but how do I find out if >0 rows were returned by the DB? This is common and rudimentary, but it's not documented (at least where I've looked). These small problems (lack of documentation) are making my progress slow going...
Thanks in advance.
-
- rank:
-
Professional
- registered:
- January 2004
- Status:
- offline
- last visit:
- 22.03.06
- Posts:
- 958
Like I said in the other post the Example module is a good starting point. Then there's the document by jbirchett and stuff like the API reference.
You can find most docs at http://docs.postnuke.com
And apart from avoiding the pnHTML class it pays to look at the /includes/classes/adodb/docs/ to see how the DB layer works.
And if you get stuck just post here
HTH
-
- rank:
-
Steering Committee
- registered:
- December 2002
- Status:
- offline
- last visit:
- 02.12.08
- Posts:
- 13418
Quote
but it's not documented (at least where I've looked).
You need to look at the ADOdb docs for information on how to use the database abstraction layer, as PostNuke simply implements this class for database interactions.
--
Regards,
Simon
itbegins.co.uk - Zikula Consulting
Please read the Support Guide
-
- rank:
-
Professional
- registered:
- December 2002
- Status:
- offline
- last visit:
- 24.08.08
- Posts:
- 1588
Instead of doing the "for" loop you can always use:
$resultarray = $result->GetArray();
I have heard that this should return an array with a numerical key as well as a key named as the column name - for some reason with PostNuke it only returns the numerical key :(
-Lobos
--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
-
- rank:
-
Steering Committee
- registered:
- December 2002
- Status:
- offline
- last visit:
- 02.12.08
- Posts:
- 13418
Quote
I have heard that this should return an array with a numerical key as well as a key named as the column name
For the column name you'll probably need to set the ADOdb fetch mode to ADODB_FETCH_ASSOC. If you do this you must remember to set it back to FETCH_NUM before exiting your code or you run the risk of breaking other queries for other modules.
--
Regards,
Simon
itbegins.co.uk - Zikula Consulting
Please read the Support Guide
-
- rank:
-
Professional
- registered:
- December 2002
- Status:
- offline
- last visit:
- 24.08.08
- Posts:
- 1588
Thanks Simon,
I am not 100% but I think that you can set this global (fetch type) temporarily in the DB connection object as seen below:
Quote
function webvida_bridge_getblockinfo(){
$dbconn = &pnDBGetConn(true);
$pntable = &pnDBGetTables();
$currentlang = pnUserGetLang();
$column = &$pntable['blocks_column'];
$dbconn->SetFetchMode(ADODB_FETCH_BOTH);
$SQL = "SELECT * FROM $pntable[blocks]";
$result = $dbconn->Execute($SQL);
$blockinfo = $result->GetArray();
$result->Close();
print_r($blockinfo);
return $blockinfo;
}
Quote
cut------------------------------------
[0] => Array
(
[0] => 1
[pn_bid] => 1
[1] => menu
[pn_bkey] => menu
[2] => Main Menu
[pn_title] => Main Menu
[3] =>
[pn_content] =>
[4] =>
[pn_url] =>
[5] => 0
[pn_mid] => 0
[6] => l
[pn_position] => l
[7] => 1.0
[pn_weight] => 1.0
[8] => 1
[pn_active] => 1
[9] => 0
[pn_refresh] => 0
[10] => 2001-11-22 09:07:26
[pn_last_update] => 2001-11-22 09:07:26
[11] =>
[pn_language] =>
[12] => 1
[pn_collapsable] => 1
[13] => 1
[pn_defaultstate] => 1
)
cut------------------------------------
It may be worth noting that I have used ADODB_FETCH_BOTH in this example and the DB object returns both numerical and column named array keys to GetArray(), but you could also use others as per mentioned here:
http://phplens.com/adodb/reference.varibles.adodb_fetch_mode.html
-Lobos
--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
-
- rank:
-
Steering Committee
- registered:
- December 2002
- Status:
- offline
- last visit:
- 02.12.08
- Posts:
- 13418
Quote
I am not 100% but I think that you can set this global (fetch type) temporarily in the DB connection object as seen below:
2 things - I am not sure which ADOdb takes in preference, whether it's the global or class variable (I believe Xaraya had problems with this).
2nd - you are assigning your DB connection object by reference, so any changes you make to the DB connection object will be reflected elsewhere as well.
Assigning by reference is no longer necessary. There is no performance gain to be had from assigning by reference.
--
Regards,
Simon
itbegins.co.uk - Zikula Consulting
Please read the Support Guide
-
- rank:
-
Professional
- registered:
- December 2002
- Status:
- offline
- last visit:
- 24.08.08
- Posts:
- 1588
ah ok... to be honest I don't really know much about it, I just thought that assigning the $dbconn->SetFetchMode(ADODB_FETCH_BOTH); like this would make it temporary and confined only to the current object invocation as opposed to setting it as define (global) which would make it last the "distance" LOL - so on this I thought that the fetch mode would be reset on the next invocation of the object bascially saving you having to redefine the fetchmode afterwards...
So are you saying that this is not the case?
-Lobos
--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
-
- rank:
-
Professional
- registered:
- December 2002
- Status:
- offline
- last visit:
- 24.08.08
- Posts:
- 1588
Oh I get you now - since you only set the DB object once per function you would need to set it again for my idea to work - but I think it would revert to normal on the next function yes? I'm starting to feel a bit confused at my own ramblilng so I hope you follow LOL
-Lobos
--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
-
- rank:
-
Steering Committee
- registered:
- December 2002
- Status:
- offline
- last visit:
- 02.12.08
- Posts:
- 13418
The PostNuke core and all PostNuke modules use the same database connection object - so if you set a property on this object it will persist through the rest of PostNuke's execution unless you set it back. At least, I think. I haven't tested ;)
--
Regards,
Simon
itbegins.co.uk - Zikula Consulting
Please read the Support Guide
|
