[Skip to main content](#vessel-content) # Create Order Reports (in sql or otherwise) ## - **[nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile")** - [![nkelkar's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/gallery/dog.gif)](/forum/user/5012-nkelkar "View nkelkar's Profile") Topic Author - [ Offline](/forum/user/5012-nkelkar) Less More - Posts: 7 - Thank you received: 1 - - - - 14 years 7 months ago [\#44570](#44570) by [nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile") *[Create Order Reports (in sql or otherwise)](/forum/4-how-to/44570-create-order-reports-in-sql-or-otherwise#44570 "Hi We sell event tickets, and have 2 products - Adult and Child tickets. How can I run a report in SQL (via phpmyadmin) or otherwise to see how many of Adult tickets and Child tickets - basically products - were purchased? I also need a list (group by account name). e.g PersonA brought 2 Adult tickets and 3 Child tickets PersonB brought 5 Adult tickets and 2 Child tickets The report should spit out something like: PersonA - Adult 2 - Child 3 PersonB - Adult 5 - Child 2 Total - Adult 7 - Child 5 This is basically to plan how many people will be attending. Thanks...")* was created by *[nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile")* Hi We sell event tickets, and have 2 products - Adult and Child tickets. How can I run a report in SQL (via phpmyadmin) or otherwise to see how many of Adult tickets and Child tickets - basically products - were purchased? I also need a list (group by account name). e.g PersonA brought 2 Adult tickets and 3 Child tickets PersonB brought 5 Adult tickets and 2 Child tickets The report should spit out something like: PersonA - Adult 2 - Child 3 PersonB - Adult 5 - Child 2 Total - Adult 7 - Child 5 This is basically to plan how many people will be attending. Thanks... Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQtaG93LXRvLzQ0NTcwLWNyZWF0ZS1vcmRlci1yZXBvcnRzLWluLXNxbC1vci1vdGhlcndpc2U/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[tony@10uk.net](/forum/user/6276-tony-10uk-net "View tony@10uk.net's Profile")** - [![tony@10uk.net's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/6276-tony-10uk-net "View tony@10uk.net's Profile") - [ Offline](/forum/user/6276-tony-10uk-net) Less More - Posts: 127 - Thank you received: 12 - - 14 years 7 months ago [\#44601](#44601) by [tony@10uk.net](/forum/user/6276-tony-10uk-net "View tony@10uk.net's Profile") Replied by *[tony@10uk.net](/forum/user/6276-tony-10uk-net "View tony@10uk.net's Profile")* on topic *[Re: Create Order Reports (in sql or otherwise)](/forum/4-how-to/44570-create-order-reports-in-sql-or-otherwise#44601 "Hi We sell event tickets, and have 2 products - Adult and Child tickets. How can I run a report in SQL (via phpmyadmin) or otherwise to see how many of Adult tickets and Child tickets - basically products - were purchased? I also need a list (group by account name). e.g PersonA brought 2 Adult tickets and 3 Child tickets PersonB brought 5 Adult tickets and 2 Child tickets The report should spit out something like: PersonA - Adult 2 - Child 3 PersonB - Adult 5 - Child 2 Total - Adult 7 - Child 5 This is basically to plan how many people will be attending. Thanks...")* Check out the developer documentation resources at [ www.hikashop.com/en/support/documentatio...r-documentation.html](http://www.hikashop.com/en/support/documentation/62-hikashop-developer-documentation.html) Scroll to the end to see the data model and download it. If you know how to read an ERD and can do some SQL wrapped in a bit of PHP then you're sorted. Looks great in SQLWorkbench. Tony Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQtaG93LXRvLzQ0NTcwLWNyZWF0ZS1vcmRlci1yZXBvcnRzLWluLXNxbC1vci1vdGhlcndpc2U/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile")** - [![nkelkar's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/gallery/dog.gif)](/forum/user/5012-nkelkar "View nkelkar's Profile") Topic Author - [ Offline](/forum/user/5012-nkelkar) Less More - Posts: 7 - Thank you received: 1 - - - - 14 years 7 months ago - 14 years 7 months ago [\#44630](#44630) by [nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile") Replied by *[nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile")* on topic *[Re: Create Order Reports (in sql or otherwise)](/forum/4-how-to/44570-create-order-reports-in-sql-or-otherwise#44630 "Hi We sell event tickets, and have 2 products - Adult and Child tickets. How can I run a report in SQL (via phpmyadmin) or otherwise to see how many of Adult tickets and Child tickets - basically products - were purchased? I also need a list (group by account name). e.g PersonA brought 2 Adult tickets and 3 Child tickets PersonB brought 5 Adult tickets and 2 Child tickets The report should spit out something like: PersonA - Adult 2 - Child 3 PersonB - Adult 5 - Child 2 Total - Adult 7 - Child 5 This is basically to plan how many people will be attending. Thanks...")* I had to do this on a bit of an emergency notice. Its great to have an ERD, I simply opened the tables in phpMyAdmin and figured out what I needed. I wll now check the ERD to see if I did something wrong (hopefully not). For others who might be looking to do the same, here are the snippets of code. **FIRST**, the SQL Query that will get Count of the Quantities. So I know I sold 'n' number of Adult Tickets and 'y' number of Child Tickets. This is to get a number so we can order food, arrange for appropriate venue, etc. SELECT count(tableprefix_hikashop_order_product.order_product_id) as Orders, tableprefix_hikashop_order_product.order_product_name as Item, sum(tableprefix_hikashop_order_product.order_product_quantity) as Quantity FROM tableprefix_hikashop_order_product INNER JOIN tableprefix_hikashop_order ON tableprefix_hikashop_order_product.order_id=tableprefix_hikashop_order.order_id WHERE tableprefix_hikashop_order.order_status="confirmed" Group By order_product_name **SECOND**, Details of people attending, and what they purchased (line items from the order_products table). Had to work on some fancy inner joins (4 table join). If you want to limit to some particular products, use something on lines of -- AND tableprefix_hikashop_product.product_name like '%Membership' -- SELECT tableprefix_users.name, tableprefix_users.email, tableprefix_hikashop_order.order_number, DATE_FORMAT(FROM_UNIXTIME(tableprefix_hikashop_order.order_created),'%d/%m/%Y') as created, tableprefix_hikashop_product.product_name as item, tableprefix_hikashop_order_product.order_product_quantity as quantity, tableprefix_hikashop_order_product.order_product_price as unit_price FROM tableprefix_users INNER JOIN tableprefix_hikashop_user ON tableprefix_hikashop_user.user_cms_id=tableprefix_users.id INNER JOIN tableprefix_hikashop_order ON tableprefix_hikashop_order.order_user_id=tableprefix_hikashop_user.user_id INNER JOIN tableprefix_hikashop_order_product ON tableprefix_hikashop_order_product.order_id=tableprefix_hikashop_order.order_id INNER JOIN tableprefix_hikashop_product ON tableprefix_hikashop_product.product_id=tableprefix_hikashop_order_product.product_id WHERE tableprefix_hikashop_order.order_status="confirmed" ORDER BY tableprefix_users.name ASC, tableprefix_hikashop_order.order_created ASC These two reports give me exactly what I was looking for. I embedded them into php, made some nice tables and the reports are available for the event organizers. Hope this helps. Last edit: 14 years 7 months ago by [nkelkar](/forum/user/5012-nkelkar "View nkelkar's Profile"). Reason: minor update The following user(s) said Thank You: [reeses007](/forum/user/8958-reeses007 "View reeses007's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQtaG93LXRvLzQ0NTcwLWNyZWF0ZS1vcmRlci1yZXBvcnRzLWluLXNxbC1vci1vdGhlcndpc2U/dG1wbD1tYXJrZG93bg==) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop- Install & Update- Product & Category Display- Content Creation- Sales and Orders- - Orders management- - Reports & Statistics- Customers- Checkout- Payment Methods- Shipping Methods- Import & Export (CSV, XLS…)- Specific Features- - Affilation- - Taxes- - Wishlist- - Vote- - Carousel & Slider- - Mass Actions- - Filters & Search- - Discount & Coupon- - Feature Request- Integrations- Showcase- General Talk about HikaShop- Development- - HikaShop 3.0 Beta- Translation- Commercial Jobs HikaShop Components- HikaMarket- - Market: Feature requests- - Market: How to?- - Market: Bug Report- HikaSerial- - General talk about HikaSerial- - Serial: Bug Report- - Serial: How to?- - Support en Français de HikaSerial (French Support)- - HikaSubscription- HikaAuction- - Auction: Feature requests- - Auction: How to?- - Auction: Bug report Hikashop around the world- Support en Français (French Support)- Russian- - FAQ. Курс выживания с Hikashop. Частые вопросы и о- - Расширения Hikashop- - Наши сайты на Hikashop- 日本語版サポート(Japanese Support)- Thai- German Archives- Bug Report- How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.129 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org) [](#top)