关系型数据库查询MySQL

发布于 2024-12-06 11:54:16 字数 1014 浏览 2 评论 0原文

我修改了表的名称。 sportevents 表是主表,它应该从以下表中获取数据:event_date、events、results 和 Members。有没有办法做到这一点。请不要我需要保留这个结构。

sportevents (link table)
• id
• event_id
• date_id
• result_id

event_date
• id
• date

events
• id
• eventname

results
• id
• result

members
• id (the ID number of a person)

userlogin
• id
• username
• password

我已经成功地在没有连接的情况下得到了正确的结果。以下:

$query = "SELECT * FROM members, sportevents, dates, results, event, userlogin ". 
         "WHERE userlogin.username = '$un' " . 
         "AND sportevents.id = members.id " . 
         "AND sportevents.event_id = event.id " .
         "AND sportevents.date_id = dates.id " .
         "AND sportevents.result_id = results.id";

$results = mysql_query($query)
    or die(mysql_error());
    while ($row = mysql_fetch_array($results)) {
    echo $row['eventname'];
    echo " - ";
    echo $row['year'];
    echo " - ";
    echo $row['result'];

    }

给我这个:

Karoo Cycle - 2008 - 1h14mins

I have modified the names of my Tables. The sportevents table is the main table and it should get its data from the tables: event_date, events, results, and members. Is there a way to do this. Please not i need to keep this structure.

sportevents (link table)
• id
• event_id
• date_id
• result_id

event_date
• id
• date

events
• id
• eventname

results
• id
• result

members
• id (the ID number of a person)

userlogin
• id
• username
• password

I have managed to get it right without joins. The following:

$query = "SELECT * FROM members, sportevents, dates, results, event, userlogin ". 
         "WHERE userlogin.username = '$un' " . 
         "AND sportevents.id = members.id " . 
         "AND sportevents.event_id = event.id " .
         "AND sportevents.date_id = dates.id " .
         "AND sportevents.result_id = results.id";

$results = mysql_query($query)
    or die(mysql_error());
    while ($row = mysql_fetch_array($results)) {
    echo $row['eventname'];
    echo " - ";
    echo $row['year'];
    echo " - ";
    echo $row['result'];

    }

Gives me this:

Karoo Cycle - 2008 - 1h14mins

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

待"谢繁草 2024-12-13 11:54:16

我假设您将成员的数据存储在 $_SESSION['member'] 或 smth 中。我不知道为什么你在事件、event_date 上分开了标签(在我看来应该有一个),我猜 main_events 类似于事件的组/类别。

并且您缺少会员 - 活动链接。将字段“member_id”添加到事件表中。

如果真是这样的话,那就是这样的事情了。

$q = 'SELECT e.*, ed.year, r.result FROM events As e 
LEFT JOIN event_date As ed ON ed.id = e.id
LEFT JOIN result As r ON r.id = e.id
WHERE e.member_id = ' . $_SESSION['member']['id'];

从中您可以获得

事件 ID、事件名称、事件年份、结果。 “JohnSmith”您可以从 $_SESSION['member'] 获取。

如果您决定不对事件、事件日期、结果使用单独的表,而只使用一张包含多个字段的表,则无需任何 LEFT JOINS,只需非常简单的 SELECT 查询即可完成此操作。

I presume that you have member's data stored in to $_SESSION['member'] or smth. And i dont know exactly why you have separated tabes on event, event_date (there should be one in my perspective) and I guess that main_events is something like event's group/category.

and you are missing MEMBER - EVENT link. add field 'member_id' to the events table.

If that's what it is then it's something like that.

$q = 'SELECT e.*, ed.year, r.result FROM events As e 
LEFT JOIN event_date As ed ON ed.id = e.id
LEFT JOIN result As r ON r.id = e.id
WHERE e.member_id = ' . $_SESSION['member']['id'];

from that you get

event id, event name, event year, result. "JohnSmith" you can get from $_SESSION['member'].

If you decide not to use separate tables for event, event_date, result and use only one table with more fields u can do this without any LEFT JOINS just very simple SELECT query.

羁绊已千年 2024-12-13 11:54:16

首先,您需要将事件结果链接。这取决于您的域模型,但我假设每个事件只有一个结果,因此我会将 result_id 添加到 events 表中。另外,您需要以某种方式将成员与事件链接起来,我将使用

events_members
member_id
main_event_id

table.通过此版本,您将能够让多个成员参与多个活动。

最后,查询将类似于以下内容:

select m.username, e.eventname, y.year, r.result
from members m
join events_members em on em.member_id = m.id
join main_events me on em.main_event_id = me.id
join event e on e.id = me.event_id
join year y on y.id = me.year_id
join result r on r.id = e.result_id
where m.username = $username

First, you need to link event with a result. It depends on your domain model, but I'll assume you have only one result per event, so I'll add result_id to the events table. Also, you need to link members with events somehow, I'll use

events_members
member_id
main_event_id

table. With this vcersion you'll be able to have multiple members participate in multiple events.

And finally, the query would be something like the following:

select m.username, e.eventname, y.year, r.result
from members m
join events_members em on em.member_id = m.id
join main_events me on em.main_event_id = me.id
join event e on e.id = me.event_id
join year y on y.id = me.year_id
join result r on r.id = e.result_id
where m.username = $username
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文