[eluser]Unknown[/eluser]
Hello there!
I'm having a very weird problem with a quite simple select statement. Here it is:
I'm reading a csv file (that is an export of an Excel file). Field separator is ";" and each field is enclosed with "".
I use the second, third and fifth fields to create a SELECT to look for that information in the DB. In other words, I'm looking for people in a table according to their last and first names and, email. For example:
Code:
SELECT MAX(n_id_persona) AS n_id_persona
FROM (
SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
FROM PERSONAS p
WHERE (NO_ACCENTS(p.d_apellidos) LIKE NO_ACCENTS('%Lastname%')
AND NO_ACCENTS(p.d_nombres) LIKE NO_ACCENTS('%Firstname%'))
UNION
SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
FROM PERSONAS p
LEFT JOIN CORREOS c ON (p.n_id_persona = c.n_id_persona)
WHERE ((c.c_correo LIKE '%[email protected]%')) );
The issue is that, randomly, CodeIgniter does not bring any N_ID_PERSONA but if I test the same query in Oracle SQL Developer it retrieves and ID.
I first thought it was a timeout, but is discarded. Also, reformulated the queries because I thought they were not correct but nop. The function NO_ACCENTS() just replaces all accented characters by the corresponding letter and writes everything in uppercase. I also tried without using this NO_ACCENTS() function and got the same weird result.
Here it is the code:
controllers/importador.php
Code:
public function __construct()
{
parent::__construct();
$this->output->enable_profiler(TRUE);
session_start();
}
public function index()
{
//Connection info to the DB
$_SESSION['c'] = $connection_info;
$this->db = $this->load->database($_SESSION['c'], TRUE, TRUE);
$this->load->model('Importador_model');
//Reads the csv
$this->load->library('csvreader');
$filePath = '/home/vcl/Desktop/CRM/File_name.csv';
$data['csvData'] = $this->csvreader->parse_file($filePath);
foreach($data['csvData'] as $linea){
$data['coincidencia'] = $this->Importador_model->Buscar_contacto(str_replace('\'', '\'\'', $linea['Apellido']), str_replace('\'', '\'\'', $linea['Nombre']), $linea['Email']);
echo $linea['Apellido'].' '.$linea['Nombre'].''.$linea['Entrevista'].'<br>';
$temp = $data['coincidencia']->row();
echo $temp->N_ID_PERSONA;
echo '<br>';
}
}
models/importador_model.php
Code:
function Buscar_contacto($ape = NULL, $nom = NULL, $email = NULL)
{
$CI =& get_instance();
$query = $CI->db->query('SELECT MAX(n_id_persona) AS n_id_persona
FROM (
SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
FROM PERSONAS p
WHERE (NO_ACCENTS(p.d_apellidos) LIKE NO_ACCENTS(\'%'.$ape.'%\')
AND NO_ACCENTS(p.d_nombres) LIKE NO_ACCENTS(\'%'.$nom.'%\'))
UNION
SELECT DISTINCT MAX(p.n_id_persona) AS n_id_persona
FROM PERSONAS p
LEFT JOIN CORREOS c ON (p.n_id_persona = c.n_id_persona)
WHERE ((c.c_correo LIKE \'%'.$email.'%\')) )');
return $query;
}
Any ideas? Does this happen to anyone? Is this a bug?
Thanks a lot!
V