+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How to write this SQL statement in Active Record (/showthread.php?tid=31225)
How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]Isamtron[/eluser]
Hello,
How to write this SQL statement in Active Record:
Code:
$this->db->query('SELECT a.slogan, a.brief, u.id, u.name FROM example_tbl AS a, users AS u WHERE u.user_id = "' . USER_ID . '"');Please help. Thanks.
How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]WanWizard[/eluser]
I'm not sure you want to. This query contains two tables, but no join criteria. So the result will be a match of every record of a with every record of u. Is that intentional?
How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]Isamtron[/eluser]
Hi,
My code returns only one row because of the WHERE clause I have.
How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]WanWizard[/eluser]
Read the query, and then read it again. There is no join in this query. If you don't see the error, may I suggest to read up on designing queries?
Code:
mysql> use test;
Database changed
mysql> select * from users;
+---------+----+--------+
| user_id | id | name |
+---------+----+--------+
| 1 | 1 | user 1 |
| 2 | 2 | user 2 |
+---------+----+--------+
2 rows in set (0.00 sec)
mysql> select * from example_tbl;
+---------+--------+
| slogan | brief |
+---------+--------+
| slogan1 | brief1 |
| slogan2 | brief2 |
+---------+--------+
2 rows in set (0.00 sec)
mysql> SELECT a.slogan, a.brief, u.id, u.name FROM example_tbl AS a, users AS u WHERE u.user_id =1;
+---------+--------+----+--------+
| slogan | brief | id | name |
+---------+--------+----+--------+
| slogan1 | brief1 | 1 | user 1 |
| slogan2 | brief2 | 1 | user 1 |
+---------+--------+----+--------+
2 rows in set (0.00 sec)How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]Isamtron[/eluser]
Hi,
users table primary key "id" not "user_id". example_tbl has a field called user_id as well.
How to write this SQL statement in Active Record - El Forum - 06-10-2010
[eluser]WanWizard[/eluser]
All fine, but your query has 'user_id' in the WHERE clause. And your query refers to a user_id field in the users table, and an id field in the example_tbl table. So what is it?
It's very difficult answering questions if the question isn't correct, don't you think?
In the assumption that there are no more suprises, and the field is called user_id, the query should be
Code:
$this->db->query('SELECT a.slogan, a.brief, u.user_id, u.name FROM example_tbl AS a JOIN users AS u USING (user_id) WHERE u.user_id = "' . USER_ID . '"');Code:
$this->db->select('a.slogan, a.brief, u.user_id, u.name');
$this->db->from('example_tbl a');
$this->db->join('users u', 'a.user_id = u.user_id'); // standard AR doesn't support USING
$this->db->where('u.user_id="'.USER_ID.'"');
$query = $this->db->get();How to write this SQL statement in Active Record - El Forum - 06-14-2010
[eluser]Isamtron[/eluser]
Quote:It's very difficult answering questions if the question isn't correct, don't you think?Yet, you were able to help me
Your last solution worked. Thank you very much.
How to write this SQL statement in Active Record - El Forum - 06-14-2010
[eluser]glopv2[/eluser]
As an added bonus, if you know you're only hunting for 1 result you could use "LIMIT 1" to let mysql stop searching once it finds the 1st match.
How to write this SQL statement in Active Record - El Forum - 06-14-2010
[eluser]Isamtron[/eluser]
Thanks for the tip.