选择多个表MySQL并检索不同的数据

I have 4 tables that I need to pull data from. I need to count how many people are signed for a single event and see if a user is applied for an event.

我有4个表,我需要从中提取数据。我需要计算为单个事件签名的人数,并查看用户是否申请了一个事件。

These are my table setups:

这些是我的表格设置:

TABLE: users
+----+----------+-------+--------+-------+
| id | username | level | class  | guild |
+----+----------+-------+--------+-------+
| 1  | example1 | 100   | Hunter | blah  |
| 2  | example2 | 105   | Mage   | blah2 |
| 3  | example3 | 102   | Healer | blah  |
+----+----------+-------+--------+-------+
ID is primary

TABLE: event_randoms
+----+----------+-------+--------+----------+----------+
| id | username | level | class  | apped_by | event_id |
+----+----------+-------+--------+----------+----------+
| 1  | random1  |  153  | Hunter |    3     |    3     |
| 2  | random2  |  158  | Healer |    3     |    1     |
| 3  | random3  |  167  | Warrior|    1     |    3     |
+----+----------+-------+--------+----------+----------+
ID is primary
apped_by should be foreign key to users.id
event_id should be foreign key to events.id

TABLE: events
+----+------------+------------+-----------+-----------+-----------+
| id | event_name | event_date | initiator | min_level | max_level |
+----+------------+------------+-----------+-----------+-----------+
| 1  |   event1   |    date1   |     1     |    100    |     120   |
| 2  |   event2   |    date2   |     1     |    121    |     135   |
| 3  |   event3   |    date3   |     1     |    100    |     120   |
| 4  |   event4   |    date4   |     1     |    150    |     200   |
+----+------------+------------+-----------+-----------+-----------+
ID is primary

TABLE: event_apps
+----+----------+--------------+
| id | event_id | applicant_id |
+----+----------+--------------+
| 1  |    3     |      2       |
| 2  |    4     |      2       |
| 3  |    3     |      1       |
| 4  |    1     |      3       |
+----+----------+--------------+
ID is primary
event_id should be foreign key to events.id
applicant_id should be foreign key to users.id

I will be the first to admit that I am very new to this. I just learned how to use MySQL a few days ago. I can grab stuff from a single table, but I am unsure how to grab from multiple tables.

我将是第一个承认我对此非常陌生的人。我刚刚学会了如何使用MySQL。我可以从单个表中获取内容,但我不确定如何从多个表中获取内容。

This is the SQL query I tried

这是我试过的SQL查询

SELECT DD_events.id, event_id, applicant_id, guild, level, class, DD_users.id
      FROM DD_events, DD_event_apps, DD_users
      WHERE DD_event_apps.event_id = DD_events.id
      AND DD_event_apps.applicant_id = DD_users.id

and tried to print_r an array but the array turns up empty.

并尝试print_r数组但数组变空。

So a few questions pertain to this: 1: How would I count and display as a number how many people (users and randoms) are signed up for an event? eg: event 3 should have 4 total (2 users and 2 randoms)

因此,有几个问题与此相关:1:我如何计算并显示为一个事件注册了多少人(用户和randoms)?例如:事件3应该总共有4个(2个用户和2个randoms)

2: How do I see if a particular individual is signed for an event and display text based if they are or not? eg: user 1 is signed up for event 3 so it would be "Registered" but user 2, who is not signed, would display "Not Registered"

2:如何查看特定个人是否为某个事件签名并根据他们是否显示文本?例如:用户1注册了事件3,因此它将被“注册”,但未签名的用户2将显示“未注册”

3: I want to display info for who is signed for a particular event in 2 tables, 1 for users and another for randoms. eg: Event 3 would have 2 users info (username, guild, class, level) under the users table and then 2 random users info (name, class, level, what user applied this person) in the random table.

3:我想显示2个表中为特定事件签名的人的信息,1表示用户,另一个表示randoms。例如:事件3在用户表下将有2个用户信息(用户名,公会,类,级别),然后在随机表中有2个随机用户信息(名称,类,级别,用户应用此人)。

Any and all help is appreciated even if you can answer 1 part.

即使您可以回答1部分,也可以感谢任何和所有帮助。

2 个解决方案

#1

I'm thinking this would be your base query:

我想这将是你的基本查询:

SELECT
    event.id,
    app.applicant_id,
    usr.guild,
    usr.level,
    usr.class,
    usr.id AS Userid
FROM
    DD_events event
JOIN
    DD_event_apps app
    ON  (event.id = app.event_id)
LEFT JOIN
    DD_users usr
    ON  (app.user_id = usr.id)

You can make modifications to this to aggregate it, like so:

您可以对此进行修改以对其进行聚合,如下所示:

SELECT
    event.id,
    COUNT(app.applicant_id) AS ApplicantCount,
    COUNT(DISTINCT usr.guild) AS UniqueGuilds,
    COUNT(DISTINCT usr.level) AS UniqueLevels,
    COUNT(DISTINCT usr.class) AS UniqueClasses,
    COUNT(DISTINCT usr.id) AS UniqueUsers
FROM
    DD_events event
JOIN
    DD_event_apps app
    ON  (event.id = app.event_id)
LEFT JOIN
    DD_users usr
    ON  (app.user_id = usr.id)
GROUP BY
    event.id

I could write those scripts for you, but I think this provides a good starting point for you to continue from. You'll find that T-SQL is fairly simple when you are trying to get the results you are looking for. Hope this helps!

我可以为你编写这些脚本,但我认为这为你提供了一个很好的起点。当你试图获得你想要的结果时,你会发现T-SQL相当简单。希望这可以帮助!

#2

<?php $query = "SELECT count(*) AS numbuh FROM DD_event_apps WHERE event_id = {$row['id']}";
try
{
    // These two statements run the query against your database table.
    $stmt = $db->prepare($query);
    $stmt->execute();
}
catch(PDOException $ex)
{
    // Note: On a production website, you should not output $ex->getMessage().
    // It may provide an attacker with helpful information about your code. 
    die("Failed to run query: " . $ex->getMessage());
}
echo($query);
// Finally, we can retrieve all of the found rows into an array using fetchAll
$count = $stmt->fetchAll();
echo($count['numbuh']); ?>

#1