How to connect joomla3 DB from external php file?

Question

I have created external php file inside my module and there i have use some sql queries , first i have tried using php/mysql and it works , then i tried to make it convert to joomla style . but when i use joomla framework to db connection gives errors

Old code: FROM PHP

mysql_connect("localhost","root","");
mysql_select_db("1234");

 $searchp=$_GET["term"];
 $query=mysql_query("SELECT * FROM sltdb_cddir_content where title like '%".$searchp."%'AND categories_id=82 order by title ASC ");
 $json=array();
    while($display=mysql_fetch_array($query)){
         $json[]=array(
                    'value'=> $display["title"],
                    'label'=>$display["title"]
                        );
    }

 echo json_encode($json);

New Code : JOOMLA3

    define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

define('JPATH_BASE', $_SERVER['DOCUMENT_ROOT'] . DS . '');

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

//create application
$mainframe = &JFactory::getApplication('site');
$db = JFactory::getDBO();

// Create a new query object.
$query = $db -> getQuery(true);
$searchp = $_GET["term"];
$query -> select($db -> quoteName(array('title')));
$query -> from($db -> quoteName('sltdb_cddir_content'));
$query -> where($db -> quoteName('title') . ' LIKE ' . $db -> quote('\'$searchp.%\''));
$query -> order('ordering ASC');

$db -> setQuery($query);
$json = array();
while ($display = mysql_fetch_array($query)) {
    $json[] = array('value' => $display["title"], 'label' => $display["title"]);
}

echo json_encode($json);

once after converting to the code in joomla its given a error

*"mysql_fetch_array() expects parameter 1 to be resource, object given in "*

Please advice me where i have done incorrect .

EDIT 01

    define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

define('JPATH_BASE', $_SERVER['DOCUMENT_ROOT'] . DS . '');

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

//create application
$mainframe = &JFactory::getApplication('site');
$searchp = $_GET["term"];
$db = JFactory::getDBO();

// Create a new query object.
$query = $db -> getQuery(true);

$query -> select($db -> quoteName(array('title')));
$query -> from($db -> quoteName('sltdb_cddir_content'));
$query -> where($db -> quoteName('title') . ' LIKE ' . $db -> quote('\'$searchp.%\''));
$query->where($db->quoteName('categories_id')." = ".$db->quote(82));
$query -> order('ordering ASC');

$db->setQuery($query);

$results = $db-> loadAssocList();
$json = array();
foreach($results as $json_result) {
  $json[] = array('value' => $json_result["title"], 'label' => $json_result["title"]) ;
}

echo json_encode($json);

I'm assuming you just skipped the code where you use the configuration to connect to the database? ALso do not escape the $search% the class already does that for you. — Elin
– Elin, Commented Mar 11, 2014 at 23:20

Bladimir · Accepted Answer · 2014-03-11 17:56:02Z

1

You are mixing using Joomla's "SDK" with pure PHP methods for data access, so I would recommend stay away from that mix:

1) mysql_fetch_array its going to be depreacated, and indeed it expects the 1st parameter to be a mysql connection created with a mysql_connect call, that's why the error says that the 1st parameter expects to be a resource, you can check the documentation in here http://www.php.net/mysql_fetch_array.

2) Since you are using Data Access classes from joomla to obtain the connection and build the query, which is fine, I would recommend keep using it for obtaining the results and looping through them, like this.

// Reset the query using our newly populated query object.
$db->setQuery($query);

// Load the results as a list of associated arrays.
$results = $db-> loadAssocList();
$json = array();
foreach($results as $json_result) {
  $json[] = array('value' => $json_result["title"], 'label' => $json_result["title"]) 
}

More about Joomla's DB access here: http://docs.joomla.org/Selecting_data_using_JDatabase

answered Mar 11, 2014 at 17:56

Bladimir

362 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3406921 Over a year ago

thanks for the idea.. bythe way how can put WHERE clauses for the joomla mysql query "where title like '%".$searchp."%'AND categories_id=82 " ?

Lodder Over a year ago

@user3406921 - have a look at the documentation link you have been provided. Your answer will be there

user3406921 Over a year ago

@Lodder : yes , i go through the porvided document and managed to refactor the code , but still its not working . i have update my question with new code under "EDIT 01" . please advice me?

Bladimir Over a year ago

When you want to add more than one where condition you should sent it as an array of conditions and the glue which could be "AND" or "OR", so the where part will end up like this: $query->where(["{$db->quoteName('title')} LIKE {$db->quote('$searchp%')}","{$db->quoteName('categories_id')} = {$db->quote(82)}"], 'AND'); Its basically an array of strings with each where condition with "AND" glue so the query will end up like WHERE title LIKE '$searchp%' AND categories_id = 82 See more about the API here api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_where

Lodder · Accepted Answer · 2014-03-11 19:28:33Z

1

Made a few tweaks to your database query and replaced $_GET with the correct Joomla method

define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);
define('JPATH_BASE', $_SERVER['DOCUMENT_ROOT'] . DS . '');
require_once (JPATH_BASE . DS . 'includes' . DS . 'defines.php');
require_once (JPATH_BASE . DS . 'includes' . DS . 'framework.php');
require_once (JPATH_BASE . DS . 'libraries' . DS . 'joomla' . DS . 'factory.php');

$app    = JFactory::getApplication();
$jinput = $app->input;
$db     = JFactory::getDbo();

$searchp = $jinput->get('term', null, null);
$id = 82;

$query = $db->getQuery(true);
$query->select($db->quoteName('title'))
      ->from($db->quoteName('sltdb_cddir_content'))
      ->where($db->quoteName('title') . ' LIKE ' . $db->quote($searchp))
      ->where($db->quoteName('categories_id') . ' = ' . $db->quote((int) $id))
      ->order('ordering ASC');
$db->setQuery($query);

Let me know if it works

edited Mar 11, 2014 at 19:28

answered Mar 11, 2014 at 19:13

Lodder

19.8k11 gold badges65 silver badges104 bronze badges

6 Comments

user3406921 Over a year ago

its gives "Error displaying the error page: Application Instantiation Error"

user3406921 Over a year ago

bythe way does "$mainframe = &JFactory::getApplication('site');" also need or only "$jinput = JFactory::getApplication()->input;" is enough as per your answer ?

Lodder Over a year ago

Also please ensure you're using the most up to date version of the 3.x series, which is 3.2.3

user3406921 Over a year ago

thanks for the help but still im getting "Error displaying the error page: Application Instantiation Error: Application Instantiation Error" issue.. i already updted jooml1a 3.2.3

Lodder Over a year ago

If you do this var_dump(JPATH_BASE)l, so does it show you the root of the Joomla site? If so, then what happens if you remove ->where($db->quoteName('title') . ' LIKE ' . $db->quote($searchp)) from the code? Also, what version of PHP and MySQL are you using?

|

Elin · Accepted Answer · 2014-03-11 23:28:10Z

0

Use

echo $query->dump();

To see what the generated query looks like and you can then test the query directly.

You need to connect to the database ... is it the same or different? If it is the same you can do

// Creating the database connection. $this->db = JDatabase::getInstance( array( 'driver' => $config->dbtype, 'host' => $config->host, 'user' => $config->user, 'password' => $config->password, 'database' => $config->db, 'prefix' => $config->dbprefix, ) );

But if it is a different databas you'll need to supply the connection data in some other way.

answered Mar 11, 2014 at 23:28

Elin

6,7803 gold badges28 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to connect joomla3 DB from external php file?

3 Answers 3

4 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related