We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Raw SQL not Working Phalcon 3.x

Got it smoothly working with phalcon 2.x but not working with phalcon 3.x

        $data = new BlanketItemTb();
        $sql = "SELECT iit.item_id, iit.item_description, format(bit.ordered_quantity,0) as ordered_quantity, format(bit.remaining_quantity,0) as remaining_quantity
                FROM blanket_item_tb bit
                LEFT JOIN item_inventory_tb iit ON bit.item_id = iit.item_id
                WHERE bit.blanket_id = '$blanket_id' ORDER BY iit.item_id";

     return new Resultset(
            null,
            $data,
            $data->getReadConnection()->query($sql)
        );


145.0k

Dude, use parameter binding, right not it's not safe query beacause it can be sql injected. Also what you mean not working? Show us more info like what error etc.



956
in reply to Wojciech Ślawski

Dude, use parameter binding, right not it's not safe query beacause it can be sql injected. Also what you mean not working? Show us more info like what error etc.

Let's put aside first the binding, there's no particular error, however the returned result comes with full of nulls. As you can see here, phalcon 2 returns all data without null

Json Response Phalcon 2.X

[{"blanket_id":"80","date":"2017-08-12","blanket_no":"BL0000078","po_no":"456456","customer_name":"PACIFIC PAINT","plant_location":"Plant A - ","action":"Post","sales_person":"Andy Co"},{"blanket_id":"79","date":"2017-08-12","blanket_no":"BL0000077","po_no":"123456","customer_name":"PACIFIC PAINT","plant_location":"Plant A - ","action":"Post","sales_person":"Andy Co"}]

Json Reponse Phalcon 3.X

[{"blanket_id":"80","blanket_no":"BL0000078","date":"2017-08-12","po_no":"456456","customer_id":null,"plant_id":null,"action":"Post","sales_person_id":null"}]



145.0k

But those are not fields you wrote you wrote in original post. Check in sql logs(add logger for queries if you don't have yet) what query is being made, then test it outside of phalcon if result is correct, if it's the same as phalcon - then it's not phalcon issue. If query is wrong then come back here.



956
in reply to Wojciech Ślawski
edited Sep '17

But those are not fields you wrote you wrote in original post. Check in sql logs(add logger for queries if you don't have yet) what query is being made, then test it outside of phalcon if result is correct, if it's the same as phalcon - then it's not phalcon issue. If query is wrong then come back here.

here's the query

$sql = "SELECT bmt.blanket_id, bmt.date, bmt.blanket_no, bmt.po_no, ct.customer_name, CONCAT(cpt.plant, ' - ', cpt.city) as plant_location, bmt.action, CONCAT(spt.first_name, ' ', spt.last_name) as sales_person FROM blanket_main_tb bmt LEFT JOIN customer_tb ct ON bmt.customer_id = ct.customer_id
LEFT JOIN customer_plants_tb cpt ON bmt.plant_id = cpt.plant_id LEFT JOIN sales_person_tb spt ON bmt.sales_person_id = spt.sales_person_id LEFT JOIN blanket_so_tb bst ON bmt.blanket_id = bst.blanket_id WHERE bst.blanket_id IS NULL ORDER BY bmt.blanket_id DESC";



956

Anyone can help me with this one?