Solved

A daily price table

Posted on 2004-09-22
7
359 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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

695 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