Struggling with first attempt at creating a view

[MargateSteve]

I have been having a little play around to try to understand creating views now that I have upgraded to MySQL 5 and am stuck already (surprisingly enough!).

$query = mysql_query("
CREATE VIEW 
test 
AS
SELECT *
, DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate
,HT.team_name as HomeTeam
,VT.team_name as AwayTeam
,COMP.comp_short_name as CP
FROM
all_games
JOIN teams as HT ON (all_games.home_team = HT.team_id)
JOIN teams as VT ON (all_games.away_team = VT.team_id) 
JOIN competitions as COMP ON (all_games.comp = COMP.comp_id) 
WHERE 
$id = all_games.home_team AND all_games.home_goals IS NOT NULL 
OR
$id = all_games.away_team AND all_games.home_goals IS NOT NULL 
ORDER BY `date` DESC 
LIMIT 5");

$tpall_games = mysql_query("SELECT * FROM test");
if (!$tpall_games) {
    die(mysql_error());
};

 

The VIEW works perfectly if it is just used as a query, but I wanted to create a view so I can then run several different queries off of it.

The error returned is 'Table 'foo.test' doesn't exist' which suggests the VIEW is not being created.

 

I presume that I am missing something very simple such as actually executing the VIEW in some way before the query tries to read from it.

 

Steve

[requinix]

I'll skip over what I think is the source of the problem because it seems like there's another issue here.

 

Views don't accept arguments. They aren't functions. You can't create a view using some variable.

Basically, all a view does* is let you give a name to a complicated query - so you don't need to repeat a complicated query every time. When you do that then you can pretend it's a table**: SELECT from that and add your extra conditions then.

CREATE VIEW 
test 
AS
SELECT *
, DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate
,HT.team_name as HomeTeam
,VT.team_name as AwayTeam
,COMP.comp_short_name as CP
FROM
all_games
JOIN teams as HT ON (all_games.home_team = HT.team_id)
JOIN teams as VT ON (all_games.away_team = VT.team_id) 
JOIN competitions as COMP ON (all_games.comp = COMP.comp_id) 
WHERE 
all_games.home_goals IS NOT NULL 
OR
all_games.away_goals IS NOT NULL

SELECT *
FROM
test
WHERE
home_team = $id AND home_goals IS NOT NULL
OR
away_team = $id AND away_goals IS NOT NULL
ORDER BY `date` DESC
LIMIT 5

Create the view in your database - not in your script.

 

* It has other purposes too.

** For SELECTing. Not necessarily for other actions (eg, UPDATE or DELETE).

[MargateSteve]

Sorry for the delay in getting back, I have been a bit busy.

 

I always thought, for some reason, that views worked like queries in the page! I have got views working now so thanks for the help.

Steve