How to CREATE a 'VIEW(SQL)' in CodeIgniter and SELECT data from it?

I have a query that needs to check whether the record is still active before it includes it in the query. Now my problem is that the record status of that record is in another database and WE all know that we cant join tables from different databases.

What I want to do is to create a view from the other database and I'll just join that view to my query. The problem is HOW can I create a view and select data from it in CodeIgniter?

Thanks in advance.

By the way, Im not the one who designed the database. -Company defined-

Heres a sample of my query, its not the exact one since it includes a lot of tables. I hope I could give you a hint of what I'm trying to do.

SELECT count(IDNO), course, sum(student_balance)
FROM student_balances
WHERE school_term = '2013' AND student_balance > 0
GROUP BY course
ORDER BY course

All student records there are selected regardless if its enrolled or not. there is a table containing enrolled students of the current school year which that table is from another database. I want to count only the records of students that are enrolled.

Answers


WE all know that we can't join tables from different databases

Not sure if applicable in your case, but here are a few posts about querying across db's:

Querying multiple databases at once PHP Mysql joins across databases http://stackoverflow.com/a/5698396/183254

At any rate, you don't need to use a join; just query the other db to see if the thing is active

$DB2 = $this->load->database('otherdb', TRUE);
$active = $DB2->query('SELECT is_active blah...
if($active)
{
    //do other query
}

Update

This probably isn't syntactically correct, but should point you in the right direction. As always, user guide.

// load other db
$db2 = $this->load->db('otherdb',TRUE);

// get enrolled student id's from other db
$active_students = $db2->query('SELECT id FROM students WHERE enrolled = 1')->result();

// query this db for what you want
$this->db->select('count(IDNO), course, sum(student_balance)');
$this->db->where('school_term',2013);
$this->db->where('student_balance >',0);

// where_in will limit the query to the id's in $active_students
$this->db->where_in('id', $active_students);

// finally, execute the query on the student_balances table
$balances = $this->db->get('student_balances');

Need Your Help

Make a picture cover the whole cell in foundation zurb

html css zurb-foundation-5

I am trying to make some picture fully cover the cells in a foundation zurb. I keep getting some margins in the cells. Is there a way to avoid this? The pictures are a little bit less in width than...

Different probability for ranges of random numbers

javascript random distribution probability

I'm looking for the best way of implementing random number generator, that will allow me to have control over probability from what range the generated number will be returned. To visualize what I'm

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.