VirtueMart Forum

VirtueMart 2 + 3 + 4 => General Questions => Topic started by: j45p41 on August 08, 2014, 06:10:18 AM

Title: Help on A simple table creation
Post by: j45p41 on August 08, 2014, 06:10:18 AM
Hi,

I wonder if somebody can kindly help me on how to achieve the following:
1.   Get all items (products) that are contained in all orders between two predefined dates.
2.   Insert all these items into a database table
3.   add a further column with each item's corresponding category. i.e. forks, cutlery
4.   Create link that admin users can access that with display the contents of this table.
I am not an expert at sql or php so anybody that would be kind enough to help me out on this will have my gratitude – reaching out to the universe!

Thanks in Advance.

sorry moderator if this is in the wrong section.
Title: Re: Help on A simple table creation
Post by: GJC Web Design on August 08, 2014, 09:40:12 AM
Honestly think your expectations of what this forum is for is a bit much - it isn't "ask a question and we will provide free coding solutions..."

But a starting point will the the VM admin reports views or a 3rd party extension to export orders etc..
Title: Re: Help on A simple table creation
Post by: AH on August 08, 2014, 11:04:19 AM
Use excel with an odbc data link to the database

I doubt someone will spend the time doing this for free for you.

I suggest you employ someone with the pre-requisite skills to create what you have specified.

Title: Re: Help on A simple table creation
Post by: John2400 on August 08, 2014, 13:56:53 PM
In your revenue report in the shop you can see most of this. Just pick a date. It will give you a great report.
There are quite a few extensions that import and export data.
as well as quite few that give outstanding reports.
All with data you require.
Title: Re: Help on A simple table creation
Post by: j45p41 on August 08, 2014, 14:27:33 PM
Hi Guys,

thank you for your insights. My intention certainly was not for somebody to do it for me - it was a request to be pointed in the right direction or where to start. Apologies if it appears that I am abusing this site but I think I have been misunderstood.
Title: Re: Help on A simple table creation
Post by: AH on August 08, 2014, 19:59:12 PM
j45

no problems

This is relatively straightforward if you understand some sql and how to get data into excel from an external source use

Here are some links that may help

Instead why not create local copies of the database for reporting and then work on them using heidisql  http://www.heidisql.com/

Using "offline" data and direct local connections via either Heidisql or Excel ODBC will save you having to write specific code, reduces live server load and is Extremely flexible!

Updating the local dbase with a live db dump, takes seconds in heidisql.

Using heidisql, in many cases" will save you having to write specific code to get data as you can save the SQL result directly to a CSV file from heidisql

You can also build "snippets" of sql for re-use

you could connect to the database using the mysql odbc connector then create a report using excel and mysql

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html
excel data connections

http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx#BMunderstanding_data_connections
and

http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx


I hope this helps you and  anyone out there that wants to get to the data and really get to grips with their store.

Give it a go.