/    Sign up×
Community /Pin to ProfileBookmark

Club Name by Date

Copy linkTweet thisAlerts:
Jul 25.2021

I’ve got a table, where my “club” has a name. Let’s say it’s the Los Angeles Swift.

However my project is looking at the club’s history. Back in 1924 they were called the Los Angeles Wildcats.

When viewing the results of a particular year. I want to look up the club name as per that year.

My idea is to do a clubName table. Linking to the clubData table. clubData will have clubId which I can index too.

I’ll then have start and end dates for the club name. I can search for the name that falls within any start/end dates.

My concern is, how can I avoid any overlap? what happens with the current name which has no end date?

Name changes in this project are common so need to have this catered for.

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@SempervivumJul 25.2021 — >how can I avoid any overlap? what happens with the current name which has no end date?

Simply omit end date. Select the names where the given date is greater than the start date and order by start date. The first row in the result will be the name at the given date:
``<i>
</i>SELECT name
FROM club_name
WHERE club_id = 47 AND DATE_FORMAT(start_date, '%Y-%m-%d') &gt; '1960-01-01'
ORDER by start_date ASC
LIMIT 1<i>
</i>``
Copy linkTweet thisAlerts:
@kiwisauthorJul 25.2021 — @Sempervivum#1634679

Hmmm so are you suggesting, just a startDate on each name entry and no endDates? so the next start date supersedes the previous one by ordering and limiting within the query

I'd never thought of that!
Copy linkTweet thisAlerts:
@SempervivumJul 26.2021 — Yes I do. There is a similar situation when coding actions for different times of the day. E. g. from 0:00 to 7:00 do this, from 07:00 to 12:00 do that, from 12:00 to 17:00 do another action ...

Beginners tend to code it like this:
``<i>
</i>// current hours in the corresponding variable
if (hour &gt;= 0 &amp;&amp; hour &lt; 7) {
// do this
} else if (hour&gt;= 7 &amp;&amp; hour &lt; 12) {
// do that
} else if (hour &gt;= 12 &amp;&amp; hour &lt; 17&gt;) {
// another action
} // and so on<i>
</i>`</CODE>
However it can be simplified to this:
<CODE>`<i>
</i>// current hours in the corresponding variable
if (hour &lt; 7) {
// do this
} else
// here we can be shure that hour is &gt;= 7
if (hour &lt; 12) {
// do that
} else if (hour &lt; 17) {
// another action
} // and so on<i>
</i>``
Copy linkTweet thisAlerts:
@kiwisauthorJul 28.2021 — So if a record has the name as ABC with Start date 2020-01-01

I search for a name based on the the date 2021-05-05

I should be able to do

<i>
</i>SELECT <span><code>clubName</code></span> FROM clubnaminghistory WHERE clubId = 9 AND startDate &gt;= '2021-04-30' LIMIT 1


I get no results.
Copy linkTweet thisAlerts:
@kiwisauthorJul 28.2021 — Duh I have to do Order by
Copy linkTweet thisAlerts:
@SempervivumJul 28.2021 — @kiwis80#1634807 Does this mean that it's working now?

Regarding your previous post, query without sorting, I would have expected that it's returning a **wrong** result, not an empy one?
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.23,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...