Welcome Guest, Not a member yet? Register   Sign In
CodeIgniter Forums Archived Discussions Archived General Discussion [SOLVED]500 Internal Server Error
[SOLVED]500 Internal Server Error
  • El Forum
    Guest
  •  
#1
10-22-2009, 08:39 PM

[eluser]kikz4life[/eluser]
just upgraded my ci version to 1.7.2 and i encountered an 500 Internal Server Error in my website. its irritating becoz some of the module are working fine but some are not. grrR...

this is the error logs found by firebug and also to make it more detailed i've post my model.php here.

POST http://localhost/myweb/ar_salesorder/listview 500 Internal Server Error 1.59s
Code:
<html>
<head>
<title>Database Error</title>
<style type="text/css">

body {
background-color:    #fff;
margin:                40px;
font-family:        Lucida Grande, Verdana, Sans-serif;
font-size:            12px;
color:                #000;
}

#content  {
border:                #999 1px solid;
background-color:    #fff;
padding:            20px 20px 12px 20px;
}

h1 {
font-weight:        normal;
font-size:            14px;
color:                #990000;
margin:             0 0 4px 0;
}
</style>
</head>
<body>
    <div id="content">
        <h1>A Database Error Occurred</h1>
        <p>Error Number: 1064</p><p>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 'FROM (`erp_ar_so`)
LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_' at line 2</p><p>SELECT `so_id` as pkey, `so_no`, DATE_FORMAT(so_date, `'%b` %d, `%Y')`, `erp_e`.`data_display` as type, `erp_b`.`customer_name`, `emp_name`, `net`, `erp_ar_so`.`contact`, `k`.`data_display` as status
FROM (`erp_ar_so`)
LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_id`=`erp_b`.`cust_id`
LEFT JOIN `erp_maint_customers` erp_c ON `erp_ar_so`.`del_client_id`=`erp_c`.`cust_id`
LEFT JOIN `erp_maint_employee` erp_d ON `erp_ar_so`.`salesman_id`=`erp_d`.`emp_id`
LEFT JOIN `erp_sec_dataset` erp_e ON `erp_ar_so`.`type_id`=`erp_e`.`data_id`
LEFT JOIN `erp_sec_dataset` k ON `erp_ar_so`.`status`=`k`.`data_value` AND k.data_code = "TRAN_STATUS"
WHERE `erp_ar_so`.`is_deleted` = '0'
ORDER BY `so_date` desc
LIMIT 15</p>    </div>
&lt;/body&gt;
&lt;/html&gt;

This is my MODEL
Code:
function getList()
    {
        $sortname = $this->input->post('sortname');
        $sortorder = $this->input->post('sortorder');
        $page = $this->input->post('page');        
        $rp = $this->input->post('rp');        
        
        $query = $this->input->post('query');
        $qtype = $this->input->post('qtype');
        
        $stype = $this->input->post('s_type');
        $otype = $this->input->post('o_type');
        $skey = $this->input->post('s_key');
        if (!$sortname) $sortname = 'so_id';
        if (!$sortorder) $sortorder = 'DESC';
        
        if (!$page) $page = 1;        
        if (!$rp) $rp = 25;
                
        $start = (($page-1) * $rp);
        
        $this->db->start_cache();
        
        if ($stype)
        {
            foreach ($stype as $key => $value)
            {
                if ($skey[$key])
                {
                    if ($stype[$key]=='so_date')  $match = convertDate($skey[$key]);
                    else $match = $skey[$key];
                    
                    switch ($otype[$key])
                    {
                        case "=":
                            $this->db->where($stype[$key],$match);
                            break;
                        case "!=":
                            $this->db->where($stype[$key]." !=",$match);
                            break;
                        case "like":
                            $this->db->like($stype[$key],$match);
                            break;
                        case ">":
                            $this->db->where($stype[$key]." >",$match);
                            break;
                        case "<":
                            $this->db->where($stype[$key]." <",$match);
                            break;
                    }
                }
            }
            
        }
        //if (isset($filter)) $this->db->like($filter);
        
        $this->db->join('maint_customers erp_b','ar_so.client_id=b.cust_id','left');
        $this->db->join('maint_customers erp_c','ar_so.del_client_id=c.cust_id','left');
        $this->db->join('maint_employee erp_d','ar_so.salesman_id=d.emp_id','left');
        $this->db->join('sec_dataset erp_e','ar_so.type_id=e.data_id','left');
        $this->db->from('ar_so');
        $this->db->where('ar_so.is_deleted','0');
        $num = $this->db->count_all_results();        
    
        $this->db->join('sec_dataset k','ar_so.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');
        $this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status");
        $this->db->order_by($sortname,$sortorder);
        $this->db->limit($rp, $start);
        $query = $this->db->get("ar_so");
        echo $this->db->last_query();
        
        //echo $this->db->last_query();
        //exit;
        
        $this->db->flush_cache();

        $data['db'] = $query;        
        $data['page'] = $page;
        $data['num'] = $num;
        return $data;            
        
    }

in the previous version of CI 1.6.3 that i used its been working perfectly fine, i'm thinking that this is a bug in CI in terms of join sql statements.
  • El Forum
    Guest
  •  
#2
10-23-2009, 03:22 AM

[eluser]rogierb[/eluser]
Your select is getting escaped automatically
Code:
$this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status");

Just add 'false' as the second param and you should be good to go
Code:
$this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status", false);
  • El Forum
    Guest
  •  
#3
10-23-2009, 05:10 AM

[eluser]kikz4life[/eluser]
hi rogeirb,

btw, thanks for the reply. i did what you suggest about adding the second param false. But it still Internal Server Error & now it says this
Code:
<div id="content">
        <h1>A Database Error Occurred</h1>
        <p>Error Number: 1066</p><p>Not unique table/alias: 'erp_ar_so'</p><p>SELECT so_id as pkey, so_id, so_no, DATE_FORMAT(so_date, '%b %d, %Y'), erp_e.data_display as type, erp_b.customer_name, emp_name, net, erp_ar_so.contact, k.data_display as status
FROM (`erp_ar_so`, erp_ar_so)
LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_id`=`erp_b`.`cust_id`
LEFT JOIN `erp_maint_customers` erp_c ON `erp_ar_so`.`del_client_id`=`erp_c`.`cust_id`
LEFT JOIN `erp_maint_employee` erp_d ON `erp_ar_so`.`salesman_id`=`erp_d`.`emp_id`
LEFT JOIN `erp_sec_dataset` erp_e ON `erp_ar_so`.`type_id`=`erp_e`.`data_id`
LEFT JOIN `erp_sec_dataset` k ON `erp_ar_so`.`status`=`k`.`data_value` AND k.data_code = "TRAN_STATUS"
WHERE `erp_ar_so`.`is_deleted` = '0'
ORDER BY so_date desc
LIMIT 15</p>    </div>
&lt;/body&gt;

hope you can help me with this... thanks again rogeirb.
  • El Forum
    Guest
  •  
#4
10-23-2009, 05:42 AM

[eluser]rogierb[/eluser]
This is the error :
Code:
FROM (`erp_ar_so`, erp_ar_so)

Did you change the
Code:
$this->db->from('ar_so');
?
  • El Forum
    Guest
  •  
#5
10-23-2009, 07:18 AM

[eluser]kikz4life[/eluser]
nope, i didn't. back from my previous version of CI its all working "all modules". i even try to use single quotes or double in querying db. zzZZ.
  • El Forum
    Guest
  •  
#6
10-23-2009, 07:42 AM

[eluser]rogierb[/eluser]
Can you post the code? Right now we can only guess what changes you have made.
  • El Forum
    Guest
  •  
#7
10-25-2009, 08:41 PM

[eluser]kikz4life[/eluser]
Sorry for the late reply, weekends. lol.

This is my MAIN controller(interacts to all the modules).

Main_Controller.php
Code:
...............
        function Main()
    {
        parent::Controller();
        $method = $this->uri->rsegment(2);
        $user = $this->session->userdata('user');
        if (!$user) redirect('sec_users/login');
        
        $this->load->model('Dataset_db');
    }
    
    function index()
    {
        $data['navs'] = $this->Dataset_db->buildNav(0);
        $data['current_tab'] = 'dashboard';
        
        $data['content'] = $this->load->view('home',$data,true);
        $this->load->vars($data);
        $this->load->view('default_view');
    }
...............
Main_Model.php
Code:
.....
    function getTypes($datacode)
    {
        $this->db->select('data_id,data_value,data_display,description');
         //$this->db->where("data_code='$datacode' AND display='1'"); -- original code in 1.6.3
        $this->db->where("data_code",$datacode);
        $this->db->where("display",1);
        $this->db->orderby("order_idx", "asc");
        $query = $this->db->get('sec_dataset');
        return $query->result();        
    }
    
    function getSearchTypes($datacode)
    {
        $this->db->select('data_id,data_value,data_display,description');
         //$this->db->where("data_code='$datacode'"); -- original code in 1.6.3
        $this->db->where("data_code",$datacode);
        $this->db->orderby("order_idx", "asc");
        $query = $this->db->get('sec_dataset');
        return $query->result();        
    }
    
    function getNavs($parent)
    {
        $this->db->select('module_id,module,link,icon');
        $this->db->orderby("sort", "asc");
        $query = $this->db->get_where('sec_modules', array('publish' => '1','parent' => $parent));
        return $query->result_array();        
    }
.........
}

?&gt;

The changes i only made are those in the funtion of getTypes() and getSearchTypes(). comments provided.
  • El Forum
    Guest
  •  
#8
10-25-2009, 09:01 PM

[eluser]kikz4life[/eluser]
Part 2
This is salesorder module., one of the module that encounter this Server internal error. I'll post most of it codes here for better understanding. I added the second param false the select query in the salesorder_model.php

salesorder_controller.php
Code:
class AR_salesorder extends Controller {

    function AR_salesorder()
    {
        parent::Controller();
        $user = $this->session->userdata('user');
        if (!$user) redirect('sec_users/login');      
        $this->load->model('Dataset_db');
        $this->load->model('AR_salesorder_db');        
        $lang_select = $this->session->userdata('lang_select');
        $this->config->set_item('language', $lang_select);      
        $this->lang->load('language');
    }  
    function index()
    {    
        $data['showtoolbar'] = true;
        $data['so_ptitle'] = lang('so_ptitle');
        $data['navs'] = $this->Dataset_db->buildNav(0);
        //tab management
        $tabs = $this->session->userdata('tabs');
        if (!$tabs) $tabs = array();
        $tabs['ar_salesorder'] = $this->Dataset_db->getModule('ar_salesorder');
        $this->session->set_userdata('tabs',$tabs);        
        $data['current_tab'] = $tabs['ar_salesorder']['link'];        
        //access level
        $user = $this->session->userdata('user');
        $company = $this->session->userdata('company');
        $acess = $this->Dataset_db->getAccess($user['role_id'],$tabs['ar_salesorder']['module_id'],$company['company_id']);
        $data['access_level'] = $acess['access_level'];
        
        if (substr($acess['access_level'], 0,1)==0)
        {
            $data['showtoolbar'] = false;
            $data['content'] = "<div class='content'><h1>Restricted Access</h1><div>This module is restricted from <b>".$user['role_desc']."</b> users.</div></div>";
            $this->load->vars($data);
            $this->load->view('default_view');
            return true;
        }              
        $data['order_type'] = $this->Dataset_db->getTypes('CUST_TYPE');
        $data['internal_terms'] = $this->Dataset_db->getTypes('TERMS');
        $data['operator'] = $this->Dataset_db->getSearchTypes('OPERATOR');        
        $data['content'] = $this->load->view('home',$data,true);
        $this->load->vars($data);
        $this->load->view('default_view');
    }
    
    function listview()
    {
        $data = $this->AR_salesorder_db->getList();    
        $this->load->view('template/table',$data);
    }
    
    
}

salesorder_model.php
Code:
class AR_salesorder_db extends Model {

    function AR_salesorder_db()
    {
        // Call the Model constructor
         parent::Model();
    }
    
    function getList()
    {
        $sortname = $this->input->post('sortname');
        $sortorder = $this->input->post('sortorder');
        $page = $this->input->post('page');        
        $rp = $this->input->post('rp');        
        
        $query = $this->input->post('query');
        $qtype = $this->input->post('qtype');
        
        $stype = $this->input->post('s_type');
        $otype = $this->input->post('o_type');
        $skey = $this->input->post('s_key');
        if (!$sortname) $sortname = 'so_id';
        if (!$sortorder) $sortorder = 'DESC';
        
        if (!$page) $page = 1;        
        if (!$rp) $rp = 25;
                
        $start = (($page-1) * $rp);
        
        $this->db->start_cache();
        
        if ($stype)
        {
            foreach ($stype as $key => $value)
            {
                if ($skey[$key])
                {
                    if ($stype[$key]=='so_date')  $match = convertDate($skey[$key]);
                    else $match = $skey[$key];
                    
                    switch ($otype[$key])
                    {
                        case "=":
                            $this->db->where($stype[$key],$match);
                            break;
                        case "!=":
                            $this->db->where($stype[$key]." !=",$match);
                            break;
                        case "like":
                            $this->db->like($stype[$key],$match);
                            break;
                        case ">":
                            $this->db->where($stype[$key]." >",$match);
                            break;
                        case "<":
                            $this->db->where($stype[$key]." <",$match);
                            break;
                    }
                }
            }
            
        }
        
        $this->db->join('maint_customers erp_b','ar_so.client_id=b.cust_id','left');
        $this->db->join('maint_customers erp_c','ar_so.del_client_id=c.cust_id','left');
        $this->db->join('maint_employee erp_d','ar_so.salesman_id=d.emp_id','left');
        $this->db->join('sec_dataset erp_e','ar_so.type_id=e.data_id','left');
        $this->db->from('ar_so');
        $this->db->where('ar_so.is_deleted','0');
        $num = $this->db->count_all_results();        
    
        $this->db->join('sec_dataset erp_k','ar_so.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');
        $this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display, b.customer_name, emp_name, net, ar_so.contact, k.data_display", FALSE);
        $this->db->order_by($sortname,$sortorder);
        $this->db->limit($rp, $start);
        $query = $this->db->get('ar_so');
        
        $this->db->flush_cache();

        $data['db'] = $query;        
        $data['page'] = $page;
        $data['num'] = $num;
        return $data;            
        
    }
    
}
  • El Forum
    Guest
  •  
#9
10-26-2009, 12:52 AM

[eluser]rogierb[/eluser]
I cannot find table "erp_ar_so" in the code you posted. There is only "ar_so".

The first error you posted was about that table.
The second error is about another table, called "erp_ar_so".

Can you post the code relating to that table?
  • El Forum
    Guest
  •  
#10
10-26-2009, 01:34 AM

[eluser]kikz4life[/eluser]
its working fine now., what i did is remove the sec param false and replace the DATE_FORMAT(so_date,'%b %d, %Y') to so_no only. From the looks of it, the date_format(...) is whats causing the internal error. i've post another thread on why is it like that.

i'll work on the other module from now, wish the problem is related to this.. is it this hard to apply the updated version of CI..? The reason why i want to update the version of my CI in the website coz i want it to be open source.

btw, thanks for all the advise. I still think CI is really awesome framework. Smile




Theme © iAndrew 2016 - Forum software by © MyBB