CODEWITHSUNDEEP
phpmysqlcodeigniter
DFX
DFX

Reputation: 13

CodeIgniter - Error Number: 1146 (Table doesn't exist after execute a "CREATE TABLE" command)

I've posted yesterday an issue with an mySQL update syntax on CodeIgniter in here: CodeIgniter - MySQL Error 1064 (Update table1 inner join table2(...))

But now after I solved that problem, another one come up. Now the update query doesn't know the new created table. But if I change to a select statement, it works smoothly.

For that reason I've decided to post the full script.

Code:

<?php



$this->load->database();

$query_tbaux='CREATE TABLE IF NOT EXISTS STUDY_LIST_AUX AS (

SELECT DISTINCT p.pat_id, p.pat_custom1 age, p.pat_name, 
p.pat_sex, s.study_iuid, p.pat_birthdate, s.accession_no, 
s.study_datetime date_s, s.study_desc, s.mods_in_study, s.pk, 
c.institution, s.study_block, s.study_urgent, 
\'0000-00-00 00:00:00\' AS \'report_date\', \'{null}\' AS \'report_status\',
s.study_tipo, 
s.study_src, 
s.study_consulta
FROM study s
INNER JOIN patient p ON s.patient_fk = p.pk
INNER JOIN series c ON c.study_fk = s.pk
INNER JOIN rel_users_hosp u ON u.hosp_id = c.institution
WHERE s.study_datetime >= \'2015-04-26 00:00:00\'
AND s.study_datetime <= \'2015-04-30 23:59:59\'
AND s.study_iuid IS NOT NULL
AND u.user_id =  \'admin\'

)';

if ($this->db->query($query_tbaux))
    {
        echo "Q True!<br><br>";
        $data = array(
               'STUDY_LIST_AUX.report_date' => "DATE_FORMAT(study_report.report_date,'%Y-%m-%d %h:%i:%s')",
               'STUDY_LIST_AUX.report_status' => 'study_report.report_status',
            );

        $this->db->update('STUDY_LIST_AUX, study_report', $data, array('STUDY_LIST_AUX.study_iuid'=>'study_report.study_iuid'));
    }
    else
        {
            echo "Q False<br><br>";
        };


?>

Display/Error:

Q True!

A Database Error Occurred

Error Number: 1146

Table 'pacsdb.STUDY_LIST_AUX,' doesn't exist

UPDATE STUDY_LIST_AUX, study_report SET STUDY_LIST_AUX.report_date = 'DATE_FORMAT(study_report.report_date,\'%Y-%m-%d %h:%i:%s\')', STUDY_LIST_AUX.report_status = 'study_report.report_status' WHERE STUDY_LIST_AUX.study_iuid = 'study_report.study_iuid'

I've checked phpmyadmin after refresh the page and the table really exists and it contains the data from the select statement.Can you please tell me what mistake I did?

Upvotes: 1

Views: 11835

Answers (3)

DFX
DFX

Reputation: 13

The solution was more simple than it looks.

$query_update = "UPDATE STUDY_LIST_AUX
        INNER JOIN study_report
        SET STUDY_LIST_AUX.report_date = DATE_FORMAT(study_report.report_date,'%Y-%m-%d %h:%i:%s'), STUDY_LIST_AUX.report_status = study_report.report_status
        WHERE STUDY_LIST_AUX.study_iuid = study_report.study_iuid";


$this->db->query($query_update);

It seems to be a issue with the CodeIgnigter Framework on the update integrated function ($this->db->update(...)). It doesn't work with sub queries, and the best solution goes for using the normal query execution function ($this->db->update($query))

Anyway, thanks for the help ;) All your answers were very helpful in a way to get to this solution.

Upvotes: 0

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385144

Pay closer attention.

Table 'pacsdb.STUDY_LIST_AUX,'

See that comma at the end? That should not be there. The error message is right.

Upvotes: 0

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

This may cause you real problem.Remove ,study_report after your table name.Try it

$this->db-
>update('STUDY_LIST_AUX', 
$data, 
 array('STUDY_LIST_AUX.study_iuid'=>
'study_report.study_iuid'));

Upvotes: 2

Related Questions