[solved] Nested Selected is it possible with active record?

[eluser]mjsilva[/eluser]
Hi,

I have the following code in a model:

Code:

$this->db->select('tab_clients.client_id,
                           tab_clients.name,
                           tab_countries.name AS country,
                           tab_status.description AS `status`,
                           tab_clients.nif,
                           tab_clients.email,
                           tab_clients.phone,
                           tab_clients.fax,
                           tab_clients.address,
                           tab_clients.locality,
                           tab_clients.postal_code,
                           tab_clients.mail_address,
                           tab_clients.mail_locality,
                           tab_clients.mail_postal_code,
                           tab_clients.n_client_artsoft,
                           tab_clients.comments,                          
                           (SELECT client_id FROM tab_clients WHERE client_id > '.$client_id.' ORDER BY client_id ASC LIMIT 0,1) AS nxtID,
                           (SELECT client_id FROM tab_clients WHERE client_id < '.$client_id.'  ORDER BY client_id DESC LIMIT 0,1) AS prvID
                           ');
        
        $this->db->from('tab_clients');
        $this->db->join('tab_countries', 'tab_clients.country_id = tab_countries.country_id', 'left');
        $this->db->join('tab_status', 'tab_clients.status_id = tab_status.status_id', 'left');
        $this->db->where('tab_clients.client_id', $client_id);

        $query = $this->db->get();

        if ($query->num_rows () > 0) {
            return $query->row_array();
        }

When I run It I get this error:

Ocorreu um Erro de Base de Dados

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`1` AS nxtID, `SELECT` client_id FROM tab_clients WHERE client_id < 4 ORDER BY c' at line 1

SELECT `tab_clients`.`client_id`, `tab_clients`.`name`, `tab_countries`.`name` AS country, `tab_status`.`description` AS `status`, `tab_clients`.`nif`, `tab_clients`.`email`, `tab_clients`.`phone`, `tab_clients`.`fax`, `tab_clients`.`address`, `tab_clients`.`locality`, `tab_clients`.`postal_code`, `tab_clients`.`mail_address`, `tab_clients`.`mail_locality`, `tab_clients`.`mail_postal_code`, `tab_clients`.`n_client_artsoft`, `tab_clients`.`comments`, `SELECT` client_id FROM tab_clients WHERE client_id > 4 ORDER BY client_id ASC LIMIT 0, `1` AS nxtID, `SELECT` client_id FROM tab_clients WHERE client_id < 4 ORDER BY client_id DESC LIMIT 0, `1` AS prvID FROM (`tab_clients`) LEFT JOIN `tab_countries` ON `tab_clients`.`country_id` = `tab_countries`.`country_id` LEFT JOIN `tab_status` ON `tab_clients`.`status_id` = `tab_status`.`status_id` WHERE `tab_clients`.`client_id` = '4'

The problem is generated by CI, check what's wrong in bold:

SELECT
`tab_clients`.`client_id`,
`tab_clients`.`name`,
`tab_countries`.`name` AS country,
`tab_status`.`description` AS `status`,
`tab_clients`.`nif`,
`tab_clients`.`email`,
`tab_clients`.`phone`,
`tab_clients`.`fax`,
`tab_clients`.`address`,
`tab_clients`.`locality`,
`tab_clients`.`postal_code`,
`tab_clients`.`mail_address`,
`tab_clients`.`mail_locality`,
`tab_clients`.`mail_postal_code`,
`tab_clients`.`n_client_artsoft`,
`tab_clients`.`comments`,
(SELECT client_id FROM tab_clients WHERE client_id > 4 ORDER BY client_id ASC LIMIT 0, `1)` AS nxtID,
(SELECT client_id FROM tab_clients WHERE client_id < 4 ORDER BY client_id DESC LIMIT 0, `1)` AS prvID
FROM (`tab_clients`)
LEFT JOIN `tab_countries` ON `tab_clients`.`country_id` = `tab_countries`.`country_id`
LEFT JOIN `tab_status` ON `tab_clients`.`status_id` = `tab_status`.`status_id`
WHERE `tab_clients`.`client_id` = '4'

Can I acomplish what I'm trying to do with active record or have to use $this->db->query('YOUR QUERY HERE');?

Tks

[eluser]bretticus[/eluser]
Yes, you can. I suggest you re-read Selecting Data about around.

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

:-)

[eluser]mjsilva[/eluser]
Sorry bretticus my bad.

Thanks