Login

01-05-2010, 04:02 AM

[eluser]maria clara[/eluser]
hi to all,

i encountered a database error that shows this:

in the MODEL:

Code:
<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_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, erp_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_no asc

LIMIT 10</p>    </div>

thanks in advance,
maria

01-05-2010, 04:06 AM

[eluser]rogierb[/eluser]

Code:
FROM (`erp_ar_so`, erp_ar_so)

Seems invalid. You cannot join the same table twice with the same name.

I guess what you are trying to do is

Code:
FROM (`erp_ar_so` erp_ar_so)

or just plain

Code:
FROM (`erp_ar_so`)

01-05-2010, 07:08 PM

[eluser]maria clara[/eluser]
[quote author="rogierb" date="1262707605"]

Code:
FROM (`erp_ar_so`, erp_ar_so)

Seems invalid. You cannot join the same table twice with the same name.

I guess what you are trying to do is

Code:
FROM (`erp_ar_so` erp_ar_so)

or just plain

Code:
FROM (`erp_ar_so`)

[/quote]

hi,

yes..it should have only 1 table but i dont know why it shows 2 same table name in the error.
thanks for the response.

regards,
maria

01-05-2010, 07:11 PM

[eluser]theprodigy[/eluser]
can you please post your model (or function) that is trying to run the query?
It will make it easier to debug your code if we can see it Wink

01-05-2010, 07:48 PM

[eluser]maria clara[/eluser]
hi,

here's my query in the MODEL:

Code:
$page = $this->input->post('page');

        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid 

        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort 

        $sord = $this->input->post('sord'); // get the direction

        $query = $this->input->post('query');

        $qtype = $this->input->post('qtype');

        if (!$sidx) $sidx = 'so_id';

        if (!$sord) $sord = 'asc';

        if (!$page) $page = 1;

        if (!$limit) $limit = 25;

        $start = (($page-1) * $limit);

        $this->db->start_cache();

        if ($qtype == 'so_no' && $query) $this->db->like('so_no', $query); 

        if ($qtype == 'so_no' && $query) $this->db->like('so_id', $query);

        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->join('sec_dataset k','ar_so.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');

        $this->db->from('ar_so');

        $this->db->where('ar_so.is_deleted','0');

        $num = $this->db->count_all_results();

        $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", true);

        $this->db->order_by($sidx,$sord);

        $this->db->limit($limit, $start);

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

in the CONTROLLER:

Code:
switch($postConfig['action']){

            case $crudConfig['create']:

                $c = "";

                $dt['so_no'] = $this->input->post('so_no');

                $sec_exist = $this->AR_salesorder_db->getDetails($dt);

                if ($ar_exist)

                {

                    $c .= 'Sales Order ID already exists.';

                    $data['action'] = 'exist';

                }

                else

                {

                $fields = array(

                        "so_no"

                        ,"type_id"

                        ,"client_id"

                        ,"address"

                        ,"contact"

                        ,"del_client_id"

                        ,"del_address"

                        ,"del_contact"

                        ,"ms_id"

                        ,"pd_id"

                        ,"salesman_id"

                        ,"remarks"

                        ,"customer_po"

                        ,"currency_id"

                        ,"currency_rate"

                        ,"pay_type_id"

                        ,"term_id"

                        ,"int_term_id"    

                        ,"gross"    

                        ,"discount"    

                        ,"net"    

                        );

                foreach ($fields as $field)

                {if (isset($_POST[$field])) $dt[$field] = $this->input->post($field);}

                $item = $this->input->post("item");        

                $data['item'] = $this->AR_salesorder_db->save($dt,$item);

                }

                break;

            case $crudConfig['update']:

                $sql = 'update '.$crudTableName.' set ';

                foreach($crudColumns as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };

                $sql .= implode(',',$updateArray);

                $sql .= ' where so_id = '.$crudColumnValues['id'];

                mysql_query( $sql ); 

                break;

            case $crudConfig['delete']:

                $this->db->where('so_id', $this->input->post('id'));

                $this->db->delete($crudTableName); 

                break;

            }

        if (isset($data)) echo json_encode($data);

01-05-2010, 07:57 PM

[eluser]theprodigy[/eluser]

Code:
$this->db->from('ar_so');

Code:
$query = $this->db->get("ar_so");

You don't need to set both the from() AND pass the table into get(). Only use 1. This is why it is calling your table twice in your query.

01-05-2010, 08:11 PM

[eluser]maria clara[/eluser]
i commented the

Code:
$this->db->from('ar_so')

and now i have this database error:

Code:
&lt;html&gt;

&lt;head&gt;

&lt;title&gt;Database Error&lt;/title&gt;

&lt;style type="text/css"&gt;

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;

}

&lt;/style&gt;

&lt;/head&gt;

&lt;body&gt;

    <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 'LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_id`=`erp_b`.`cust_i' at line 2</p><p>SELECT COUNT(*) AS `numrows`

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'</p>    </div>

&lt;/body&gt;

&lt;/html&gt;

01-05-2010, 08:15 PM

[eluser]theprodigy[/eluser]
try putting

Code:
$this->db->from('ar_so')

back in, and taking the "ar_so" out of

Code:
$this->db->get('ar_so')

EDIT:
leave just

Code:
$this->db->get()

01-05-2010, 08:20 PM

[eluser]maria clara[/eluser]
[quote author="theprodigy" date="1262765721"]try putting

Code:
$this->db->from('ar_so')

back in, and taking the "ar_so" out of

Code:
$this->db->get('ar_so')

EDIT:
leave just

Code:
$this->db->get()

[/quote]

i have just tried your advice but it seems it doesnt work..it still shows this error:

Code:
<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`, `erp_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_no` asc

LIMIT 10</p>

its now on the query of the joined table

01-05-2010, 08:35 PM

[eluser]maria clara[/eluser]
i already have fixed it but it shows my console this error:

Code:
str is null

anonymous(Object name=E)jquery-1....2.min.js (line 12)

anonymous(Object name=xml, div.ui-jqgrid-bdiv, Object name=rcnt value=0, Object name=more value=false, Object name=adjust value=0)jquery.j...id.min.js (line 10)

anonymous(XMLHttpRequest readyState=4 status=200 statusText=OK, "parsererror")jquery.j...id.min.js (line 10)

L()jquery-1....2.min.js (line 19)

anonymous(190)jquery-1....2.min.js (line 19)

[Break on this error] if (str.match("date")=="date")\r\nscript.js (line 1541)

E is null

[Break on this error] (function(){var l=this,g,y=l.jQuery,p=l....each(function(){o.dequeue(this,E)})}});\njquery-1....2.min.js (line 12

hope you can give me advice on this..

thanks,
maria