transform mysql table

tomasd · February 21, 2008

Hi,

I need some help or ideas please...

I've got a pretty large table consisting of 4 fields, there are 3 fields I'm particularly interested in 'check_date','price' and 'out_date'. If I select out_date,price,check_date from my table I'm getting similar output;

out_date price check_date

| 2008-04-02 | 29.99 | 2008-2-20 |

| 2008-04-03 | 34.99 | 2008-2-20 |

| 2008-04-04 | 24.99 | 2008-2-20 |

| 2008-04-04 | 34.99 | 2008-2-20 |

| 2008-04-05 | 59.99 | 2008-2-20 |

| 2008-04-05 | 39.99 | 2008-2-20 |

| 2008-04-06 | 59.99 | 2008-2-20 |

| 2008-04-07 | 34.99 | 2008-2-20 |

| 2008-04-07 | 29.99 | 2008-2-20 |

| 2008-04-08 | 29.99 | 2008-2-20 |

| 2008-04-09 | 29.99 | 2008-2-20 |

| 2008-04-10 | 34.99 | 2008-2-20 |

| 2008-04-11 | 34.99 | 2008-2-20 |

| 2008-04-11 | 24.99 | 2008-2-20 |

| 2008-04-12 | 34.99 | 2008-2-20 |

| 2008-04-12 | 59.99 | 2008-2-20 |

| 2008-04-13 | 14.79 | 2008-2-20 |

| 2008-04-13 | 24.99 | 2008-2-20 |

| 2008-04-14 | 29.99 | 2008-2-20 |

| 2008-04-15 | 29.99 | 2008-2-20 |

| 2008-04-16 | 29.99 | 2008-2-20 |

| 2008-04-17 | 34.99 | 2008-2-20 |

| 2008-04-18 | 24.99 | 2008-2-20 |

| 2008-04-19 | 34.99 | 2008-2-20 |

| 2008-04-20 | 14.79 | 2008-2-20 |

| 2008-04-20 | 24.99 | 2008-2-20 |

| 2008-04-21 | 29.99 | 2008-2-20 |

| 2008-04-22 | 29.99 | 2008-2-20 |

| 2008-04-23 | 29.99 | 2008-2-20 |

| 2008-04-24 | 34.99 | 2008-2-20 |

| 2008-04-25 | 39.99 | 2008-2-20 |

| 2008-04-25 | 24.99 | 2008-2-20 |

| 2008-04-26 | 34.99 | 2008-2-20 |

| 2008-04-27 | 14.79 | 2008-2-20 |

| 2008-04-27 | 24.99 | 2008-2-20 |

| 2008-04-28 | 29.99 | 2008-2-20 |

| 2008-04-29 | 0.02 | 2008-2-20 |

| 2008-04-30 | 29.99 | 2008-2-20 |

| 2008-04-02 | 29.99 | 2008-2-21 |

| 2008-04-03 | 34.99 | 2008-2-21 |

| 2008-04-04 | 29.99 | 2008-2-21 |

| 2008-04-04 | 34.99 | 2008-2-21 |

| 2008-04-05 | 59.99 | 2008-2-21 |

| 2008-04-05 | 39.99 | 2008-2-21 |

| 2008-04-06 | 59.99 | 2008-2-21 |

| 2008-04-07 | 34.99 | 2008-2-21 |

| 2008-04-07 | 29.99 | 2008-2-21 |

| 2008-04-08 | 29.99 | 2008-2-21 |

| 2008-04-09 | 29.99 | 2008-2-21 |

| 2008-04-10 | 34.99 | 2008-2-21 |

| 2008-04-11 | 34.99 | 2008-2-21 |

| 2008-04-11 | 24.99 | 2008-2-21 |

| 2008-04-12 | 34.99 | 2008-2-21 |

| 2008-04-12 | 59.99 | 2008-2-21 |

| 2008-04-13 | 29.99 | 2008-2-21 |

| 2008-04-13 | 24.99 | 2008-2-21 |

| 2008-04-14 | 29.99 | 2008-2-21 |

| 2008-04-15 | 29.99 | 2008-2-21 |

| 2008-04-16 | 29.99 | 2008-2-21 |

| 2008-04-17 | 34.99 | 2008-2-21 |

| 2008-04-18 | 24.99 | 2008-2-21 |

| 2008-04-19 | 34.99 | 2008-2-21 |

| 2008-04-20 | 14.79 | 2008-2-21 |

| 2008-04-20 | 24.99 | 2008-2-21 |

| 2008-04-21 | 29.99 | 2008-2-21 |

| 2008-04-22 | 29.99 | 2008-2-21 |

| 2008-04-23 | 29.99 | 2008-2-21 |

| 2008-04-24 | 34.99 | 2008-2-21 |

| 2008-04-25 | 39.99 | 2008-2-21 |

| 2008-04-25 | 24.99 | 2008-2-21 |

| 2008-04-26 | 34.99 | 2008-2-21 |

| 2008-04-27 | 14.79 | 2008-2-21 |

| 2008-04-27 | 24.99 | 2008-2-21 |

| 2008-04-28 | 29.99 | 2008-2-21 |

| 2008-04-29 | 0.01 | 2008-2-21 |

| 2008-04-30 | 29.99 | 2008-2-21 |

Is there any way re-arranging fields so it looks like this:

out_date price_20 price_21

| 2008-04-02 | 29.99 | 29.99|

| 2008-04-03 | 34.99 | 34.99|

| 2008-04-04 | 24.99 | 29.99|

| 2008-04-04 | 34.99 | 34.99|

| 2008-04-05 | 59.99 | 59.99|

| 2008-04-05 | 39.99 | 39.99|

| 2008-04-06 | 59.99 | 59.99|

| 2008-04-07 | 34.99 | 34.99|

| 2008-04-07 | 29.99 | 29.99|

| 2008-04-08 | 29.99 | 29.99|

| 2008-04-09 | 29.99 | 29.99|

| 2008-04-10 | 34.99 | 34.99|

| 2008-04-11 | 34.99 | 34.99|

etc...

is that possible to do?

Any help appreciated!

beebum · February 21, 2008

If I understand you correctly, you want something like this:

SELECT out_date, price AS price_20, price*.95 AS price_21...

That's assuming 20 and 21 are quantities where a discount is applied.

tomasd · February 22, 2008

Thanks for the suggestion, but not quite.

I'm not sure but I think I need select within select. I tried following;

mysql> select out_date, (select price from STN_MJV where cast(check_date as date)='2008-02-20') as price_20, (select price from STN_MJV where cast(check_date as date)='2008-02-21') as price_21 from STN_MJV;

ERROR 1242 (21000): Subquery returns more than 1 row

tomasd · February 22, 2008

It works on a small table...

mysql> select * from STN_MJV;
+---------------------+------------+----------+--------+
| check_date          | out_date   | out_time | price  |
+---------------------+------------+----------+--------+
| 2007-12-20 11:36:24 | 2007-12-21 | 11:25:00 | 139.99 |
| 2007-12-21 11:36:24 | 2007-12-21 | 11:25:00 |  19.99 |
+---------------------+------------+----------+--------+
2 rows in set (0.00 sec)

mysql> select out_date, (select price from STN_MJV where cast(check_date as date)='2007-12-20') as price_20, (select price from STN_MJV where cast(check_date as date)='2007-12-21') as price_21 from STN_MJV;
+------------+----------+----------+
| out_date   | price_20 | price_21 |
+------------+----------+----------+
| 2007-12-21 |   139.99 |    19.99 |
| 2007-12-21 |   139.99 |    19.99 |
+------------+----------+----------+
2 rows in set (0.00 sec)

fenway · February 22, 2008

It will work if you add an ORDER BY <yourField> LIMIT 1... just decide which price you want.

aschk · February 22, 2008

I'm not sure ORDER BY is the right answer. What he's looking for is a pivot table of sorts, however I would like to know the following:

1) What is the other (4th) field

2) What is your key on the table? i.e. index

the reason I ask is that this data seems terribly unnormalised as first glance. The 4th field might help identify what's going on.

fenway · February 22, 2008

You're right, I just re-read the initial post again... hard to decide how to "match up" the out_date/check_date pair prices with each other.

aschk · February 22, 2008

Yeah I thought they were just duplicate entries, but if you have an extended look through the data you'll see that some aren't in fact duplicate rows, which thus leads me to believe that the 4th column is significant in identifying the row differences.

tomasd · February 24, 2008

Well... I wrote this php script that does data mining on one of budget airline website in UK. All works very well

Stage 1: curl query

Stage 2: regex on html

Stage 3: write flight date, flight time, flight price to mysql

stage 4: report <- working on that now.

Database structure

check_date is timestamp when data was written to the table

out_date is date of the flight (I check like 300 days to the future from now[i can specify any range])

out_time is time of the flight

price is the price

mysql> describe STN_KUN;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| check_date | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| out_date   | date         | NO   |     |                   |       |
| out_time   | time         | NO   |     |                   |       |
| price      | decimal(5,2) | YES  |     | NULL              |       |
+------------+--------------+------+-----+-------------------+-------+

I'm trying to draw graphs using jpgraph

$sql_getprice = "select price,check_date from STN_KUN where out_date='2008-03-18'";
$sql_query = mysql_query ($sql_getprice);
$ystack = array();
while($row = mysql_fetch_array($sql_query)){
  array_push($ystack,$row['price']);
}
$sql_query = mysql_query ($sql_getprice);
$xstack = array();
while($row = mysql_fetch_array($sql_query)){
  array_push($xstack,$row['check_date']);
}

Problem is that I can only have 1 line per graph, unless my mysql query is 'clever'. Currently I'm fetching like

check_date,price which is for a particular out_date. I want to be able to get check_date,price1 for day1, price2 for day 2, price 3 for day 3, price 4 for day 4 so I can have 4 lines on same graph.

See graph attached here http://img295.imageshack.us/img295/6721/graphcn0.gif

Any ideas on how to transform that table for jpgraph are greatly appreciated!

Cheers

Tom

Sign In

transform mysql table

Recommended Posts

tomasd

Link to comment

Share on other sites

beebum

Link to comment

Share on other sites

tomasd

Link to comment

Share on other sites

tomasd

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

aschk

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

aschk

Link to comment

Share on other sites

tomasd

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information