Jump to content

how to display data value in a row


meher0345
Go to solution Solved by Barand,

Recommended Posts

hi, 

i have a database 'sampledb" and one table 'sampletable' in it.  this table contain only three fields  named 'symbol' , 'price' , 'date' . the data type of symbol is varchar, float, and int respectively. I inserted date values as 20220928 format.

I want to display  data values horizontally on my web page  such as: 

symbol      current price      last week price      last month price      last year price

ABC              12.52                 11.0                          13.69                       12.2

PQR               6.5                    5.72                          5.0                           10.13

XYZ               150                    119                           200                           182

 

What php code / sql query should i  need to write?

thanks

 

Link to comment
Share on other sites

A better data type for prices is decimal EG

current_price DECIMAL(10,2) ,

Also your dates should be stored as DATE type (yyyy-mm-dd), not int. Then you get maximum utitlity from all the datetime functions.

What query have you tried so far?

Do you have data for every day going back 1 year or more?

Link to comment
Share on other sites

Ok. I modified the data type for price and date as you mentions. 

symbol VARCHAR(10)   ,   price DECIMAL(10,2) ,  date Date(12)

My ultimate target is to display stock prices on my website just like   tradingeconomics dot com/commodities     does.

Symbol_Name      Price    Change   Day_changed_in_%    Weekly_changed_in_%    Monthly_changed_in_%    YoY_changed_in_%    Date

 

In stock market, it is not necessary all symbol trade every day, majority symbol trade every day but some do not.  so for calculation of last week price, last month price and last year price , i like to select nearest back date.

currently, I have just 3 days data in my table as sample data.  I plan to import whole year data after cleaning it in my table. 

In excel if I have data like current price , last week price , last month price , yoy price  , then i can calculate % of all data  and display it on my page through table format. but unfortunately I could not get displaying data in desire format in phpmyadmin through sql statement.

so for I tried  "SELECT * FROM stockdata symbol where date = '2022-09-23' || date = '2022-09-28' order by symbol; " .  it show data in row by row,   not column wise.

 

 

 

Link to comment
Share on other sites

  • Solution

If you had data for every day for symbol it would be relatively simple

SELECT a.symbol
     , a.date as today
     , a.price as today_price
     , b.date as weekago
     , b.price as last_wk_price
     , c.date as monthago
     , c.price as last_mth_price
     , d.date as yearago
     , d.price as last_yr_price
FROM sampletable a 
     LEFT JOIN sampletable b ON a.symbol = b.symbol
                             AND b.date = a.date - INTERVAL 7 DAY
 	 LEFT JOIN sampletable c ON a.symbol = c.symbol
                              AND c.date = a.date - INTERVAL 1 MONTH
 	 LEFT JOIN sampletable d ON a.symbol = d.symbol
                              AND d.date = a.date - INTERVAL 1 YEAR
WHERE a.date = CURDATE(); 

which gives

++--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+
| symbol | today      | today_price | weekago    | last_wk_price | monthago   | last_mth_price | yearago    | last_yr_price |
+--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+
| ABC    | 2022-09-30 |       19.24 | 2022-09-23 |          5.96 | 2022-08-30 |           5.30 | 2021-09-30 |          3.86 |
| DEF    | 2022-09-30 |       19.52 | 2022-09-23 |         10.15 | 2022-08-30 |           7.61 | 2021-09-30 |         16.59 |
| XYZ    | 2022-09-30 |       15.00 |            |               | 2022-08-30 |          15.11 |            |               |
+--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+

ABC and DEF have daily data whereas XYZ on ly has 1 or 2 per week so, as you see, some results are missing. The tricky part is finding the closest date in the table to the required date. My solution was to use a MySQL user defined function.

CREATE FUNCTION `nearestDate`(sym varchar(20), thedate date) RETURNS date
    READS SQL DATA
BEGIN
	DECLARE v_closest DATE;
	DECLARE v_mindiff INT;
    
    SELECT date INTO v_closest                                   # do we have the exact date?
    FROM sampletable
    WHERE symbol = sym AND date = thedate
    LIMIT 1;
    
    IF v_closest IS NOT NULL THEN                                # if we do, return it
		RETURN v_closest;
    END IF;
    
	SELECT y.date INTO v_closest                                      # find smallest date difference
	FROM (
		   SELECT symbol
				, MIN(abs(datediff(date, thedate))) as mindiff
		   FROM sampletable
		   WHERE symbol = sym
		   ) x                                                       
		 JOIN                                                         # and match to find the date 
		 (
			SELECT date
			FROM sampletable
			WHERE symbol = sym
     ) y ON abs(datediff(y.date, thedate)) = x.mindiff;
        
	RETURN v_closest;                                            # return found date
END

The query using that function becomes

SELECT a.symbol
     , a.date as today
     , a.price as today_price
     , b.date as d7
     , b.price as last_wk_price
     , c.date as d30
     , c.price as last_mth_price
     , d.date as d365
     , d.price as last_yr_price
FROM sampletable a 
     LEFT JOIN sampletable b
               ON a.symbol = b.symbol
               AND b.date = nearestDate(a.symbol, curdate()-interval 7 day)
     LEFT JOIN sampletable c
               ON a.symbol = c.symbol
               AND c.date = nearestDate(a.symbol, curdate()-interval 1 month)
     LEFT JOIN sampletable d
               ON a.symbol = d.symbol
               AND d.date = nearestDate(a.symbol, curdate()-interval 1 year)
WHERE a.date = curdate();

now giving

+--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+
| symbol | today      | today_price | d7         | last_wk_price | d30        | last_mth_price | d365       | last_yr_price |
+--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+
| ABC    | 2022-09-30 |       19.24 | 2022-09-23 |          5.96 | 2022-08-30 |           5.30 | 2021-09-30 |          3.86 |
| DEF    | 2022-09-30 |       19.52 | 2022-09-23 |         10.15 | 2022-08-30 |           7.61 | 2021-09-30 |         16.59 |
| XYZ    | 2022-09-30 |       15.00 | 2022-09-25 |          2.87 | 2022-08-30 |          15.11 | 2021-09-29 |          4.20 |
+--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+

 

  • Like 1
Link to comment
Share on other sites

On further experimentation, a hybrid method, which only calls  on the function when the conventional query fails to find a matching date, seems to be the most efficient option.

In my tests, this method was 30 times faster (but that would depend on how many unmatched dates there were)

SELECT a.symbol
     , a.price as today
     , CASE WHEN b.price IS NOT NULL THEN b.price
            ELSE (
                  SELECT price FROM sampletable
                  WHERE symbol = a.symbol
                  AND date = nearestDate2(a.symbol, CURDATE() - interval 7 day)
                  )
                  END as last_wk
     , CASE WHEN c.price IS NOT NULL THEN c.price
            ELSE (
                  SELECT price FROM sampletable
                  WHERE symbol = a.symbol
                  AND date = nearestDate2(a.symbol, CURDATE() - interval 1 month)
                  )
                  END  as last_mth
     , CASE WHEN d.price IS NOT NULL THEN d.price
            ELSE (
                  SELECT price FROM sampletable
                  WHERE symbol = a.symbol
                  AND date = nearestDate2(a.symbol, CURDATE() - interval 1 year)
                  )
                  END  as last_yr
FROM sampletable a 
     LEFT JOIN sampletable b ON a.symbol = b.symbol
                             AND b.date = a.date - INTERVAL 7 DAY
     LEFT JOIN sampletable c ON a.symbol = c.symbol
                              AND c.date = a.date - INTERVAL 1 MONTH
     LEFT JOIN sampletable d ON a.symbol = d.symbol
                              AND d.date = a.date - INTERVAL 1 YEAR
WHERE a.date = CURDATE();

+--------+-------+---------+----------+---------+
| symbol | today | last_wk | last_mth | last_yr |
+--------+-------+---------+----------+---------+
| ABC    | 19.24 |    5.96 |     5.30 |    3.86 |
| DEF    | 19.52 |   10.15 |     7.61 |   16.59 |
| XYZ    | 15.00 |    2.87 |    15.11 |    4.20 |
+--------+-------+---------+----------+---------+

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.