Zikula Community

• Arthens
  
  

  • Rank: Developer
  • Registered: Mar 29, 2005
  • Last visit: Mar 13, 2010
  • Posts: 228

  Posted: Feb 05, 2008 - 01:49 AM

  I'm using DBUtill::executeSQL because I need to do a SELECT with a particular JOIN and I was not sure how to do it using selectExpandedObjectArray.
  
  The code is
  
  

  Code

  $sql = "SELECT pn_prodotti.*, pn_fatture_liste.prezzo as ordine_prezzo,  pn_fatture_liste.qta as ordine_qta,  pn_fatture_liste.omaggio as ordine_omaggio, pn_fatture_liste.totale as ordine_totale FROM pn_prodotti LEFT JOIN  pn_fatture_liste ON (pn_prodotti.id = pn_fatture_liste.prodotto AND pn_fatture_liste.fattura = '$fid') $where ORDER BY categoria, codice";
  
  
  $objArray = DBUtil::executeSQL($sql);

  
  
  The SQL query works fine if I run it using phpMyAdmin, but when I run it using DBUtill::executeSQL I get a strange result.
  I was expecting to get an array of arrays, with the common struture 'name' => 'value', instead I get this: (copied using the pndebug plugin )
  
  

  Code

  ADORecordSet_mysql Object (29)
  ->databaseType = "mysql"
  ->canSeek = true
  ->dataProvider = "native"
  ->fields = array (19)
    0 => "2"
    1 => "1234564645"
    2 => "Automobile"
    3 => "Ferrari"
    4 => "444"
    5 => "235g"
    6 => "10.456"
    7 => "0"
    8 => "1"
    9 => "1"
    10 => "A"
    11 => "2008-02-04 20:38:28"
    12 => "2"
    13 => "2008-02-04 20:41:07"
    14 => "2"
    15 => null
    16 => null
    17 => null
    18 => null
  ->blobSize = 100
  ->sql = "SELECT pn_prodotti.*, pn_fatture_liste.prezzo as ordine_prezzo, pn_fatture_liste.qta as ordine_qta, pn_fatture_liste.omaggio as ordine_omaggio, pn_fatture_liste.totale as ordine_totale FROM pn_prodotti LEFT JOIN pn_fatture_liste ON (pn_prodotti.id = pn_fatture_liste.prodotto AND pn_fatture_liste.fattura = '1') WHERE attivo = '1' ORDER BY categoria, codice"
  ->EOF = false
  ->emptyTimeStamp = " "
  ->emptyDate = " "
  ->debug = false
  ->timeCreated = 0
  ->bind = false
  ->fetchMode = 2
  ->connection = ADODB_mysql Object (86)
     ->databaseType = "mysql"
     ->dataProvider = "mysql"
     ->hasInsertID = true
     ->hasAffectedRows = true
     ->metaTablesSQL = "SHOW TABLES"
     ->metaColumnsSQL = "SHOW COLUMNS FROM `%s`"
     ->fmtTimeStamp = "'Y-m-d H:i:s'"
     ->hasLimit = true
     ->hasMoveFirst = true
     ->hasGenID = false
     ->isoDates = true
     ->sysDate = "CURDATE()"
     ->sysTimeStamp = "NOW()"
     ->hasTransactions = false
     ->forceNewConnect = true
     ->poorAffectedRows = true
     ->clientFlags = 0
     ->substr = "substring"
     ->nameQuote = "`"
     ->compat323 = false
     ->_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"
     ->database = "hair"
     ->host = "localhost"
     ->user = "XXXX"
     ->password = "XXXX"
     ->debug = false
     ->maxblobsize = 262144
     ->concat_operator = "+"
     ->length = "length"
     ->random = "rand()"
     ->upperCase = "upper"
     ->fmtDate = "'Y-m-d'"
     ->true = "1"
     ->false = "0"
     ->replaceQuote = "\'"
     ->charSet = false
     ->metaDatabasesSQL = ""
     ->uniqueOrderBy = false
     ->emptyDate = " "
     ->emptyTimeStamp = " "
     ->lastInsID = false
     ->hasTop = false
     ->readOnly = false
     ->genID = 0
     ->raiseErrorFn = false
     ->cacheSecs = 3600
     ->memCache = false
     ->memCacheHost = null
     ->memCachePort = 11211
     ->memCacheCompress = false
     ->arrayClass = "ADORecordSet_array"
     ->noNullStrings = false
     ->numCacheHits = 0
     ->numCacheMisses = 0
     ->pageExecuteCountRows = true
     ->uniqueSort = false
     ->leftOuter = false
     ->rightOuter = false
     ->ansiOuter = false
     ->autoRollback = false
     ->fnExecute = false
     ->fnCacheExecute = false
     ->blobEncodeType = false
     ->rsPrefix = "ADORecordSet_"
     ->autoCommit = true
     ->transOff = 0
     ->transCnt = 0
     ->fetchMode = 1
     ->null2null = "null"
     ->_oldRaiseFn = false
     ->_transOK = null
     ->_connectionID = Resource id #37
     ->_errorMsg = false
     ->_errorCode = false
     ->_queryID = Resource id #83
     ->_isPersistentConnection = false
     ->_bindInputArray = false
     ->_evalAll = false
     ->_affected = false
     ->_logsql = false
     ->_transmode = ""
     ->databaseName = "hair"
  ->_numOfRows = 3
  ->_numOfFields = 19
  ->_queryID = Resource id #81
  ->_currentRow = 0
  ->_closed = false
  ->_inited = true
  ->_obj = null
  ->_names = null
  ->_currentPage = -1
  ->_atFirstPage = false
  ->_atLastPage = false
  ->_lastPageNo = -1
  ->_maxRecordCount = 0
  ->datetime = false
  ->adodbFetchMode = 1

  
  
  
  Am I missing something? What's the correct way to run a SQL query?
  
  Thanks
  
  
  
  edited by: Arthens, Feb 05, 2008 - 02:50 PM
  
  --
  Zikula Italia
  SimpleGallery

  • 
  •  
• rgasch
  
  

  • Rank: Team Member
  • Registered: Jan 05, 2003
  • Posts: 775

  Posted: Feb 05, 2008 - 03:28 AM

  The behaviour you are seeing is correct. DBUtil::executeSQL returns a result set. In order to create a proper data-array from this result set you need to call DBUtil::marshallObjects().
  
  Since you are using a LEFT JOIN, you may want to use DBUtil::selectExpandedObjectArray() to achieve this; this method creates dynamic left-join statements from the $joinInfo parameter.
  
  Greetings
  R

  • 
  •  
• Arthens
  
  

  • Rank: Developer
  • Registered: Mar 29, 2005
  • Last visit: Mar 13, 2010
  • Posts: 228

  Posted: Feb 05, 2008 - 03:59 AM

  Thanks for the answer,
  
  I didn't use selectExpandedObjectArray because the relation between the 2 tables is not 1:1 but 1:N, and I need to transform it to 1:1 by adding a second clause in the JOIN like this:
  LEFT JOIN pn_fatture_liste ON (pn_prodotti.id = pn_fatture_liste.prodotto AND pn_fatture_liste.fattura = '$fid')
  I can't put it in the WHERE, otherwise it'd becomemore similar to a FULL INNER JOIN.
  
  Is this possible using DBUtil::selectExpandedObjectArray()?
  
  Thanks again
  
  
  
  edited by: Arthens, Feb 05, 2008 - 05:06 PM
  
  --
  Zikula Italia
  SimpleGallery

  • 
  •  
• rgasch
  
  

  • Rank: Team Member
  • Registered: Jan 05, 2003
  • Posts: 775

  Posted: Feb 05, 2008 - 05:43 AM

  Quote

  Is this possible using DBUtil::selectExpandedObjectArray()?

  
  I *think* you should be able to get this to work (I seem to dimly remember having doing this at some point in the past) by playing with the appropriate $joinInfo parameters (essendially you should be able to pass a literal where clause as part of your $joinInfo struct) but there's no guarantee that you *will* get it to work. If you get it to work, please report this fact back here.
  
  Greetings
  R

  • 
  •  
• Arthens
  
  

  • Rank: Developer
  • Registered: Mar 29, 2005
  • Last visit: Mar 13, 2010
  • Posts: 228

  Posted: Feb 05, 2008 - 08:30 AM

  It seems to work fine like this:
  

  Code

  $joinInfo[] = array ('join_table'         =>  'fatture_liste',
                           'join_field'         =>  array('totale', 'prezzo', 'qta', 'omaggio'),
                           'object_field_name'  =>  array('ordine_totale', 'ordine_prezzo', 'ordine_qta', 'ordine_omaggio'),
                           'compare_field_table'=>  'tbl.id AND a.fattura='.$fid,
                           'compare_field_join' =>  'prodotto');
  
      $objArray = DBUtil::selectExpandedObjectArray ('prodotti', $joinInfo, $where, 'categoria, id');

  
  
  I had to put "tbl.id AND a.fattura='.$fid" instead of "id AND fattura='.$fid" because in this case DBUtill can't translate the code using the pntables.php data.
  
  
  Bye, Giacomo
  
  --
  Zikula Italia
  SimpleGallery

  • 
  •  
•  
•  

Users on-line

• michiel,
• rgasch

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