Re: [fw-db] Zend_Db_Select SQL 'unknown column' error

I might be stating the obvious, but can you check if the countries table has a countries_id field? A typo might be the cause of this.... Alternatively your join might be the problem whereas the query cannot pick up the countries table so that it can filter using country_id

What I usually do is some reverse engineering. I type the query in phpmyadmin (if you are using MySQL or its equivalent in other RDBMSs) and run it there. If there is a problem then I pick it up there and when I am satisfied with the results I construct the select object.

Crude but it works and it has saved me time in the long run.

I hope this helps

Nikolaos

On Tue, Feb 10, 2009 at 11:58, emorgan <mp@morganprecision.net> wrote:

Hi,
I am using a select object in a class that extends Zend_Db_Table_Abstract
... I have a complex query that is giving me this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column
'countries.countries_id' in 'where clause'

What is really stumping me is that when I echo out the select statement just
before calling fetchAll(), I get the output and run it in MySQL, and the
query works just fine with no errors. So I'm not sure if I've stumbled
across a bug in how the select object is being handled. Any suggestions?

Here is my code to build the select object:
$select = $this->select()
               ->from($this, array('inquiries_id', 'date_created'))
               ->setIntegrityCheck(false)
               ->joinUsing('contacts', 'contacts_id', $contactFields)
               ->joinUsing('addresses', 'addresses_id', $addressFields)
               ->join('countries',
                       'countries.countries_id = addresses.countries_id',
                       'country')
               ->join('users', 'users.users_id = inquiries.entered_by',
                       array('users_id', 'username'));

and it later gets modified with this:
$select->joinUsing('inquiries_catalogs', 'inquiries_id', '')
               ->where('catalogs_id = ?', $search->catalogs_id);

... and this:
$select->where('countries.countries_id = ?', 'GB');

Below is what I get when I echo the select object:

SELECT `inquiries`.`inquiries_id`, `inquiries`.`date_created`,
`contacts`.`name`, `contacts`.`company_name`, `addresses`.`address1`,
`addresses`.`address2`, `addresses`.`city`, `addresses`.`states_id`,
`addresses`.`state_desc`, `addresses`.`postal`, `countries`.`country`,
`users`.`users_id`, `users`.`username`
FROM `inquiries`
INNER JOIN `contacts` ON `contacts`.contacts_id = `inquiries`.contacts_id
INNER JOIN `addresses` ON `addresses`.addresses_id =
`inquiries`.addresses_id
INNER JOIN `countries` ON countries.countries_id = addresses.countries_id
INNER JOIN `users` ON users.users_id = inquiries.entered_by
INNER JOIN `inquiries_catalogs` ON `inquiries_catalogs`.inquiries_id =
`inquiries`.inquiries_id
WHERE (catalogs_id = '1') AND (countries.countries_id = 'GB')

Thanks!
Eugene
--
View this message in context: http://www.nabble.com/Zend_Db_Select-SQL-%27unknown-column%27-error-tp21937903p21937903.html
Sent from the Zend DB mailing list archive at Nabble.com.

--
The contents of this message may contain confidential or privileged information and is intended solely for the recipient(s). Use or distribution to and by any other party is not authorized. If you are not the intended recipient, copying, distribution or use of the contents of this information is prohibited.