Joomla Mysql Query Issue

Question

The two joomla tables :

uh46v_fb_sw_user_items  uh46v_comprofiler
id  userid              id  user_id cb_country  cb_intl_shipping
5   833                 833 833     de          ch|*|fr|*|de
6   833                 834 834     fr          ch|*|fr
11  835                 835 835     ch          ch|*|fr|*|de
10  834                 836 836     fr

The Joomla query :

$userIntlShip = 'ch,de,fr';
$userCountry = 'de';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
->select($db->quoteName(array('i.id')))
->from($db->quoteName('uh46v_fb_sw_user_items', 'i'))
->join('INNER', $db->quoteName('uh46v_comprofiler', 'c') . ' ON (' . $db->quoteName('c.id') . ' = ' . $db->quoteName('i.userid') . ')')
->where($db->quoteName('c.cb_intl_shipping') . ' LIKE "%'.$userCountry.'%" AND ' . $db->quoteName('c.cb_country') . ' IN ('.$userIntlShip.')');
$db->setQuery($query);
$rows = $db->loadObjectList(); 
$list = array();
foreach ($rows as $row) 
{
$list[] = $row->id;
}
$authoritem = implode(',', $list); 
echo $authoritem;

Causes the following error :

1054 Unknown column 'ch' in 'where clause' SQL=SELECT `i`.`id` FROM `uh46v_fb_sw_user_items` AS `i` INNER JOIN `uh46v_comprofiler` AS `c` ON (`c`.`id` = `i`.`userid`) WHERE `c`.`cb_intl_shipping` LIKE "%de%" AND `c`.`cb_country` IN (ch,fr,de)

You need to put quotes around the separate values of userIntlShip to avoid them being misinterpreted as columns. — Carsten, Jul 25 '15 at 01:24
I have done this : IN ("'.$userIntlShip.'") No more error but the query should find some results but it doesn't. — waby, Jul 25 '15 at 01:39
Ideally do a select on both tables and put the output here. Maybe limit the actual data rows to 5, to keep it simple. — Carsten, Jul 25 '15 at 01:51
I have covered this topic very thoroughly: https://joomla.stackexchange.com/a/22898/12352 Please post all of your Joomla questions on [joomla.se] Stack Exchange. — mickmackusa, Jul 23 '20 at 15:02

Elin · Answer 1 · 2015-07-25T07:30:16.270

First this

LIKE "%'.$userCountry.'%" AND '

should be

LIKE . $db->quote( '%'.$userCountry.'%') AND '

And then I would do the following:

$countryCodes = explode(",", $userIntlShip);
$countryCodes = implode('","', $countryCodes);

' IN ("'. $countryCodes .'")'

or even better just start with $userIntlShip = array('ch','de','fr');

Because then you can pull the list from the database and skip the exploding.

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

I can only assume the double-quotes around your LIKE "%de%" are causing this issue.

Have a look at this and try using single quotes instead.

edited May 23 '17 at 11:51

Community

1
1

answered Jul 25 '15 at 03:10

Carsten

2,047
1
21
46

Some db's use double quotes for wrapping string values. Firebird is one (off the top of my head), I'm sure there are others. The real problem is that the comma-delimited string is being treated as comma-delimited columns -- this is clearly not right. I have covered this topic fully on JSE, see my comment under the question. – mickmackusa Jul 23 '20 at 15:04

score 0 · Answer 3 · answered Jul 25 '15 at 06:45

0

You should make it like

$userIntlShip = "'ch','de','fr'";

Because here your query work like one string. as where country like ('ch,de,fr');

Correct query is like Where country like ('ch','de','fr');

answered Jul 25 '15 at 06:45

Ronak Chauhan

143
1
8

Joomla Mysql Query Issue

3 Answers3