Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.2.3

Bug #15463 Broken SELECT
Submitted: 2008-12-30 22:25 UTC
From: mpgjunky Assigned:
Status: Open Package: MDB_QueryTool (version 1.2.2)
PHP Version: 5.1.6 OS: Fedora 6
Roadmaps: (Not assigned)    
Subscription  


 [2008-12-30 22:25 UTC] mpgjunky (Michael Mussulis)
Description: ------------ I get a broken select when I use left joins and multiple tables. The code is show below: $ticket = new Ticket($dsn, null, 2); $ticket->useResult('object'); $ticket->setSelect("client_tickets.*, users_table.*,prices.*, displaytext, count(wp.user_id) as wnp, count(pp.user_id) as pnp"); $ticket->addLeftJoin("users_table", "users_table.userid = client_tickets.userid"); $ticket->addLeftJoin("prices", "users_table.acc_type = prices.acc_type"); $ticket->addLeftJoin("payment_worldpay as wp", "users_table.userid = wp.user_id"); $ticket->addLeftJoin("paypal_payments as pp", "users_table.userid = pp.user_id"); $ticket->setGroup("userid"); $trec = $ticket->get($id)->fetchRow(); See actual result for sql statement. There are in fact two problems (if I am not wrong): 1. Spaces in front of 'table.*' 2. Column prefixing statement in Query.php line 2097, _buildSelect() function (which also affects returned result as object). I fixed the problems by: 1. Add: $tables = array_map('trim', $tables) after the tables have been extracted into $tables. 2. Modify line 2097 from: $cols[$aTable][] = $aTable. '.' .$colName . ' AS '. $this->_quoteIdentifier('_'. $this->getTableShortName($aTable) .'_'. $colName); TO $cols[$aTable][] = $aTable. '.' .$colName . ' AS '. $this->_quoteIdentifier($this->getTableShortName($aTable) .'_'. $colName); I am not sure if the problems I experienced are a result of me not using the class properly or not. If this is the case please let me know. Cheers, Michael. Expected result: ---------------- A proper SQL statement, even if column names are being prefixed with table name as show below: SELECT client_tickets.`id` AS `id`,client_tickets.`id_cat` AS `id_cat`,client_tickets.`description` AS `description`,client_tickets.`status` AS `status`,client_tickets.`userid` AS `userid`,client_tickets.`adate` AS `adate`,client_tickets.`t_status` AS `t_status`,users_table.`id_users_table` AS `users_id_users_table`,users_table.`name` AS `users_name`,users_table.`address` AS `users_address`,users_table.`town` AS `users_town`,users_table.`country` AS `users_country`,users_table.`email` AS `users_email`,users_table.`zipcode` AS `users_zipcode`,users_table.`userid` AS `users_userid`,users_table.`pwd` AS `users_pwd`,users_table.`sex` AS `users_sex`,users_table.`birth_date` AS `users_birth_date`,users_table.`acc_type` AS `users_acc_type`,users_table.`status` AS `users_status`,users_table.`creation_date` AS `users_creation_date`,users_table.`hash` AS `users_hash`,users_table.`recurr` AS `users_recurr`,users_table.`referral` AS `users_referral`,users_table.`activated` AS `users_activated`,users_table.`expiry` AS `users_expiry`,users_table.`friend` AS `users_friend`,users_table.`comments` AS `users_comments`,users_table.`dutch_id` AS `users_dutch_id`,users_table.`remote_addr` AS `users_remote_addr`,users_table.`period` AS `users_period`,prices.`id` AS `prices_id`,prices.`acc_type` AS `prices_acc_type`,prices.`price` AS `prices_price`,prices.`d_price` AS `prices_d_price`,prices.`currency` AS `prices_currency`,prices.`enabled` AS `prices_enabled`,prices.`display` AS `prices_display`,prices.`displaytext` AS `prices_displaytext`,prices.`displayto` AS `prices_displayto`,prices.`type` AS `prices_type`,prices.`ns_type` AS `prices_ns_type`,prices.`days` AS `prices_days`,`displaytext`,count(wp.user_id) AS `wnp`,count(pp.user_id) AS `pnp` FROM `client_tickets` LEFT JOIN users_table ON users_table.userid = client_tickets.userid LEFT JOIN prices ON users_table.acc_type = prices.acc_type LEFT JOIN payment_worldpay as wp ON users_table.userid = wp.user_id LEFT JOIN paypal_payments as pp ON users_table.userid = pp.user_id GROUP BY client_tickets.userid Actual result: -------------- SELECT client_tickets.`id` AS `id`,client_tickets.`id_cat` AS `id_cat`,client_tickets.`description` AS `description`,client_tickets.`status` AS `status`,client_tickets.`userid` AS `userid`,client_tickets.`adate` AS `adate`,client_tickets.`t_status` AS `t_status`,users_table.`id_users_table` AS `_table_id_users_table`,users_table.`name` AS `_table_name`,users_table.`address` AS `_table_address`,users_table.`town` AS `_table_town`,users_table.`country` AS `_table_country`,users_table.`email` AS `_table_email`,users_table.`zipcode` AS `_table_zipcode`,users_table.`userid` AS `_table_userid`,users_table.`pwd` AS `_table_pwd`,users_table.`sex` AS `_table_sex`,users_table.`birth_date` AS `_table_birth_date`,users_table.`acc_type` AS `_table_acc_type`,users_table.`status` AS `_table_status`,users_table.`creation_date` AS `_table_creation_date`,users_table.`hash` AS `_table_hash`,users_table.`recurr` AS `_table_recurr`,users_table.`referral` AS `_table_referral`,users_table.`activated` AS `_table_activated`,users_table.`expiry` AS `_table_expiry`,users_table.`friend` AS `_table_friend`,users_table.`comments` AS `_table_comments`,users_table.`dutch_id` AS `_table_dutch_id`,users_table.`remote_addr` AS `_table_remote_addr`,users_table.`period` AS `_table_period`,prices.`id` AS `_prices_id`,prices.`acc_type` AS `_prices_acc_type`,prices.`price` AS `_prices_price`,prices.`d_price` AS `_prices_d_price`,prices.`currency` AS `_prices_currency`,prices.`enabled` AS `_prices_enabled`,prices.`display` AS `_prices_display`,prices.`displaytext` AS `_prices_displaytext`,prices.`displayto` AS `_prices_displayto`,prices.`type` AS `_prices_type`,prices.`ns_type` AS `_prices_ns_type`,prices.`days` AS `_prices_days`,`displaytext`,count(wp.user_id) AS `wnp`,count(pp.user_id) AS `pnp` FROM `client_tickets` LEFT JOIN users_table ON users_table.userid = client_tickets.userid LEFT JOIN prices ON users_table.acc_type = prices.acc_type LEFT JOIN payment_worldpay as wp ON users_table.userid = wp.user_id LEFT JOIN paypal_payments as pp ON users_table.userid = pp.user_id GROUP BY client_tickets.userid

Comments