Joomla! Issue Tracker | Joomla! CMS #22010 - When using the union in the query raise Warning: mysqli::stat(): Couldn't fetch mysqli

J3 Issue ?

Closed
18 Mar 2019
Medium
Build: staging
# 22010

wolf002
4 Sep 2018

class JuicesModelShipments extends JModelList{
	protected function getListQuery()
	{
		// Filter by search in title
		$search = $this->getState('filter.search');
		$date_delivery = $this->getState('list.date_delivery');
		// Create a new query object.
		$db    = $this->getDbo();
		$query = $db->getQuery(true);

		// Select the required fields from the table.
        $q2 = $db->getQuery(true);      
		$q2->select("a.order_id, a.type, a.product_id, a.quantity, a.additive, a.date_delivery, a.time_delivery, 'abonement' AS genus")
		    ->from('`#__juices_order_abonement_products` AS a')
		    ->select("o.name AS name, o.surname AS surname, o.email AS email, o.address AS address")
			->join("LEFT", "#__juices_orders AS o ON o.id=a.order_id");
		// Filter by search in title	
		if (!empty($search)){
			$word = $db->Quote('%' . $db->escape($search, true) . '%');
			$q2->where('( o.name LIKE ' . $word . '  OR  o.surname LIKE ' . $word . '  OR  o.email LIKE ' . $word . '  OR  o.address LIKE ' . $word . ' )');
		}
		if($date_delivery !== null  &&!empty($date_delivery)){
			$q2->where("a.date_delivery = '".$db->escape($date_delivery)."'");
		}	
		$query->select("p.order_id, p.type, p.product_id, p.quantity, p.additive, p.date_delivery, p.time_delivery, 'product' AS genus")
		    ->from('`hbsw0_juices_order_products` AS p')
		    ->select("o.name AS name, o.surname AS surname, o.email AS email, o.address AS address")
			->join("LEFT", "#__juices_orders AS o ON o.id=p.order_id")
		    ->unionAll($q2);
		// Filter by search in title
		if (!empty($search)){
			$word = $db->Quote('%' . $db->escape($search, true) . '%');
			$query->where('( o.name LIKE ' . $word . '  OR  o.surname LIKE ' . $word . '  OR  o.email LIKE ' . $word . '  OR  o.address LIKE ' . $word . ' )');
		}	
		if($date_delivery !== null  &&!empty($date_delivery)){
			$query->where("p.date_delivery = '".$db->escape($date_delivery)."'");
		}	
                
		// Add the list ordering clause.
		$orderCol  = $this->state->get('list.ordering', 'name');
		$orderDirn = $this->state->get('list.direction', 'ASC');

		if ($orderCol && $orderDirn)
		{
			$query->order($db->escape($orderCol . ' ' . $orderDirn));
		}

		return $query;
	}
}

Actual result

System information (as much as possible)

Warning: mysqli::stat(): Couldn't fetch mysqli in F:\OpenServwer\domains\soki.local\libraries\joomla\database\driver\mysqli.php on line 209

Warning: mysqli_get_server_info(): Couldn't fetch mysqli in F:\OpenServwer\domains\soki.local\libraries\joomla\database\driver\mysqli.php on line 503

Warning: mysqli_errno(): Couldn't fetch mysqli in F:\OpenServwer\domains\soki.local\libraries\joomla\database\driver\mysqli.php on line 977

Warning: mysqli_error(): Couldn't fetch mysqli in F:\OpenServwer\domains\soki.local\libraries\joomla\database\driver\mysqli.php on line 989

Additional comments

If I remove a string with union, then everything works without errors

	protected function getListQuery()
	{
		// Filter by search in title
		$search = $this->getState('filter.search');
		$date_delivery = $this->getState('list.date_delivery');
		// Create a new query object.
		$db    = $this->getDbo();
		$query = $db->getQuery(true);

		// Select the required fields from the table.
        $q2 = $db->getQuery(true);      
		$q2->select("a.order_id, a.type, a.product_id, a.quantity, a.additive, a.date_delivery, a.time_delivery, 'abonement' AS genus")
		    ->from('`#__juices_order_abonement_products` AS a')
		    ->select("o.name AS name, o.surname AS surname, o.email AS email, o.address AS address")
			->join("LEFT", "#__juices_orders AS o ON o.id=a.order_id");
		// Filter by search in title	
		if (!empty($search)){
			$word = $db->Quote('%' . $db->escape($search, true) . '%');
			$q2->where('( o.name LIKE ' . $word . '  OR  o.surname LIKE ' . $word . '  OR  o.email LIKE ' . $word . '  OR  o.address LIKE ' . $word . ' )');
		}
		if($date_delivery !== null  &&!empty($date_delivery)){
			$q2->where("a.date_delivery = '".$db->escape($date_delivery)."'");
		}	
		$query->select("p.order_id, p.type, p.product_id, p.quantity, p.additive, p.date_delivery, p.time_delivery, 'product' AS genus")
		    ->from('`hbsw0_juices_order_products` AS p')
		    ->select("o.name AS name, o.surname AS surname, o.email AS email, o.address AS address")
			->join("LEFT", "#__juices_orders AS o ON o.id=p.order_id");
		    //->unionAll($q2);
		// Filter by search in title
		if (!empty($search)){
			$word = $db->Quote('%' . $db->escape($search, true) . '%');
			$query->where('( o.name LIKE ' . $word . '  OR  o.surname LIKE ' . $word . '  OR  o.email LIKE ' . $word . '  OR  o.address LIKE ' . $word . ' )');
		}	
		if($date_delivery !== null  &&!empty($date_delivery)){
			$query->where("p.date_delivery = '".$db->escape($date_delivery)."'");
		}	
                
		// Add the list ordering clause.
		$orderCol  = $this->state->get('list.ordering', 'name');
		$orderDirn = $this->state->get('list.direction', 'ASC');

		if ($orderCol && $orderDirn)
		{
			$query->order($db->escape($orderCol . ' ' . $orderDirn));
		}

		return $query;
	}

wolf002 - open - 4 Sep 2018

joomla-cms-bot - change - 4 Sep 2018

Labels

Added: ?

joomla-cms-bot - labeled - 4 Sep 2018

wolf002 - change - 4 Sep 2018

The description was changed

wolf002 - edited - 4 Sep 2018

wolf002 - change - 4 Sep 2018

The description was changed

wolf002 - edited - 4 Sep 2018

franz-wohlkoenig - change - 5 Sep 2018

Category

⇒

SQL

alikon - comment - 9 Sep 2018

can you run/post the $query->dump() in phpmyadmin or in mysql console ?

franz-wohlkoenig - change - 9 Sep 2018

Status

New

⇒

Information Required

zuluhotel87 - comment - 12 Sep 2018

Same issue here. If using union, I get the following warning using Joomla! 3.8.8 :

Warning: mysqli::stat(): Couldn't fetch mysqli in C:\xampp\htdocs\joomla\libraries\joomla\database\driver\mysqli.php on line 209

Warning: mysqli_close(): Couldn't fetch mysqli in C:\xampp\htdocs\joomla\libraries\joomla\database\driver\mysqli.php on line 216

Here is my simplified $query->dump():

SELECT DISTINCT`a`.`COD_STAT_IMMAT` AS `statut`,`b`.`NOM_ASSUJ` AS `name`,`c`.`LIGN1_ADR`
FROM `bflhc_newbiz_entreprise` AS `a`
LEFT JOIN `bflhc_newbiz_nom` AS `b` ON a.NEQ = b.NEQ
LEFT JOIN `bflhc_newbiz_etablissement` AS `c` ON (a.NEQ = c.NEQ)
WHERE `a`.`NEQ` IN (1142971564,1163175681)

UNION (
SELECT DISTINCT`a`.`COD_STAT_IMMAT` AS `statut`,`b`.`NOM_ASSUJ_LANG_ETRNG` AS `name`,`c`.`LIGN1_ADR`
FROM `bflhc_newbiz_entreprise` AS `a`
LEFT JOIN `bflhc_newbiz_nom` AS `b` ON a.NEQ = b.NEQ
LEFT JOIN `bflhc_newbiz_etablissement` AS `c` ON (a.NEQ = c.NEQ)
WHERE `a`.`NEQ` IN (1142971564,1163175681
)

alikon - comment - 12 Sep 2018

it should be

SELECT DISTINCT`a`.`COD_STAT_IMMAT` AS `statut`,`b`.`NOM_ASSUJ` AS `name`,`c`.`LIGN1_ADR`
FROM `bflhc_newbiz_entreprise` AS `a`
LEFT JOIN `bflhc_newbiz_nom` AS `b` ON a.NEQ = b.NEQ
LEFT JOIN `bflhc_newbiz_etablissement` AS `c` ON (a.NEQ = c.NEQ)
WHERE `a`.`NEQ` IN (1142971564,1163175681)

UNION
SELECT DISTINCT`a`.`COD_STAT_IMMAT` AS `statut`,`b`.`NOM_ASSUJ_LANG_ETRNG` AS `name`,`c`.`LIGN1_ADR`
FROM `bflhc_newbiz_entreprise` AS `a`
LEFT JOIN `bflhc_newbiz_nom` AS `b` ON a.NEQ = b.NEQ
LEFT JOIN `bflhc_newbiz_etablissement` AS `c` ON (a.NEQ = c.NEQ)
WHERE `a`.`NEQ` IN (1142971564,1163175681)

you have a '(' after UNION that is wrong

zuluhotel87 - comment - 12 Sep 2018

I'll test it out when I get back home and reply

zuluhotel87 - comment - 13 Sep 2018

Ok, additionnal info. I tried both with and without parentheses and the results are same as demonstrated above. Simplified query:

SELECT NEQ
FROM `bflhc_newbiz_nom`
WHERE 
(MATCH(`NOM_ASSUJ`) AGAINST('+google' IN BOOLEAN MODE)) OR 
(`NOM_ASSUJ`='google')
UNION  (
SELECT NEQ
FROM `bflhc_newbiz_nom`
WHERE 
(MATCH(`NOM_ASSUJ_LANG_ETRNG`) AGAINST('+google' IN BOOLEAN MODE)) OR 
(`NOM_ASSUJ_LANG_ETRNG`='google')
)

I suspect the getListQuery() from JModelList being unusable with unions as I don't get mysqli error from outside getListQuery()

brianteeman - change - 30 Oct 2018

Labels

Added: J3 Issue

brianteeman - labeled - 30 Oct 2018

franz-wohlkoenig - comment - 5 Mar 2019

@alikon can you please comment?

franz-wohlkoenig - comment - 16 Mar 2019

@HLeithner can you please comment?

HLeithner - comment - 18 Mar 2019

I tried to rebuild your issue but can't I tested it with joomla sample data and added a fulltext index to the title.

Query I used:

SELECT 
  id 
FROM 
  vfk73_content 
WHERE 
  (
    MATCH(`title`) AGAINST("+your" IN BOOLEAN MODE)
  ) 
  OR (`title` = "your") 
UNION 
  (
    SELECT 
      id 
    FROM 
      vfk73_content 
    WHERE 
      (
        MATCH(`title`) AGAINST("+about" IN BOOLEAN MODE)
      ) 
      OR (`title` = "about")

PHP Code I used:

$db     = JFactory::getDbo();
$query1 = $db->getQuery(true);
$query2 = $db->getQuery(true);

$query1->select('id')->from('#__content')->where('(MATCH(`title`) AGAINST("+your" IN BOOLEAN MODE)) OR (`title`="your")');
$query2->select('id')->from('#__content')->where('(MATCH(`title`) AGAINST("+about" IN BOOLEAN MODE)) OR (`title`="about")');

$query1->union($query2);
$db->setQuery($query1);

$list = $db->loadObjectList();

this outputs all 6 rows fromt 2-5 and 1 at last.

@alikon the () for the union is correct thats the way joomla build union queries

Which mysql version do you use? I use 5.6, we need the SQL error message, the php message only tells us that the query wasn't successful.

@zuluhotel87 I think this is not a joomla issue, in my opinion this is an error in your SQL query, I close this issue, you can always reopen it.

HLeithner - change - 18 Mar 2019

Status	Information Required	⇒	Closed
Closed_Date	0000-00-00 00:00:00	⇒	2019-03-18 12:05:38
Closed_By		⇒	HLeithner

HLeithner - close - 18 Mar 2019

Add a Comment

Older
Newer

Joomla! Issue Tracker - CMS

[#22010] - When using the union in the query raise Warning: mysqli::stat(): Couldn't fetch mysqli

Actual result

System information (as much as possible)

Additional comments

Add a Comment