0

I am having an issue with a basic query in PHP. The query runs perfectly in PHPMyAdmin, but in the browser I am getting:

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, object given in /home/######/public_html/test.php on line 54

I've tried single quotes and double quotes but I'm getting nowhere fast. Below is my full code so far:

//display errors
error_reporting(E_ALL);
ini_set("display_errors", 1);

//init Joomla Framework 
define( '_JEXEC', 1 ); 
define( 'JPATH_BASE', dirname(__FILE__).'/' ); // print this out or observe errors to see which directory you should be in (this is two subfolders in) 
define( 'DS', DIRECTORY_SEPARATOR ); 
//echo JPATH_BASE;

require_once ( JPATH_BASE .DS.'includes'.DS.'defines.php' ); 
require_once ( JPATH_BASE .DS.'includes'.DS.'framework.php' ); 
require_once ( JPATH_CONFIGURATION   .DS.'configuration.php' ); 
require_once ( JPATH_LIBRARIES .DS.'joomla'.DS.'database'.DS.'database.php' ); 
require_once ( JPATH_LIBRARIES .DS.'import.php' ); 

//DB Connection 
$Config = new JConfig(); 
$db_driver      = $Config->dbtype;   // Database driver name 
$db_host        = $Config->host;     // Database host name 
$db_user        = $Config->user;     // User for database authentication 
$db_pass        = $Config->password; // Password for database authentication 
$db_name        = $Config->db;       // Database name 
$db_prefix      = $Config->dbprefix; // Database prefix (may be empty) 

// Database prefix (if empty then remove prefixing double underscore) 
$db_prefix      = (trim($db_prefix)=="") ? "":$db_prefix; 
//echo $db_prefix;

$db_connect = mysqli_connect($db_host,$db_user,$db_pass); 

$content_count = 0; 

//CONNECTED! so run a SQL query as per usual 
if (!mysqli_connect_errno()) { 
    $query="SELECT COUNT(*) as ProductSku FROM ".$db_prefix."virtuemart_products";
    if ($result = mysqli_query($db_connect, $query, MYSQLI_USE_RESULT)) {  
        while($obj = $result->fetch_object()){ 
            $content_count = $obj->ProductSku; 
        } 
    }                 
}

//echo $query;  
//echo $content_count;
mysqli_free_result( $db_connect );

Update

Ok, so the code below works fine, but what I want to do is return all records where the created_on field has today's date. (rather than the simple query I quoted first, this was just to try and get the code working) Thus the query would be:

SELECT product_sku FROM #__virtuemart_products WHERE created_on <= NOW();"

Following this I want to export to a csv file, but I am getting an error:

Fatal error: Call to a member function fetch_fields() on a non-object in

Here is my full code so far:

//display errors
error_reporting(E_ALL);
ini_set("display_errors", 1);

//init Joomla Framework 
define('_JEXEC', 1); 
define('JPATH_BASE', dirname(__FILE__).'/');
define('DS', DIRECTORY_SEPARATOR); 

echo JPATH_BASE;

require_once (JPATH_BASE .DS. 'includes' .DS. 'defines.php'); 
require_once (JPATH_BASE .DS. 'includes' .DS. 'framework.php');

// Start database stuff
$db = JFactory::getDbo();

//Select records that were created today <------This works but i can't get it to //work in the joomla API.
$query = "SELECT product_sku FROM #__virtuemart_products WHERE created_on <= NOW();";

$db->setQuery($query);
$result = $db->loadObjectList();

//print_r($result);

if (!$result) die('Couldn\'t fetch records');
$headers = $result->fetch_fields();
foreach($headers as $header) {
    $head[] = $header->name;
}
$fp = fopen('/home/#####/public_html/test1.csv', 'x');
if ($fp && $result) {
    fputcsv($fp, array_values($head)); 
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}
?>

2 Answers 2

1

You're using the Joomla API, therefore you do not need to use mysqli_ commands.

Instead, use the following:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

$query->select('COUNT(*)')
      ->from($db->quoteName('#__virtuemart_products'));

$db->setQuery($query);

$result = $db->loadResult();

echo $result;

As you can see, there's no need for manual database connections, because JFactory::getDbo() takes care of this for you, by grabbing the connection details from your configuration.php file.

The database table prefix #__ which you can see in the query is Joomla's generic prefix, which will automatically be replaced with the value defined in your configuration.php

There are also some require statements that you do not need. Have a look at the full code below:

<?php

//display errors
error_reporting(E_ALL);
ini_set("display_errors", 1);

//init Joomla Framework 
define('_JEXEC', 1); 
define('JPATH_BASE', dirname(__FILE__).'/');
define('DS', DIRECTORY_SEPARATOR); 

require_once (JPATH_BASE .DS. 'includes' .DS. 'defines.php'); 
require_once (JPATH_BASE .DS. 'includes' .DS. 'framework.php');

// Start database stuff
$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select('COUNT(*)')
      ->from($db->quoteName('#__virtuemart_products'));     
$db->setQuery($query);

$result = $db->loadResult();

echo $result;
6
  • Brilliant thank you, im new to this so i'm on a big learning curve. I also need to create an array of product_skus, which i want to export to a csv file and email to admin. I have found some code that does this so i'll be looking at that next. Any ideas what is wrong with this? $query = $db->getQuery(true); $query->select($db->quoteName(array('product_sku'))); ->from($db->quoteName('#__virtuemart_products')); ->where($db->quoteName (DATE_FORMAT('created_on', '%Y-%m-%d')) = CURDATE(); $db->setQuery($query); $result = $db->loadResult();
    – Dtorr1981
    Mar 10, 2016 at 15:02
  • Let's work in baby steps here and go through your initial for the database query. The code I provided you with....what was the outcome? Did it work, did you get any errors? Let me know. Once solved, you can ask a new question ;)
    – Lodder
    Mar 10, 2016 at 16:00
  • I have just updated the code above.
    – Dtorr1981
    Mar 10, 2016 at 16:33
  • I have managed to solve the fetch_fields() issue, i had not used $result = $db->execute();.
    – Dtorr1981
    Mar 10, 2016 at 17:44
  • $db->execute(); is only required for inserting, deleting and updating queries
    – Lodder
    Mar 10, 2016 at 19:09
0

First, about your first coding attempt:

  • You are declaring the vital $db_name, but you never use it, and that is likely to be the earliest issue to solve. That value is best written as the 4th patameter of mysqli_connect().
  • $db_prefix is a deceptive variable name. It would be more accurate to call it $table_prefix because it is the leading (often randomly generated) portion of the table name string. It is a member of your $db_ group of variables, but it cannot, by itself, be used to access a mysql table which may exist in one or more databases on your server.
  • As a result of not declaring a database, if (!mysqli_connect_errno()) would be true (because you can actually connect without nominating a database) but if ($result = mysqli_query($db_connect, $query, MYSQLI_USE_RESULT)) { would evaluate false, so there would not be a result object. Because mysqli_free_result( $db_connect ); is outside of your condition block, it is called no matter what, and due to not targetting a database, you are feeding it what it can't eat.

That explanation a side, Lodder is right, you should use Joomla's query building methods to make everything simple and reliable.

In your second coding attempt:

  • You are using the getDbo() method to effortlessly make a database connection.
  • You are writing a raw sql query instead of calling the select(), from(), and where() methods. This isn't necessarily "bad" because there are no variables ergo no vulnerability. Though as a matter of consistently toeing the Joomla line, it is advisable to use the wrapper methods for all queries.
  • Your new SELECT query is only targetting the column product_sku, so the appropriate result set generating method is loadColumn()
  • When writing your values to your csv file, you don't need to reindex the arrays with array_values(), and you should fclose() before you die. See an example here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.