Tech Upoad .csv file to MySQL DB and collate data?

[Publisher Bucks]

So one of our sales channels gives us a .csv file at the end of every pay period with full sales stats in it, I can get it into an SQL database through myAdmin no problems, im working on coding a .php script to upload it directly, that isnt the issue.

Once the data is upload to the SQL database, how would i go about manipulating it to show the data in a more collated form, grouping together all titles, sales numbers, etc?

For example, how would I go about coding a page (or section) that shows the total sales over the past year of Title1, Title2, Title3, etc grouped together?

I can display the data of each row individually based on Title name, but some of the titles have 100-200 sales a month, thats a lot of scrolling to collect individual sales figures per title, I know there is a way to do it automagically, what should I be looking for online tutorial wise to achieve this?

Thanks

[sarettah]

Select title, sum(amount) to total_amount from tablename where invoice_date>=date_sub(curdate(), INTERVAL 1 YEAR) group by title

.

[Publisher Bucks]

Originally Posted by sarettah

Select title, sum(amount) to total_amount from tablename group by title

.

Awesome, thanks

Now another question I have is, can I include that in a drop down menu to select a range of different options? For example, Title, Author, Sales Channel, etc, etc?

I'm trying to minimize the amount of navigation required so just being able to throw together 1 page and have a drop down to change reports would be a perfect solution, if its not too involved lol

[sarettah]

Originally Posted by Publisher Bucks

Awesome, thanks

Now another question I have is, can I include that in a drop down menu to select a range of different options? For example, Title, Author, Sales Channel, etc, etc?

I'm trying to minimize the amount of navigation required so just being able to throw together 1 page and have a drop down to change reports would be a perfect solution, if its not too involved lol

You can put the different choices in the dropdown and use the choice coming in to create the query, yes.

.

[Publisher Bucks]

Originally Posted by sarettah

You can put the different choices in the dropdown and use the choice coming in ot create the query.

.

How would I go about transfering the dropdown option to the SQL query? JavaScript or?

[Publisher Bucks]

Sorry, one more question about the total_amount... If a row has -1 would that be taken into account when the code collates the total or, will that just do individual columns?

(does that make sense?)

[sarettah]

Originally Posted by Publisher Bucks

How would I go about transfering the dropdown option to the SQL query? JavaScript or?

You do it like any other form processing. Your "<select>" is the html element for a dropdown.

When the form is submitted you capture the POST or GET corresponding to the "select" element. Then you use that value in your query.

Originally Posted by Publisher Bucks

Sorry, one more question about the total_amount... If a row has -1 would that be taken into account when the code collates the total or, will that just do individual columns?

(does that make sense?)

That does not make sense to me, not sure what you are asking.

.

[Publisher Bucks]

Originally Posted by sarettah

That does not make sense to me, not sure what you are asking.

.

I have a column with sales, for example 5, and another column that has refunds, -2 would the total_amount be 3 when displayed?

[Publisher Bucks]

CREATE TABLE `Stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`start` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`end` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`publisher` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`titleid` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`isbn` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
`author` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`distributor` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`vendor` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`unitssold` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`unitsreturned` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`unitsnet` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`model` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`retailer` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`listprice` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`currency` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`offerprice` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`percentage` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`channelfee` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`channelrevenue` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`channelextended` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`channelshare` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`distributorshare` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`publisherestimate` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`publisherpayout` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`verified` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The two bolded lines above.

In essence, sales vs. chargebacks.

[sarettah]

Originally Posted by Publisher Bucks

I have a column with sales, for example 5, and another column that has refunds, -2 would the total_amount be 3 when displayed?

No, because you have not summed the 2 columns. The code I posted only sums one column.

You would have to do something like select sum(sales + refunds) to total_amount

But don't take my word for it. Run some test queies, play with the sql some so you can see the results.

.

[Publisher Bucks]

Originally Posted by sarettah

No, because you have not summed the 2 columns. The code I posted only sums one column.

You would have to do something like select sum(sales + refunds) to total_amount

But don't take my word for it. Run some test queies, play with the sql some so you can see the results.

.

Okay, thats what I wanted to confirm, thanks, will play with this in a little while

[sarettah]

<html>
<head>
<base href="https://gfy.com/" /><!--[if IE]></base><![endif]-->
<base href="https://gfy.com/" /><!--[if IE]></base><![endif]-->
</head>
<body>
<?php

//establish oyur database connection here
// assuming your connection is called $db
// using PDO because it is best to use

$validtypes=array('title','author','channel');

if(isset($_POST['reptype']))
{
$rtype=stripslashes($_POST['reptype']);
if(in_array($rtype,$validtypes))
{
$sql_str="select ?, sum(invoice_amount) as total_amount from tablename where date_sub(curdate(), INTERVAL 1 YEAR) group by ?";
$result=$db->prepare($sql_str));
$result->execute($rtpe, $rtype);
while($row=$result->fetch(PDO::FETCH_ASSOC))
{
// echo data here
}
}
else
{
// put some error code here
}

echo "<br><a href=thispage.php>Run another report</a></br>";

}
else
{
?>
<form name="reportform" method=POST" action="thispage.php">
Select report type:
<select name="reptype" onChange="this.form.submit();">
<option "title">By Title</option>
<option "author">By Author</option>
<option "channel">By Channel</option>
</select>
</form>
<?php
}
?>
</body>
</html>

[andy erotic]

@publisherbucks Forget doing it the hard way, set up metabase (very quick and simple to get up and running) or redash (both free) and use their query builder and choose the way you want the data to display in minutes. It will pull the data directly from the database. You can choose the type of charts to display. Then build a dashboard to group your charts for easier and quicker viewing.