how to display data value in a row

[meher0345]

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

 

[Barand]

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?

[ginerjm]

The 'last..' dates - Are you looking for records that are related to the current date (today?) or are they related to the last date you have  on file for each unique 'symbol'?

[meher0345]

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.

 

 

 

[meher0345]

Due to all symbols do not trade every day, so copying/pasting historical data in excel does not work fine (missing of symbol on specific dates).  that's why I think I should do the task through some php scripting. By this i could get definite  price value.

[Barand]

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

 

[Barand]

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

 

[meher0345]

after importing desire back dates data into sampletable, I tried to run above sql queries, but I got the error

.png attached

[Barand]

If the name of the function you created is "nearestDate" then that is what the query should use (not "nearestDate2" which was an alternative version I tried.) But I'm sure you could have worked that out for yourself.

[meher0345]

Ok .  I will check it out.

however, very first solution working fine with "NULL" value if any price value is missing. 

thanks a lot for your help.