[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

A daily price table

Posted on 2004-09-22
7
Medium Priority
?
361 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 400 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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