What I am wanting to do is query my database "sc2" and search 3 tables for certain information.
我想要做的是查询我的数据库“sc2”并搜索3个表以获取某些信息。
I have 3 tables
我有3张桌子
===
Matches
====
matchid
matchname
casterid
eventid
===
Casters
===
casterid
casteralias
casterurl
===
Events
===
eventid
eventname
The current SQL statement I am using works to a certain extent:
我正在使用的当前SQL语句在某种程度上起作用:
$sql = "
SELECT m.title AS matchTitle, c.casteralias matchCaster, m.dateadded matchDate,
comp.eventname FROM matches m
LEFT JOIN
casters AS c
ON
c.casterid = m.casterid
LEFT OUTER JOIN
competitions AS comp
ON m.eventid = comp.id
WHERE m.title LIKE '%".$search."%'
OR
c.casteralias LIKE '%".$search."%'
OR
comp.eventname LIKE '%".$search."%'
ORDER BY
m.dateadded
DESC";
It pulls the necessary information but I am wanting to distinguish within the result set the originating table therefore I can go about manipulating the results based on what table the found results came from.
它提取了必要的信息,但我想在结果集中区分原始表,因此我可以根据找到的结果来自哪个表来操作结果。
matchTitle matchCaster matchDate eventname
Hero vs Revival HuskyStarcraft 2013-06-05 15:54:05 \N
Idra vs Stephano HuskyStarcraft 2013-05-30 13:28:05 \N
Stephano vs BabyKnight HuskyStarcraft 2013-05-29 14:31:46 WCS Europe
Trimaster vs Destiny HuskyStarcraft 2013-05-28 18:26:45 \N
RorO vs Innovation HuskyStarcraft 2013-05-27 22:04:28 \N
Psy vs Camara HuskyStarcraft 2013-05-26 02:13:10 \N
Minigun vs Maker HuskyStarcraft 2013-05-23 20:05:00 \N
Results seem fine.. I just need to determine a way to differentiate the actual display of the search results based on what was searched in the first place. So if the user searches for a caster, instead of getting the recent match information, they are getting a thumbnail of the caster along with recent cast from that specific caster.
结果似乎很好..我只需要确定一种方法来区分搜索结果的实际显示,这是基于首先搜索的内容。因此,如果用户搜索施法者,而不是获取最近的匹配信息,他们将获得施法者的缩略图以及来自该特定施法者的最近投射。
I hope I am making myself clear and thank you in advance for any advice you may have to give. :)
我希望我能清楚地表达自己的意见,并提前感谢您提出的任何建议。 :)
1 个解决方案
#1
0
Bit messy, but is this what you are after
有点乱,但这就是你所追求的
Select * from (
SELECT "Matches" as Source, m.title AS matchTitle,
c.casteralias as matchCaster, m.dateadded as matchDate, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE m.title LIKE '%".$search."%'
Union
SELECT "Caster", m.title,
c.casteralias,m.dateadded, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE c.casteralias LIKE '%".$search."%'
Union
SELECT "Event", m.title,
c.casteralias,m.dateadded, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE comp.eventname LIKE '%".$search."%') allLikes
ORDER BY matchDate DESC
#1
0
Bit messy, but is this what you are after
有点乱,但这就是你所追求的
Select * from (
SELECT "Matches" as Source, m.title AS matchTitle,
c.casteralias as matchCaster, m.dateadded as matchDate, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE m.title LIKE '%".$search."%'
Union
SELECT "Caster", m.title,
c.casteralias,m.dateadded, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE c.casteralias LIKE '%".$search."%'
Union
SELECT "Event", m.title,
c.casteralias,m.dateadded, comp.eventname
FROM matches m
LEFT JOIN casters AS c ON c.casterid = m.casterid
LEFT OUTER JOIN competitions AS comp ON m.eventid = comp.id
WHERE comp.eventname LIKE '%".$search."%') allLikes
ORDER BY matchDate DESC