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
3
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
0
<?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
3
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
0
<?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']); ?>