Having MySQL do the date calculations for you
I've always been a fan of keeping the database and the programming separate, but I definitely like using the database to restricting the data that comes back from a query (by using WHERE clauses properly).
Traditionally I'd use PHP to calculate a date two days in the past, format it accordingly and then use a WHERE timestamp < = NOW() AND timestamp >= $two_days_ago, but Mark showed me this earlier:
SELECT * FROM motorworld_stock WHERE first_seen BETWEEN NOW() AND (NOW() - INTERVAL 2 DAY)
I like learning really basic things that are going to be useful over and over again.
Two things to note:
- If the database server is running on a different machine than the web server NOW() might return a different time stamp. Either the machines need to be synced for time or the script should use PHP’s time stamp.
- INTERVAL 2 DAY is not SQL standards compliant, so if the query is moved to a different database it may not work.
True, but in both cases those restrictions don’t apply to our situation.
Wow, Raj never taught us that!
The only critical thing I’ll add is you should only select the columns that you require ;)
Yeah you’re got a good point Lil’ John. And Raj did teach us a few interesting things but not that ;)
We had an applicant this week claim he was the best stored procedure writer… I think he stopped short of saying in Perth but I quite literally think he thought he was the hottest and stinkiest shit ever at SQL. Now actually being the hottest and stinkiest shit at SQL I decided to try and slaughter him. It was pretty funny. Don’t get me wrong he was much better than we’d seen but still he’d be in a learning position here :) We offered him a position at the $$’s he asked and he turned us down !!
BTW while more than competent I am not the hottest and stinkiest at SQL … it was a joke :P I’m just the best where I work :P haha
I was crap at Database and SQL at Uni.
But since working at WN I’ve picked it up so much that I teach my “subordinates” SQL.
What does that have to say about the 4+ years it took me to do my degree?
It teaches you that when someone’s paying you, instead of grading you, you’ve more incentive to learn about it :)
I think it’s about the doing… so at Uni we were forced and did the bare minimum. Now I do SQL coding probably at least an hour solid a day sometimes days on end just SQL so you naturally get better at it.
I think to be really good at coding for databases you have to start thinking things differently and approaching things idfferently too.
Personally I find challenge DB stuff more fun than challenging application code work.