Zikula Community

• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 02, 2009 - 07:31 AM

  Hi,
  
  I'm having trouble getting info from a table in my database.
  
  Here's the function in pnadmin.php...
  

  Code

  <?php
  
  function pntarot_admin_main()
   
  list($dbconn) = pnDBGetConn();    
  $pntable = pnDBGetTables();
  $prefix = pnConfigGetVar('prefix');
  $pr="_pntarot_table";
  $elliot= $prefix.$pr;
  
  $recordSet = &$dbconn->Execute("SELECT * FROM $elliot order by columnid DESC");
  
  while (!$recordSet->EOF)
  
  {
  
  $pnRender = new pnRender('pntarot');
  $pnRender->assign('recordSet', $recordSet);
  return $pnRender->fetch('adminview.html');
  $recordSet->MoveNext();
  
  }
  
  ?>

  
  
  Here's the code for the above template (adminview.html)...
  

  Code

  <!--[foreach  item=aview from=$recordSet]-->
  
  <!--[$aview[0]]--> | <!--[$aview[1]]--> | <!--[$aview[2]]--> | <!--[$aview[3]]--> | <!--[$aview[4]]--> |
  <!--[$aview[5]]--> | <!--[$aview[6]]--><br><br>
  
  <!--[/foreach]-->

  
  
  The problem is there is only 1 row in the data base with the following info in it:
  

  Code

  2 | 2 | admin | | 26,2,28,63,75,70,76,25,12,46,45 | 1000,2,2,2,2,2,2,2,2,1,2 | 2009-01-02 18:42:22

  
  
  However, when the template is called, I not only get the above info, I get a mess of other stuff on different lines as follows:
  
  

  Code

  n | a | t | i | v | e |
  
  THIS ONE IS CORRECT:
  2 | 2 | admin | | 26,2,28,63,75,70,76,25,12,46,45 | 1000,2,2,2,2,2,2,2,2,1,2 | 2009-01-02 18:42:22
  
  | | | | | |
  
  | | | | | |
  
  S | E | L | E | C | T |
  
  | | | | | |
  
  & | n | b | s | p | ; |
  
  & | n | b | s | p | ; |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  | | | | | |
  
  m | y | s | q | l | |
  
  | | | | | |

  
  
  I am using PHP 4.3.9, MySQL 4.1 and CentOS4.
  
  Can anyone help?
  
  Thanks in advance.
  
  --
  EMZ

  • 
  •  
• jpritch
  
  

  • Rank: Registered User
  • Registered: Jul 27, 2008
  • Last visit: May 04, 2009
  • Posts: 12

  Posted: Jan 02, 2009 - 02:17 PM

  I think you need to create a Smarty friendly array from your ADODB recordset.
  
  This worked for me, although it maybe horribly inefficent. Hopefully someone can post something more inline with best practice...
  
  

  Code

  $result = DBUtil::executeSQL($sql);
      $profiles = array();
      if(!$result->EOF){ 
          while (!$result->EOF) {
              array_push($profiles, array(
                                          "id"                =>  $result->fields[0],
                                          "organisation_name" =>  $result->fields[1],
                                          "industry_sector"   =>  $result->fields[2]
                                          )
                          );
              $result->MoveNext();
          }
      }

  
  
  edit: actually for simple SQL just use DBUtil::selectObjectArray() which just achieves the result in one step - a Smarty friendly array. I only used the looping approach if the SQL was difficult to implement via DBUtil, eg a few joins etc.
  
  
  
  edited by: jpritch, Jan 02, 2009 - 09:50 PM

  • 
  •  
• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 03, 2009 - 06:44 AM

  JP,
  
  Thanks so much for the quick reply... As a reminder, I am using PHP 4.3.9, MySQL 4.1 and CENTOS4.
  
  Neither solution worked for me.
  
  To review, I am trying to retrieve data in a template from a MySQL database with a very simple query. The table in Zikula I am trying to access is called zk_pntarot_table. There are 7 fields in the table called: 1) columnid, 2) columnnumber, 3) columnname, 4) columnquestion, 5) columnrandarray, 6) columnrandarrayr, and 7) columndate.
  
  Togethe, these fields comprise 1 row (or record). At present, I have 2 rows in MySQL.
  
  I created a template to see the info in the 2 rows (like in a spreadsheet).
  
  As you suggested I used the DBUtil to get all rows as an array. Here's the new code I used for pnadmin.php (where I tried to use DBUtil):
  

  Code

  <?php
  
  function pntarot_admin_main()
   
  {
     if (!SecurityUtil::checkPermission('pntarot::', '::', ACCESS_ADMIN)) {
          return pnVarPrepHTMLDisplay(_V4B_PNTAROT_NOAUTH);
      }
  
  list($dbconn) = pnDBGetConn();    
      $pntable = pnDBGetTables();
       $recordSet = array();
        $recordSet = DBUtil::selectObjectArray ('pntarot_table');
     
  $pnRender = new pnRender('pntarot');
  $pnRender->assign('recordSet', $recordSet);
  return $pnRender->fetch('adminview.html');
  
  }
  
  ?>

  
  
  Here's the code for the template adminview.html:
  

  Code

  <!--[foreach  item=aview from=$recordSet]-->
  
  <!--[$aview[0]]--> | <!--[$aview[1]]--> | <!--[$aview[2]]--> | <!--[$aview[3]]--> | <!--[$aview[4]]--> | <!--[$aview[5]]--> | <!--[$aview[6]]--><br><br>
  
  <!--[/foreach]-->

  
  
  The result I am now getting is as follows:
  

  Code

  | | | | | |
  
  | | | | | |

  
  
  The difference from what I was getting in my first post is that now the code is finding the correct number of rows (2), but not passing the info in $recordSet over to the template...
  
  I know I must be missing something simple here, but for the life of me, I don't see how to do this...
  
  Any further help would be greatly appreciated!!! And I really mean that! I'll owe you one...
  
  Thanks again.
  
  All the best.
  
  EMZ
  
  --
  EMZ

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Jan 03, 2009 - 10:10 AM

  You have to take in account, that DBUtil marshall the adoDB resultSet and index each field with the field names defined in the pntables.php, so you'll be able to use friendly code like:
  

  Code

  <!--[$aview.id]--> :: <!--[$aview.title]-->

  
  
  I would recommend you to use this nested foreach instead the one you posted, to see the recordSet structure:
  

  Code

  <!--[foreach key='number' item=aview from=$recordSet]-->
  $recordSet.<!--[$number]-->:<br />
  <!--[foreach key='key' item='value' from=$aview]-->
  $aview.<!--[$key]--> => <!--[$value]--><br />
  <!--[/foreach]-->
  <br /><br />
  <!--[/foreach]-->

  
  
  Tell us how it's going
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 03, 2009 - 02:51 PM

  Hi Nestor... Thanks much for helping...
  
  I used your nested foreach in my template, and here's what I get:
  
  

  Code

  $recordSet.dataProvider:
  $aview.0 => native
  
  
  $recordSet.fields:
  $aview.0 => 2
  $aview.1 => 2
  $aview.2 => admin
  $aview.3 =>
  $aview.4 => 26,2,28,63,75,70,76,25,12,46,45
  $aview.5 => 1000,2,2,2,2,2,2,2,2,1,2
  $aview.6 => 2009-01-02 18:42:22
  
  
  $recordSet.blobSize:
  $aview.0 => 100
  
  
  $recordSet.canSeek:
  $aview.0 => 1
  
  
  $recordSet.sql:
  $aview.0 => SELECT columnid, columnnumber, columnname, columnquestion, columnrandarray, columnrandarrayr, columndate FROM zk_pntarot_table order by columnid DESC
  
  
  $recordSet.EOF:
  $aview.0 =>
  
  
  $recordSet.emptyTimeStamp:
  $aview.0 =>  
  
  
  $recordSet.emptyDate:
  $aview.0 =>  
  
  
  $recordSet.debug:
  $aview.0 =>
  
  
  $recordSet.timeCreated:
  $aview.0 => 0
  
  
  $recordSet.bind:
  $aview.0 =>
  
  
  $recordSet.fetchMode:
  $aview.0 => 2
  
  
  $recordSet.connection:
  INTENTIONALLY DELETED
  
  $recordSet._numOfRows:
  $aview.0 => 2
  
  
  $recordSet._numOfFields:
  $aview.0 => 7
  
  
  $recordSet._queryID:
  $aview.0 => Resource id #90
  
  
  $recordSet._currentRow:
  $aview.0 => 0
  
  
  $recordSet._closed:
  $aview.0 =>
  
  
  $recordSet._inited:
  $aview.0 => 1
  
  
  $recordSet._obj:
  
  
  $recordSet._names:
  
  
  $recordSet._currentPage:
  $aview.0 => -1
  
  
  $recordSet._atFirstPage:
  $aview.0 =>
  
  
  $recordSet._atLastPage:
  $aview.0 =>
  
  
  $recordSet._lastPageNo:
  $aview.0 => -1
  
  
  $recordSet._maxRecordCount:
  $aview.0 => 0
  
  
  $recordSet.datetime:
  $aview.0 =>
  
  
  $recordSet.databaseType:
  $aview.0 => mysql
  
  
  $recordSet.adodbFetchMode:
  $aview.0 => 1

  
  
  I just am interested in accessing the $recordSet.fields as you mentioned... like
  

  Code

  <--[$aview.columnid]--> |  <--[$aview.columnnumber]-->  etc.

  My pntables are as follows:
  

  Code

  <?php
  
  function pntarot_pntables()
  {
      // Initialise table array
      $pntables = array();
      $prefix = pnConfigGetVar('prefix');
  
          $table = $prefix . '_pntarot_table';
      $pntables['pntarot_table'] = $table;
      $columns = array (
          'id'    =>  'columnid',
          'number'    =>  'columnnumber',
          'name'  =>  'columnname',
          'question'  =>  'columnquestion',
          'randarray' =>  'columnrandarray',
          'randarrayr'    =>  'columnrandarrayr',
          'date'  =>  'columndate'
          );
      $pntables['pntarot_table_column'] = $columns;
  
  
  
      return $pntables;
  }
  
  ?>

  
  
  Note, in the dump above the pntables.php file, there should be 2 Records that have $recordSet.fields, but only one is showing up...
  
  What am I missing here?
  
  
  
  edited by: EMZ, Jan 03, 2009 - 09:47 PM
  
  --
  EMZ

  • 
  •  
• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 03, 2009 - 07:15 PM

  With all due respect, before Zikula, ADODB and Smarty, retrieving info from a MySQL database was easy...
  
  --
  EMZ

  • 
  •  
• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 04, 2009 - 12:47 AM

  I am running PHP 4.3.9, MySQL 4.1 on a CentOS4 server.
  
  I have tried to make my post more clear and have changed some of the code from above. I still cannot access data in a table in my MySQL database which has 7 columns. The table name is zk_pntarot_table. The columns' names are: 1) columnid, 2) columnnumber, 3) columnname, 4) columnquestion, 5) columnrandarray, 6) columnrandarrayr, and 7) columnevent. Each column contains an array... For example, #6) has an array of ten numbers separated by commas (200,1,2,1,2,1,2,1,2,1).
  
  First, I prepared my function to obtain the MySQL data in pnadmin.php as follows:
  
  

  Code

  function pntarot_admin_main()
   
  {
  
     if (!SecurityUtil::checkPermission('pntarot::', '::', ACCESS_ADMIN)) {
          return pnVarPrepHTMLDisplay(_V4B_PNTAROT_NOAUTH);
      }
  
  list($dbconn) = pnDBGetConn();    
  $pntable = pnDBGetTables();
  $prefix = pnConfigGetVar('prefix');
  $pr="_pntarot_table";
  $elliot= $prefix.$pr;
  $recordSet=array();
      $recordSet = &$dbconn->Execute("SELECT columnid, columnnumber, columnname, columnquestion, columnrandarray, columnrandarrayr, columndate FROM $elliot order by columnid DESC");
   while (!$recordSet->EOF)
  {    
  $mytid  = $recordSet->fields[0];
  $mynumber = $recordSet->fields[1];
  $myname = $recordSet->fields[2];
  $myquestion = $recordSet->fields[3];
  $myrandarray = $recordSet->fields[4];
  $myrandarrayr = $recordSet->fields[5];
  $myevent = $recordSet->fields[6];
  
  $pnRender = new pnRender('pntarot');
  $pnRender->assign('recordSet', $recordSet);
  $pnRender->assign('mytid', $mytid);
  $pnRender->assign('mynumber', $mynumber);
  $pnRender->assign('myname', $myname);
  $pnRender->assign('myquestion', $myquestion);
  $pnRender->assign('myrandarray', $myrandarray);
  $pnRender->assign('myrandarrayr', $myrandarrayr);
  $pnRender->assign('myevent', $myevent);
  
  
  $recordSet->MoveNext();
  return $pnRender->fetch('adminview.html');
  
    }
  
  }

  
  
  Next I prepared the template adminview.html as listed in the fetch command above...
  
  

  Code

  <!--[$mytid]--><br>
  <!--[$mynumber]--><br>  
  <!--[$myname]--><br>
  <!--[$myquestion]--><br>
  <!--[$myrandarray]--><br>
  <!--[$myrandarrayr]--><br>
  <!--[$myevent]--><br><br><br>

  
  
  Unfortunately, the template only displays one row in the recordset. For some reason, my code is over writing the results which come before the last row in the data base is processed. Also, I am really not entirely clear on how to use the Smarty foreach command for nested data in MySQL.
  
  Nestor and JP, if there's anyway you fellows could give me some more direction, I'd be very thankful.
  
  
  
  edited by: EMZ, Jan 04, 2009 - 09:24 PM
  
  --
  EMZ

  • 
  •  
• r3ap3r
  
  

  • Rank: Developer
  • Registered: Jan 14, 2004
  • Last visit: Oct 21, 2009
  • Posts: 348

  Posted: Jan 04, 2009 - 06:45 PM

  The problem appears to be in your while loop. You should create the $pnRender instance outside the loop. You also call "return" inside the loop which would mean you would never get more than one row. Of course even if you moved the return outside the loop you would still only get one row because you are assigning each row returned from the database to the same set of variables which will only retain the last value.
  
  You need to loop through the values returned from the database and put those in an array, then pass the completed array to the $pnRender->assign() call (you are currently trying to do it in one step). Then you can access the array inside the template.
  
  Gotta run right now, if I get a minute I'll see about posting a code example.
  -Chris

  • 
  •  
• r3ap3r
  
  

  • Rank: Developer
  • Registered: Jan 14, 2004
  • Last visit: Oct 21, 2009
  • Posts: 348

  Posted: Jan 04, 2009 - 08:00 PM

  K. Here you go.
  Your template was just fine.
  

  Code

  <!--[foreach  item=aview from=$recordSet]-->
  <!--[$aview[0]]--> | <!--[$aview[1]]--> | <!--[$aview[2]]--> | <!--[$aview[3]]--> | <!--[$aview[4]]--> |
  <!--[$aview[5]]--> | <!--[$aview[6]]--><br><br>
  <!--[/foreach]-->

  
  
  Your admin should look like this:
  

  Code

  <?php
  function pntarot_admin_main()
  {
      list($dbconn) = pnDBGetConn();      
      $pntable = pnDBGetTables();
      $prefix = pnConfigGetVar('prefix');
      $pr="_pntarot_table";
      $elliot= $prefix.$pr;    
      $recordSet = &$dbconn->Execute("SELECT * FROM $elliot order by columnid DESC
  ");
      $pnRender = new pnRender('pntarot');            
      while (!$recordSet->EOF)            
      {            
          $records[] = $recordSet->fields;
          $recordSet->MoveNext();
      }    
      $pnRender->assign('recordSet', $records);
      return $pnRender->fetch('adminview.html');
  }
  ?>

  
  
  You probably want to spend the time learning the DBUtil class methods, but the way you did it works fine in Zikula 1.1.0 with the changes to the looping structure that I listed in this post.
  
  You should also consider that if you ever add a field to your table at any location other than the end this will stop working since you are referencing everything by index number instead of column name. And of course the issue that anyone trying to debug your code without the database schema won't have a clue what $aview[1] is.
  
  -Chris

  • 
  •  
• EMZ
  
  

  • Rank: Freshman
  • Registered: Dec 12, 2002
  • Last visit: Apr 07, 2010
  • Posts: 55

  Posted: Jan 04, 2009 - 08:15 PM

  Chris,
  
  Thanks so much for your help! That worked perfectly...
  
  I O U!
  
  Thanks again, contact me at legal@cyberlaw.info if you need a freebie with some legal work (I practice cyberlaw).
  
  
  
  edited by: EMZ, Jan 05, 2009 - 12:18 AM
  
  --
  EMZ

  • 
  •  
•  
•  

Users on-line

• rgasch

This list is based on users active over the last 60 minutes.