Solved

A daily price table

Posted on 2004-09-22
7
358 Views
Last Modified: 2006-11-17

I am quite new to mysql, but I'm studing... Now I need to display in html several
"monthly" tables like this one:

==============================================
January 2005


      Rev      1      2      3      4      5      6......................31

PROD1        1      xxx      xxx      xxx      xxx      xx      xx            xxx
PROD1        2      xxx      xxx      xxxx      xx      xx      xx      x      xxx      
PROD3   1      ..      ..      ..
PROD4      1      ..      ..      ..      ..
PROD4   2        ...      ..      ..      ..      ..
...
...
PROD12  1      ..      ..            ..      ..            ...
==================================================

We can assume that is a daily price-table for 12 products. Some product can also have a Rev.1 or Rev.2
I am very doubtful concerning the best method to create the table with mysql and the select
to display such matrix ....
The user should then be able to change partial data, such as prices in a week or in two days..
 
0
Comment
Question by:winmeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12128056
How often does the price change?
0
 
LVL 1

Author Comment

by:winmeister
ID: 12128107
Not very often. Let's say that an administrator has to fill the table just once a month, then he can make very few adjustments during the month.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12128129
You could do something like this:
productId/date/price
prod1/01Jan/$1
prod1/02Jan/$3
prod1/03Jan/$4
You'll have ~350 rows per month, which is no problem.  If price changes are infrequent, you could simply store the day that the change was made, and use your programming language of choice to repeat that price until another change occurs.  Then you will only have 12 + numberChanges rows per month.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:winmeister
ID: 12130940
Yes, that could be good. You forgot the "revision" field, but I believe I can simply add: prodid/rev/date/price. No primary key, as I do not have a unique field...
 Let's assume I will work with the plain table (350 rows or more), how can I display the table data in the matrix form I have shown in my original question?
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 100 total points
ID: 12132571
You can make (prodid, rev) a primary key.

It would be easiest and best to display using scripting language of your choice - PHP, ASP, whatever.  The general algorithm:

query for rows in given month ordered by productId
for each queried row in record set {
  if(productVariable != row[productId]) {
    productVariable := row[productId]
    print new line
    print productId
  }
  print price
}
0
 
LVL 1

Author Comment

by:winmeister
ID: 12132896
As you are a PHP-master, can you suggest those lines in PHP?
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12134147
$sql = "SELECT * FROM theTable WHERE theDateField BETWEEN '2005-01-01' AND '2005-01-31' ORDER BY productId;
$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
  if ($productId != $row['productId'] || $revNum != $row['revNum']) {
    $productId = $row['productId'];
    $revNum = $row['revNum'];
    echo "\n";
    echo "$productId Rev. $revNum\t";
  }
  echo $row['price'] . "\t";
}

Obviously, if this is for a web page, you'd replace the \n with <br/> or </tr> and the \t with </td>
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql query for sum() 3 61
Why does Opencart Use Product tables use the MyISAM storage Engine 4 120
show child records separated by commas 12 49
Mysql query one to many 11 38
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question