accessing a database table :: Zikula Community

Get Zikula 1.3.3!New core, modules & more

Quick Links

Donate Today!Support your Software

Documentationlearn, share & develop

Watch

GitHub Core

Show your support for Zikula! Sign up at Github account and watch the Core project!

GitHub Modules

Forum Activity

mesteele101 responded to »ERR (3): E_USER_ERROR: Smarty error: [in pagesvar:pagesitem2en line XXX]…« 07:01 AM
mazdev responded to »Pages 2.5.0 and updating - Page not found« 06:41 AM
ehdwma created topic »Hide "Register new account" and change template to 3 col« 06:27 AM
mesteele101 responded to »Zikula 1.3.3 - Selecting a category in Pages not working« 01:29 AM
mdee created topic »How to implement returnpage ?« 01:00 AM
nestormateo responded to »Fillters in Clip« 24. May
damon responded to »Can the Updated Version Check be Turned Off (Z 1.3)« 24. May

» Visit forum | » View latest posts

Zikula Blog

Anatomy of Open Source Projects on Mar 07
Continuous Review on Mar 01
Not Invented Here on Feb 24
How to Contribute Your Code at Github on Jan 13
10 Steps to Coding-Nirvana: Tips for Successful Module Writing on Nov 12
Submitting Bug Report Tickets That Get Results on Aug 17
Cozi Tricks #1: Syntax Highlighting on Aug 07

accessing a database table

Print topic

Lobos

Rank: Expert

Registered: Dec 02, 2002

Last visit: Apr 30, 2010

Posts: 1474
Posted: Oct 22, 2004 - 06:42 AM

Hi there,

I have created a new database table for a module and I am trying to access it like this:

list($dbconn) = pnDBGetConn();
$pntable = pnDBGetTables();

$column = &$pntable['bookmarker_column'];
$result = $dbconn->Execute("
SELECT count(*)
FROM $pntable[bookmarker]");

It will not access, but if I change the instance to bookmarker to a name of one of the core modules like "comments" for instance, it accesses... shoudl this be working to access my new table or is there some other method in accessing the tables of new mods...

thanks

--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
HalbrookTech

Rank: Developer

Registered: Dec 31, 1969

Last visit: Jun 01, 2010

Posts: 6859
Posted: Oct 22, 2004 - 06:48 AM

Can you post the pnTables, just the relevant parts to the database?

--
Home Page | Find on Facebook | Follow on Twitter
Lobos

Rank: Expert

Registered: Dec 02, 2002

Last visit: Apr 30, 2010

Posts: 1474
Posted: Oct 22, 2004 - 06:50 AM

Here is the table:

#
# Table structure for table `nuke_bookmarker`
#

CREATE TABLE `nuke_bookmarker` (
`pn_id` int(10) NOT NULL auto_increment,
`pn_uid` varchar(32) NOT NULL default '',
`pn_bmname` varchar(32) NOT NULL default '',
`pn_bmURL` varchar(255) NOT NULL default '',
PRIMARY KEY (`pn_id`,`pn_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
Lobos

Rank: Expert

Registered: Dec 02, 2002

Last visit: Apr 30, 2010

Posts: 1474
Posted: Oct 22, 2004 - 07:04 AM

Yes it is definite - only core module tables can be accessed this way, i added another module and it would not access either...

--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
Simon

Rank: Legend

Registered: Dec 11, 2002

Last visit: Oct 21, 2009

Posts: 11674
Posted: Oct 22, 2004 - 08:16 AM

I would assign the SQL to a variable, and output the SQL and see what you get.

Have you got a pntables.php file with the appropriate table definintions (this is what Michael was referring to).

--
itbegins.co.uk - Zikula Consulting

birtwistle.me.uk - Personal Blog

Please read the Support Guide
Lobos

Rank: Expert

Registered: Dec 02, 2002

Last visit: Apr 30, 2010

Posts: 1474
Posted: Oct 22, 2004 - 09:18 AM

Yes here is the file:

function bookmarker_pntables()
{
// Initialise table array
$pntable = array();

// Get the name for the template item table. This is not necessary
// but helps in the following statements and keeps them readable
$bookmarker = pnConfigGetVar('prefix') . '_bookmarker';

// Set the table name
$pntable['bookmarker'] = $bookmarker;

// Set the column names. Note that the array has been formatted
// on-screen to be very easy to read by a user.
$pntable['bookmarker_column'] = array('uid' => $bookmarker . '.pn_uid',
'bmname' => $bookmarker . '.pn_bmname',
'bmURL' => $bookmarker . '.pn_bmURL');

// Return the table information
return $pntable;
}

--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
HalbrookTech

Rank: Developer

Registered: Dec 31, 1969

Last visit: Jun 01, 2010

Posts: 6859
Posted: Oct 22, 2004 - 09:35 AM

Can you post the function that you're using to try and get the data? The whole function?

--
Home Page | Find on Facebook | Follow on Twitter
Lobos

Rank: Expert

Registered: Dec 02, 2002

Last visit: Apr 30, 2010

Posts: 1474
Posted: Oct 23, 2004 - 06:14 AM

I ended up getting it to work like this:

function db_connect()
{
$result = pnDBGetConn();
return $result;
}

function add_bm()
{

if (!($conn = db_connect()))
return false;

$bookmarktable = pnConfigGetVar('prefix') . '_bookmarker';

// check not a repeat bookmark
$result = mysql_query("select * from ".$bookmarktable."
where pn_uid='$username'
and pn_bmURL='$new_url'");
}

I guess it is not the most orthodox of ways, but it works and I did try all the ways from the examples and other modules and it didn't...

So at least it works lol....

Regards

--
-Lobos
Professional PHP Framework Services: Concept, Development and Deployment
**unknown user**

Rank: Senior

Registered: Mar 16, 2002

Last visit: Oct 21, 2009

Posts: 2204
Posted: Oct 23, 2004 - 12:55 PM

...as Simon had hinted to: the prefix/table/column info is obtained from the pntables.php file.. if the table and colums were defined in that file you would have been able to use the first approach (ie: $pntable[bookmarker])

..either way though.. as long as it works.. hahaha

-IR
**unknown user**

Rank: Registered User

Registered: Mar 16, 2002

Last visit: Aug 22, 2005

Posts: 9
Posted: Jul 13, 2005 - 03:06 PM

i have same problem here, i am trying to access to database but no luck :/

Everything seems ok but it doesnt work..

----------------------------------------------------------

EDIT: Ok finally I got right query ;)

Code
list($dbconn) = pnDBGetConn();
$sql = "SELECT pn_ttest FROM nuke_testimonials WHERE pn_tid='1' ";
$result = $dbconn->Execute($sql);
list($ttest) = $result->fields;

print_r($ttest);

This one is worked well..
**unknown user**

Rank: Registered User

Registered: Mar 16, 2002

Last visit: Dec 27, 2006

Posts: 2
Posted: Dec 27, 2006 - 04:30 AM

It seems to be an unmentioned note that the pnGetDBTables doesn't pull in the added modules as well.. and from looking at the code it was never meant to (amateur here.. go easy.. ;).

Based on that thought, we can use the modname_pntables() to populate the field by performing the following steps:
a) Obtain the foldername for the module from website_prefix_modules (pnConfigGetVar('prefix') . '_modules';).
b) Perform an include on the module's pntables.php.
c) Call the module's modname_pntables() and put it in $pntables instead.

The reason I go through all this vs. just hardcoding is to do as the Postnuke teams wants/needs: stick to the API as closely as possible.

It took me a while to figure this one out - I hope either
a) Someone just knows something I don't (please post it! =)
b) Some future version of Postnuke will have an API function to access module data easier
c) Someone perhaps update the documentation (pnDBGetTables especially!) with this minor little dilemna and perhaps suggest an alternate method (even a link to this article).

Cheers!

Code
list($dbconn) = pnDBGetConn();
$pntable = pnDBGetTables();
$modtable = pnConfigGetVar('prefix') . '_modules';
$sql = "SELECT pn_directory FROM $modtable WHERE pn_name='" . $GLOBALS['ModName'] . "'";
$result = $dbconn->Execute($sql);
$temp = $result->fields;
$modfolder=$temp['0']; // we now have the folder name for the module.

include_once "modules/" . $modfolder . "/pntables.php"; // we now have access to module_pntables();
$pntables = modtrack_pntables();
print_r($pntables);

edited by: fnor_99, Dec 27, 2006 - 11:55 AM
Simon

Rank: Legend

Registered: Dec 11, 2002

Last visit: Oct 21, 2009

Posts: 11674
Posted: Dec 27, 2006 - 11:32 AM

There's an API that will do all that for you, pnModDBInfoLoad if I remember correctly.

--
itbegins.co.uk - Zikula Consulting

birtwistle.me.uk - Personal Blog

Please read the Support Guide
Paustian

Rank: Softmore

Registered: May 30, 2005

Last visit: May 31, 2010

Posts: 340
Posted: Dec 27, 2006 - 03:56 PM

A tip when working on creating SQL statments. Write your code like this.

Code
$dbconn =& pnDBGetConn(true);
$pntable =& pnDBGetTables();

$bookNameTable = $pntable['bookName'];
$bookNameList = &$pntable['book_name_list'];

$nextId = $dbconn->GenId($bookNameTable);

$sql = "INSERT INTO $bookNameTable (
$bookNameList[book_id], " .
"$bookNameList[book_name])
VALUES ( $nextId,'" . pnVarPrepForStore($name) . "')";
//print $sql; die;
$dbconn->Execute($sql);

By putting the SQL in a variable, if I am having problems getting the DB to take the statement, I can uncomment the line before the Execute statement. Then try it out in the database directly. I like to use phpMyAdmin for this. Almost always, there is a syntax error, and I can figure it out and change the code.

edited by: Paustian, Dec 28, 2006 - 03:57 AM
**unknown user**

Rank: Registered User

Registered: Mar 16, 2002

Last visit: Dec 27, 2006

Posts: 2
Posted: Dec 27, 2006 - 04:19 PM

Ahh.. Hammerhead found the best solution!

Do this instead, works like a charm:

Code
pnModDBInfoLoad($GLOBALS['ModName']);
$pntable = pnDBGetTables();

The original problem - pnDBGetTables didn't obtain the table information for 3rd party modules. pnModDBInfoLoad(Modulename) loads that info.