How to count numbers of row of sql query in joomla and display it?

Question

My sql query is this one:

SELECT COUNT(*) AS NumberOfOrders FROM table_products where published = 1;

Joomla use Jdatabase to play with tables. I found this on internet:

$db = JFactory::getDbo(); 
 $query = $db->getQuery(true); 
 $query->select(array('name', 'email', 'username')) 
   ->from($db->quoteName('#__my_users')) 
   ->where($db->quoteName('name') . ' LIKE '. $db->quote('\'%SMITH%\'')); 
 $db->setQuery($query); 

 // get the count 
 $my_count = $db->getNumRows(); 

 // retrieve the data 
 $rows = $db->loadObjectList(); 

 echo $my_count; 

So I try to modify this example for my query:

$db = JFactory::getDbo(); 
 $query = $db->getQuery(true); 
 $query->select(array('id')) 
   ->from($db->quoteName('#__table_products')) 
   ->where($db->quoteName('published') . ' LIKE '. $db->quote('1')); 

 $db->setQuery($query); 

 // get the count 
 $my_count = $db->getNumRows(); 

 // retrieve the data 
 $rows = $db->loadObjectList(); 

But it doesn't work. I get empty result. What is wrong?

Someone can help me please?

Regards

Answer 1

$db->setQuery($query);
$my_count = $db->query();
$my_count = $db->getNumRows();

Answer 2

Try this way to COUNT number of rows, but i am not sure about

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from($db->quoteName('#__table_products'));
$query->where($db->quoteName('published') . ' LIKE '. $db->quote('\'%1%\''));

$db->setQuery($query);
$count = $db->loadResult();

SEE MORE:https://docs.joomla.org/Selecting_data_using_JDatabase