I have a question for you and I have not found a solution to this, So I have 2 tables:
dosar
id name date fk_user fk_verificator
1 dosar 1 08/08/14 1 2
users
id name is_admin is_verificator
1 admin Y N
2 verificator N Y
fk_user is foreign key pointed to users, fk_verificator is also a foreign key pointed to users table.So I need to create an select to get 2 users for dosar
name date name is_admin is_verificator
dosar1 08/08/14 admin Y N
verificator N Y
My query:
$uid = (int) $this->uri->segment(3, 0);
$this->load->database();
$get_dosar = $this->db->query("SELECT * FROM dosar,users WHERE
users.id = dosar.fk_user AND
users.id = dosar.fk_verificator
AND dosar.id_dosar = $uid");
Help me please guys.
This uses modern join syntax (it is a good practice not to put join conditions in the WHERE clause):
Fiddle: http://sqlfiddle.com/#!9/d6e60/2/0
select d.name, d.date, u.is_admin, u.is_verificator
from dosar d
join users u
on u.id = d.fk_user
or u.id = d.fk_verificator
where d.id = $uid
Output (for ID #1 on dosar):
| NAME | DATE | IS_ADMIN | IS_VERIFICATOR |
|---------|-------------------------------|----------|----------------|
| dosar 1 | August, 08 2014 00:00:00+0000 | Y | N |
| dosar 1 | August, 08 2014 00:00:00+0000 | N | Y |
You seem to want to not repeat values of NAME or DATE where it is the same as the previous row. That should be done in PHP, not the SQL.
Because you're always just selecting one ID it's somewhat simple to do it in MySQL as well (see below) however I would still recommend doing that part in PHP.
select case when rn = 1 then name end as name,
case when rn = 1 then date end as date,
is_admin,
is_verificator
from(select d.name, d.date, u.is_admin, u.is_verificator, @rw := @rw + 1 as rn
from dosar d
join users u
on u.id = d.fk_user
or u.id = d.fk_verificator
cross join (select @rw := 0) r
where d.id = 1) x
Output:
| NAME | DATE | IS_ADMIN | IS_VERIFICATOR |
|---------|-------------------------------|----------|----------------|
| dosar 1 | August, 08 2014 00:00:00+0000 | Y | N |
| (null) | (null) | N | Y |