Solved

A daily price table

Posted on 2004-09-22
7
355 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
selecting date modified field from a table 2 52
Problem with Simple PHP/mySQL Query 3 76
sort in mysql based off of query param 4 26
Showing random records from database 10 44
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

860 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