|
Upoad .csv file to MySQL DB and collate data?
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 :) |
Select title, sum(amount) to total_amount from tablename where invoice_date>=date_sub(curdate(), INTERVAL 1 YEAR) group by title
. |
Quote:
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 |
Quote:
. |
Quote:
|
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?) |
Quote:
When the form is submitted you capture the POST or GET corresponding to the "select" element. Then you use that value in your query. Quote:
That does not make sense to me, not sure what you are asking. . |
Quote:
|
Quote:
In essence, sales vs. chargebacks. |
Quote:
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. . |
Quote:
|
<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> |
@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.
|
| All times are GMT -7. The time now is 08:04 PM. |
Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
©2000-, AI Media Network Inc