How to select order data from the database?

[dougramirez]

I'm trying to create a query that will allow me to retrieve all active orders, their order products, and pricing information. I can seem to find what would amount to an "order items" table in the database. I suspect that the order product/item information is buried in the tblorders.orderdata column, but I don't know for sure.

 

I know I can retrieve this information from the API by calling Get Clients Products, but that doesn't meet my technical requirements. That being a SQL query to be executed on an interval to extract this data.

 

Can someone please help me figure out how to select this data?

 

Thanks in advance.

 

Doug

[steven99]

The API would be easier to do indeed. Why does the API not meet your tech requirements? Have you looked at Get Orders http://docs.whmcs.com/API:Get_Orders ?

 

SQL form would be like:

 

select * from tblorders left join tblhosting on tblhosting.orderid = tblorders.id where tblorders.status ='Active';