Jump to content

Barand

Moderators
  • Posts

    23,191
  • Joined

  • Last visited

  • Days Won

    680

Barand last won the day on October 1

Barand had the most liked content!

About Barand

Profile Information

Recent Profile Visitors

78,033 profile views

Barand's Achievements

Prolific Member

Prolific Member (5/5)

1.9k

Reputation

332

Community Answers

  1. PS When I run $pdo->exec("SELECT * FROM student"); $res = $pdo->fetchAll(); I get Fatal error: Uncaught Error: Call to undefined method PDO::fetchAll() Have you got error reporting switched on?
  2. PDO::exec() does not return a resultset (update, insert, delete only) query() or execute() to get a resultset.
  3. First step is to turn on PDO exception reporting and ensure PHP reporting is on.
  4. There is a forum on this site where you can requet work to be done https://forums.phpfreaks.com/forum/77-job-offerings/ Provide a description of what you need and contact details.
  5. 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.
  6. 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 | +--------+-------+---------+----------+---------+
  7. 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 | +--------+------------+-------------+------------+---------------+------------+----------------+------------+---------------+
  8. 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?
  9. function is_leapyear($year){ if(is_numeric($year)){ if($year%4 == 0) { // if it's divisible by 4 its probably a leapyar if($year%100 == 0) { // but check the century condition return ($year%400 == 0); // if its divisible by 400 return true otherwise return false } else return true; } } return false; // if we get to here, return false }
  10. try <?php if (isset($_GET["leapyear"]) && !empty($_GET['leapyear'])) { $result = (is_leapyear($_GET["leapyear"])) ? "<span style=\"color:#008631;\">{$_GET['leapyear']} is a leap year</span>" : "<span style=\"color:#FF0000;\">{$_GET['leapyear']} is not a leap year</span>" ; } else $result = '' ; function is_leapyear($year){ if(is_numeric($year)){ if($year%4 == 0) { if($year%100 == 0) { return ($year%400 == 0); } else return true; } } return false; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>Leap year form</title> </head> <body> <?= $result ?> <h1>Leap Year</h1> <form method = "get" > <label for="leapyear">Enter a year</label> <input type="text" name="leapyear" id="leapyear" autofocus/> <p><input type="submit" name="confirm" value="Check For Leap Year" /></p> </form> </body> </html>
  11. Define $WINNERS = []; before your query. Then if there are no query results the in_array() is still valid.
  12. Thank you for sharing that. Is there a question to go with it? Please use code button <> when posting code.
  13. Your problem is was where to use backticks and where to use single quotes.
×
×
  • 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.