How-to connect to secondary external DB in EE2

Question

Im relatively experienced with PHP/HTML, however not so much with EE 2.4. Im working with a company using this set-up, so im on the learning curve at the moment. However, the first db 'expression' has been set up via phpmyadmin on cpanel, a second database to hold a few tables for front-end db search is required and im having trouble figuring out how to connect to this database, and to display data, i.e. select * from tbl_states

Am i to create a template script/ file for connection, or a webpage (php enabled) file? or will the php method work in ee 2.4?

<?php
include("include/dbinfo");
$comm=@mysql_connect(localhost,$username,$password);
$rs=@mysql_select_db($database) or die( "Unable to select database"); 

$sql="SELECT * FROM tbl_states";
$result = mysql_query($sql) or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
?>

$DB1 = $this->EE->load->database('ext_db', TRUE);

$q = $DB1->quesry('SELECT * FROM tbl_states'); $results = $q->result_array();

trying to use the below code to echo to no avail

if ($results ->num_rows() > 0){
  foreach ($results ->result() as $row){
    echo $row->stateName."";
  } 
}

Help towards this is highly appreciated !!

Answer 1

If this is only going to be used in a single template, you could set that template to allow PHP and then put your connection info directly in the template.

But if it's going to be used in multiple templates, you might need to create a module and put everything in there.

There's also http://360adaptive.com/connecting-to-multiple-databases-in-expressionengine/ which describes connecting to multiple dbs in EE2.