Zikula Community

• uheweb
  
  

  • Rank: Helper
  • Registered: Sep 16, 2004
  • Last visit: Oct 21, 2009
  • Posts: 731

  Posted: Jul 10, 2008 - 01:32 PM

  Tried using DBUtil::selectFieldMaxArray to get a count for categories. It was only ever returning the first row.
  
  So, tried:
  
  

  Quote

  $SQL = "select pn_cid,COUNT(pn_cid)
  from pn_admin_module
  GROUP BY pn_cid;";
  $result = DBUtil::executeSQL ($SQL);

  
  ( change prefix if testing from pn_ to whatever_ )
  
  This, again, only returned a single row even though there should be 6 rows (admin categories) in my particular Zikula install.
  
  The same SQL in any tool returns the correct number of rows - all the admin categories and the # of modules in each one.
  
  So, is GROUP BY restricted to a single row somewhere in DBUtil or ADOdb? Even with an explicit LIMIT 0,50 it still only returns one row.
  
  Can someone else test this and let me know if they see this behavior? Is there an ADOdb or DBUtil problem with GROUP BY? I followed DBUtil through and didn't see anywhere it should have problems. The SQL returned in the result set, stripped and applied directly to MySQL via another query tool returns the correct result - 6 rows with various counts.
  
  I noticed the bug report had a bug on another COUNT DBUtil function:
  
  http://code.zikula.org/core/ticket/49
  
  Perhaps the same thing?
  
  Thanks for any insight or testing...this has been driving me crazy....I may need to borrow simon's avatar
  
  NCM
  SwitchBit / UHEweb

  • 
  •  
• uheweb
  
  

  • Rank: Helper
  • Registered: Sep 16, 2004
  • Last visit: Oct 21, 2009
  • Posts: 731

  Posted: Jul 10, 2008 - 01:50 PM

  Something else:
  
  The DBUtil / result set meta stuff says that there are the correct number of rows - 5:
  
  

  Quote

  adorecordset_mysql Object ( [dataProvider] => native [fields] => Array ( [0] => 1 [1] => 9 ) [blobSize] => 100 [canSeek] => 1 [SQL] => select pn_cid,COUNT(pn_cid) from pn_admin_module GROUP BY pn_cid; [EOF] => [emptyTimeStamp] => [emptyDate] => [debug] => [timeCreated] => 0 [bind] => [fetchMode] => 2 [connection] => adodb_mysql Object ( [dataProvider] => mysql [databaseType] => mysql [database] => uploader [host] => localhost [user] => xxxxxx [password] => xxxxxx [debug] => [maxblobsize] => 262144 [concat_operator] => + [substr] => substring [length] => length [random] => rand() [upperCase] => upper [fmtDate] => 'Y-m-d' [fmtTimeStamp] => 'Y-m-d H:i:s' [true] => 1 [false] => 0 [replaceQuote] => \' [nameQuote] => ` [charSet] => [metaDatabasesSQL] => [metaTablesSQL] => SHOW TABLES [uniqueOrderBy] => [emptyDate] => [emptyTimeStamp] => [lastInsID] => [hasInsertID] => 1 [hasAffectedRows] => 1 [hasTop] => [hasLimit] => 1 [readOnly] => [hasMoveFirst] => 1 [hasGenID] => [hasTransactions] => [genID] => 0 [raiseErrorFn] => [isoDates] => 1 [cacheSecs] => 3600 [memCache] => [memCacheHost] => [memCachePort] => 11211 [memCacheCompress] => [sysDate] => CURDATE() [sysTimeStamp] => NOW() [arrayClass] => ADORecordSet_array [noNullStrings] => [numCacheHits] => 0 [numCacheMisses] => 0 [pageExecuteCountRows] => 1 [uniqueSort] => [leftOuter] => [rightOuter] => [ansiOuter] => [autoRollback] => [poorAffectedRows] => 1 [fnExecute] => [fnCacheExecute] => [blobEncodeType] => [rsPrefix] => ADORecordSet_ [autoCommit] => 1 [transOff] => 0 [transCnt] => 0 [fetchMode] => 1 [null2null] => null [_oldRaiseFn] => [_transOK] => [_connectionID] => Resource id #38 [_errorMsg] => [_errorCode] => [_queryID] => Resource id #85 [_isPersistentConnection] => [_bindInputArray] => [_evalAll] => [_affected] => [_logsql] => [_transmode] => [metaColumnsSQL] => SHOW COLUMNS FROM `%s` [forceNewConnect] => 1 [clientFlags] => 0 [compat323] => [_genIDSQL] => update %s set id=LAST_INSERT_ID(id+1); [_genSeqSQL] => create table %s (id int not null) [_genSeqCountSQL] => select count(*) from %s [_genSeq2SQL] => insert into %s values (%s) [_dropSeqSQL] => drop table %s [databaseName] => uploader ) [_numOfRows] => 5 [_numOfFields] => 2 [_queryID] => Resource id #85 [_currentRow] => 0 [_closed] => [_inited] => 1 [_obj] => [_names] => [_currentPage] => -1 [_atFirstPage] => [_atLastPage] => [_lastPageNo] => -1 [_maxRecordCount] => 0 [datetime] => [databaseType] => mysql [adodbFetchMode] => 1 )

  
  
  [_numOfRows] => 5
  
  
  
  edited by: uheweb, Jul 10, 2008 - 06:50 PM

  • 
  •  
• cyber_wolf
  
  

  • Rank: Developer
  • Registered: Jun 03, 2002
  • Last visit: Dec 21, 2009
  • Posts: 284

  Posted: Jul 10, 2008 - 09:06 PM

  I've found in using executeSQL in a module I'm working on, I had to pass the result to marshallObjects to get the actual result.
  
  --
  cyber_wolf
  www.bkbsolutions.com - My Zikula module development site.

  • 
  •  
• nestormateo
  
  

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

  Posted: Jul 10, 2008 - 09:48 PM

  Yes, marshallObjects do the second part,
  but currently i see that you're getting 2 rows only:
  

  uheweb

  [fields] => Array ( [0] => 1 [1] => 9 )

  
  or that print_r was after some processing?
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• uheweb
  
  

  • Rank: Helper
  • Registered: Sep 16, 2004
  • Last visit: Oct 21, 2009
  • Posts: 731

  Posted: Jul 11, 2008 - 01:35 AM

  Not two rows....single row with two fields - ie, Admin Category 1 with a COUNT of 9
  

  Quote

  [fields] => Array ( [0] => 1 [1] => 9 )

  
  
  There is no further processing...the output is
  
  

  Quote

  print_r($result);

  
  
  
  Thanks cyber_wolf - I didn't realize I have to marshall the result object with a pure executeSQL
  
  Its' working now...:
  
  

  Quote

  $SQL = "select pn_cid,COUNT(pn_cid)
  from pn_admin_module
  GROUP BY pn_cid;";
  $result = DBUtil::executeSQL ($SQL);
  $rows = DBUtil::marshallObjects($result,array('admincat','countcat'));
  print_r($rows);

  
  
  NCM
  Switchbit / UHE
  

  • 
  •  
• cyber_wolf
  
  

  • Rank: Developer
  • Registered: Jun 03, 2002
  • Last visit: Dec 21, 2009
  • Posts: 284

  Posted: Jul 11, 2008 - 05:24 AM

  I was getting the same results as you were when using executeSQL. So I started looking at how the pre-built select functions were working and noticed that they were building their SQL statement and sending that to the executeSQL function. Then they were sending that result to the marshallObjects function. So, after sending my results to marshallObjects, I got the result I was expecting.
  
  --
  cyber_wolf
  www.bkbsolutions.com - My Zikula module development site.

  • 
  •  
•  
•  

Users on-line

• michiel

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