Welcome Guest, Not a member yet? Register   Sign In
Problem with join
#1

[eluser]Sein Kraft[/eluser]
This my code but it down't work properly because the user.id should be 24 and is returning the rank.id as user.id.

Code:
$query_users = $this->db
                            ->from('users')
                            ->join('roles', 'roles.id = users.role')
                            ->join('ranks', 'ranks.id = users.rank')
                            ->limit($limit, $offset)
                            //->order_by('u_date', 'desc')
                            ->get();
Quote:Array
(
[0] => Array
(
[id] => 1
[user] => seinkraft
[pass] => 55487196c2206c9345816936490688514f448edf
[email] => [email protected]
[activation_key] =>
[recovery_key] =>
[role] => Administrator
[rank] => Rank 1
[ip] => 127.0.0.1
[registered] => 1259933944
[last_visit] => 1259934493
[avatar] =>
[gender] => 0
[birthdate] => 0000-00-00
[location] =>
[signature] =>
[url] =>
[icq] =>
[aim] =>
[yim] =>
[msn] =>
)

)

Help please!
#2

[eluser]Jondolar[/eluser]
What do your tables look like? Do you have multiple tables with field names of "id"?
#3

[eluser]John_Betong[/eluser]
 
Try:
Code:
echo $this->db->last_query();
 
 
 
#4

[eluser]Sein Kraft[/eluser]
LastQuery:
Code:
FROM (`users`) JOIN `roles` ON `roles`.`id` = `users`.`role` JOIN `ranks` ON `ranks`.`id` = `users`.`rank` LIMIT 1

Tables:
Code:
CREATE TABLE IF NOT EXISTS ranks (
  id int(11) NOT NULL AUTO_INCREMENT,
  rank tinytext NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS roles (
  id int(11) NOT NULL AUTO_INCREMENT,
  role varchar(32) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS users (
  id int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(25) NOT NULL,
  pass varchar(64) NOT NULL,
  email tinytext NOT NULL,
  activation_key varchar(32) NOT NULL,
  recovery_key varchar(32) NOT NULL,
  role tinyint(4) NOT NULL,
  rank tinyint(11) NOT NULL DEFAULT '1',
  ip tinytext NOT NULL,
  registered int(10) NOT NULL,
  last_visit int(10) NOT NULL,
  avatar tinytext NOT NULL,
  gender tinyint(4) NOT NULL,
  birthdate date NOT NULL,
  location tinytext NOT NULL,
  signature mediumtext NOT NULL,
  url tinytext NOT NULL,
  icq tinytext NOT NULL,
  aim tinytext NOT NULL,
  yim tinytext NOT NULL,
  msn tinytext NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
#5

[eluser]John_Betong[/eluser]
 
Try changing your join to something like this :
Code:
->join('roles as id_roles', 'id_roles.id = users.role')
->join('ranks as id_ranks', 'id_ranks.id = users.rank')
 
 
edit: forgot to change the table name
#6

[eluser]Sein Kraft[/eluser]
error:
Quote:Unknown column 'roles.id' in 'on clause'

SELECT * FROM (`users`) JOIN `roles` as id_roles ON `roles`.`id` = `users`.`role` JOIN `ranks` as id_ranks ON `ranks`.`id` = `users`.`rank` LIMIT 1

The query was replacing users.id with roles.id and for the last with rank.id.

Finally i've change the name of the column of roles.id to roles.role_id in the sql table. But its an ugly solution.
#7

[eluser]überfuzz[/eluser]
[quote author="Sein Kraft" date="1260045300"]error:
Quote:Unknown column 'roles.id' in 'on clause'

SELECT * FROM (`users`) JOIN `roles` as id_roles ON `roles`.`id` = `users`.`role` JOIN `ranks` as id_ranks ON `ranks`.`id` = `users`.`rank` LIMIT 1
[/quote]

Well John Betong assumed that you actually had a table called roles with a column called id. Could you show us your tables? Not type how to go about creating them. Undecided
#8

[eluser]Unknown[/eluser]
If you want to join multiple tables with the same column name, you must change alias column in select statement.

Example:

$this->db->select('users.*, roles.id as r_id');

You will receive the result with id of users table and r_id of roles table

If you don't use alias for the same column in select statement, the result with return only id of roles table
#9

[eluser]John_Betong[/eluser]
 
Using PhpMyAdmin I created your tables and also this query. The query runs without any errors.
 
You did not copy the code that I sent correctly, the roles and ranks table name aliasii/aliases table names were incorrect.
 
Code:
SELECT * FROM users

JOIN roles as id_roles ON id_roles.id = users.role

JOIN ranks as id_ranks ON id_ranks.id = users.rank LIMIT 1


// PhpMyAdmin result on my empty tables
// MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0010 sec )
 
 
 
edit: spelling




Theme © iAndrew 2016 - Forum software by © MyBB