A daily price table


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..
 
LVL 1
winmeisterAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
snoyes_jwConnect With a Mentor Commented:
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
 
snoyes_jwCommented:
How often does the price change?
0
 
winmeisterAuthor Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
snoyes_jwCommented:
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
 
winmeisterAuthor Commented:
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
 
winmeisterAuthor Commented:
As you are a PHP-master, can you suggest those lines in PHP?
0
 
snoyes_jwCommented:
$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
All Courses

From novice to tech pro — start learning today.