如何在CodeIgniter中创建"VIEW(SQL)"并从中选择数据? [英] 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.

我要做的是从另一个数据库创建一个视图，然后将该视图加入到我的查询中.问题是如何在CodeIgniter中创建视图并从中选择数据?

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?

谢谢.

顺便说一句，我不是设计数据库的人. -公司定义-

这里有一个我的查询示例，因为它包含很多表，所以它不是一个准确的示例.希望我能告诉您我要做什么.

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.

推荐答案

我们都知道我们无法联接来自不同数据库的表

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:

一次查询多个数据库
PHP Mysql跨数据库联接
https://stackoverflow.com/a/5698396/183254

Querying multiple databases at once
PHP Mysql joins across databases
https://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');

这篇关于如何在CodeIgniter中创建"VIEW(SQL)"并从中选择数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！