Bad SQL query Column 'zone' in order clause is ambiguous

[M. Zeeshan Khan Bawany]

I have installed PS1.6 and edited Zones of my choice, now Localization>Countries page in BO gives this error:

 

• Bad SQL query
  Column 'zone' in order clause is ambiguous

 

Help appreciated in advance.

[vekia]

please turn on error reporting,

then you will see script with mysql query where the problem appears,

it is first step in debug process and it is necessary

[sooroos]

I have reproduced this error
 

[PrestaShopDatabaseException]
Column 'zone' in order clause is ambiguous

 
<pre>
		SELECT SQL_CALC_FOUND_ROWS
		a.`id_country`,b.name as name,`iso_code`,`call_prefix`,z.id_zone as zone,a.active as active
		, z.`name` AS zone
		FROM `ps160_country` a
		LEFT JOIN `ps160_country_lang` b ON (b.`id_country` = a.`id_country` AND b.`id_lang` = 2)
		LEFT JOIN `ps160_zone` z ON (z.`id_zone` = a.`id_zone`) 
		
		WHERE 1 
		
		
		ORDER BY `zone` desc LIMIT 0,1000</pre>


at line 623 in file classes/db/Db.php

<pre>
617. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
618. 		}
619. 		else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
620. 		{
621. 			if ($sql)
622. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
623. 			throw new PrestaShopDatabaseException($this->getMsgError());
624. 		}
625. 	}
626. 
627. 	/**
</pre>

[intermediadorpopular]

 

 

I have reproduced this error

 

[PrestaShopDatabaseException]
Column 'zone' in order clause is ambiguous

 
<pre>
		SELECT SQL_CALC_FOUND_ROWS
		a.`id_country`,b.name as name,`iso_code`,`call_prefix`,z.id_zone as zone,a.active as active
		, z.`name` AS zone
		FROM `ps160_country` a
		LEFT JOIN `ps160_country_lang` b ON (b.`id_country` = a.`id_country` AND b.`id_lang` = 2)
		LEFT JOIN `ps160_zone` z ON (z.`id_zone` = a.`id_zone`) 
		
		WHERE 1 
		
		
		ORDER BY `zone` desc LIMIT 0,1000</pre>


at line 623 in file classes/db/Db.php

<pre>
617. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
618. 		}
619. 		else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
620. 		{
621. 			if ($sql)
622. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
623. 			throw new PrestaShopDatabaseException($this->getMsgError());
624. 		}
625. 	}
626. 
627. 	/**
</pre>

Como consertou este erro?

 

[sooroos]

english please

[intermediadorpopular]

english please

 

How you fixed it?

[sooroos]

No

[vekia]

hello sooroos

due to the fact that you aren't author of this topic i have to ask something in addition that was asked before

 

what prestashop version you use?

[intermediadorpopular]

hello sooroos

due to the fact that you aren't author of this topic i have to ask something in addition that was asked before

 

what prestashop version you use?

 

If you can, put the solution, I have similar problem in 1.6

[sooroos]

vekia, it is 1.6.0.5

[vekia]

and you see this error when you're trying to open country "edit" page?

[sooroos]

yes. but first i think i have deleted a few

[dmarsh]

and you see this error when you're trying to open country "edit" page?

I have the same problem and the error now appears as soon as I open Localization Countries. The error appeared when I clicked on the zone column to organize by the zone. Now I am unable to open it again to change it back to sort by country. Is there a way to reset the order by or better yet not have it throw an error for a simple form change?

[vekia]

what is your ps version? 

it will be necessary to define from what table variable zone is selected

[dmarsh]

Version 1.6.0.5 thanks. I found a rather inelegant solution by editing the AdminCountriesController.php and removed the order by zone command. There is probably a better solution or should be a patch so it doesn't error out when changing the sort order.