I am trying to write a query that extracts the orders from the db including their id, options and model name.
However, I saw that all this information is spread into multiple tables... so I am stuck now
Any help will be very appreciated.
Thanks
Hello All,
I am trying to write a query that extracts the orders from the db including their id, options and model name.
However, I saw that all this information is spread into multiple tables... so I am stuck now
Any help will be very appreciated.
Thanks
I am trying to write a query that extracts the orders from the db including their id, options and model name.
However, I saw that all this information is spread into multiple tables... so I am stuck now
Any help will be very appreciated.
Thanks
use a join statement 
alternatively, use the models that are provided. That will save you from writing the statements again.
alternatively, use the models that are provided. That will save you from writing the statements again.
Thanks for the answer,
I will easy use the Join statement, but the problem that I am facing is that I don't know which are the tables that I have to join... the information for each order is spread in so many tables with unique ID in each table
What do you mean by "alternatively, use the models that are provided"? Where do I find these models?
Thanks again
I will easy use the Join statement, but the problem that I am facing is that I don't know which are the tables that I have to join... the information for each order is spread in so many tables with unique ID in each table
What do you mean by "alternatively, use the models that are provided"? Where do I find these models?
Thanks again
use the model files provided. $this->load->model('sale/order');
and call for the functions you need
and call for the functions you need
Ok, I am almost there...
SELECT *
FROM `order` RIGHT JOIN `order_option`
on order.order_id=order_option.order_id where order_status_id = 2 group by order_option.order_id
but for some reason I am not getting the records grouped by the order_id. Instead I am getting new row for each option
What I am missing?
SELECT *
FROM `order` RIGHT JOIN `order_option`
on order.order_id=order_option.order_id where order_status_id = 2 group by order_option.order_id
but for some reason I am not getting the records grouped by the order_id. Instead I am getting new row for each option
What I am missing?
try left join and group by order.order_idlargan wrote:Ok, I am almost there...
SELECT *
FROM `order` RIGHT JOIN `order_option`
on order.order_id=order_option.order_id where order_status_id = 2 group by order_option.order_id
but for some reason I am not getting the records grouped by the order_id. Instead I am getting new row for each option
What I am missing?
Code: Select all
SELECT *
FROM `order` o LEFT JOIN `order_option` oo
ON (o.order_id = oo.order_id)
WHERE o.order_status_id = 2
GROUP BY o.order_id
I have managed to do the JOIN query. Here is how it looks like
Code: Select all
SELECT
o.order_id,
o.name,
o.value,
GROUP_CONCAT(o.order_id, o.name, o.value SEPARATOR ',') AS Options
FROM `order_option` AS o
LEFT JOIN `order` AS oo on o.order_id = oo.order_id
where oo.order_status_id = 2
group by o.order_idI have just extended the query to get the product picture as well. No need of a loop I think. Here is it if it might help someone
Then you just print $id and $Options1
Code: Select all
$sql = "SELECT order_id, GROUP_CONCAT( CONCAT( '<img width=100 src=http://youropencart/image/', image, '>', '', model, '-' ) ,
Options, '' ) AS Options1
FROM (
SELECT o.order_id, order_product.model AS model, GROUP_CONCAT( oo.name, oo.value
SEPARATOR ',' ) AS Options, product.image
FROM `order` AS o
INNER JOIN order_option AS oo ON o.order_id = oo.order_id
INNER JOIN order_product ON oo.order_product_id = order_product.order_product_id
INNER JOIN product ON product.product_id = order_product.product_id
where o.order_status_id = 2
GROUP BY oo.order_product_id
) AS oop
GROUP BY order_id";
Who is online
Users browsing this forum: No registered users and 18 guests
