How to add a fourth table to my request ?

Discussion in 'PHP' started by kalago, Oct 6, 2007.

0
  1. #1
    Hi,

    First, sorry for my low level in english.

    Here is the original request :

    $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

    It enables me to extract a chart made of 3 tables :
    TABLE_ORDERS with fields such as orders_id, customers_name etc...
    TABLE_ORDERS_TOTAL with fields such as orders_total_id, text, etc...
    TABLE_ORDERS_STATUS with fields such as orders_status_id, languages_id, orders_status_name


    I would like to add a fourth table in the request :
    TABLE_CMCIC_REFERENCE with fields such as orders_id et ref_id, in the view to extract ref_id.

    I tryied to built a new request taht is the following :

    $orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.orders_id), " . TABLE_ORDERS_TOTAL . " ot on (cm.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

    I have an error message saying :

    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 'on (cm.orders_id = ot.orders_id), orders_status s where o.orders

    select count(*) as total from orders o left join cmcic_reference cm on (o.orders_id = cm.orders_id), orders_total ot on (cm.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total'

    Who could help me solve this problem ?

    Bests regards.
     
    kalago, Oct 6, 2007 IP
  2. nabil_kadimi

    nabil_kadimi Well-Known Member

    Messages:
    1,065
    Likes Received:
    69
    Best Answers:
    0
    Trophy Points:
    195
    #2
    It's too hard to read when it's not formatted, please use vB code like this :
    // PHP CODE
phpinfo();
    PHP:
    <!-- HTML CODE -->
<body>
<p>See how it looks!</p>
</body>
    HTML:
     
    nabil_kadimi, Oct 6, 2007 IP
  3. kalago

    kalago Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I changed de code for :

    $orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.order_id) left join " . TABLE_ORDERS_TOTAL . " ot on (cm.order_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

    I get results this time but I face an other difficulty : in cmcic_reference table, order_id is a series of number including holes (1001, 1003, 1004, 1006, etc...). The holes represent people paying by checks (1002 and 1005). The resulat is the following : I have the cic numbers except the "0" so that my final chart is buit without the orders paid by checks.

    How could I fix this ?
     
    kalago, Oct 7, 2007 IP