Beginner at PHP syntax, mySQL_query error

Question

I'm attempting to make a page that is just a CREATE VIEW using DreamWeaver CS5. How do I need to input my code to just receive a table with all data relevant to the logged in user.

mysql_query(
CREATE VIEW UserResults
AS 
   SELECT E.No, E. Description
      , Count(R.RID WHERE $Username= R.Owner)
      , Count(R.RID WHERE $Username= R.Owner AND Status==’Active’ )
   FROM ETable AS E, RTABLE as R
   ORDER BY E.No)

You also shouldn't generally be creating views from code -- just like tables, they stay around once they're created, so you should only have to create them once when your application is installed. — , Dec 05 '11 at 20:19

score 3 · Answer 1 · answered Dec 05 '11 at 20:11

You have to put your query in quotes to make it a string.

mysql_query("CREATE VIEW UserResults AS SELECT E.No, E. Description, Count(R.RID WHERE $Username= R.Owner), Count(R.RID WHERE $Username= R.Owner AND Status==’Active’ ) FROM ETable AS E, RTABLE as R ORDER BY E.No")

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

Daniel is correct about the quotes. However, you should probably take a look at PDO, the mysql_ functions are a bit outdated. You can read more about that here.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 05 '11 at 20:15

SuperTron

4,203
6
35
62

score 1 · Answer 3 · answered Dec 06 '11 at 06:43

You have an extra ending parenthesis, which should be removed
Views should not have ORDER BY.
The WHERE clause should not be in the middle of the SELECT, you will need to use a CASE.
You are returning a Cartesian product instead of joining your tables together, this may provide more results than you want/expect.

The R.RID in the THEN block of the CASE statements may need to be a 1, not entirely sure what you hoped to get from those COUNT statements you originally had.

I tried to figure out what you were trying to do, and I think this is it:

CREATE VIEW UserResults AS
   SELECT E.No, E.Description
      , SUM(CASE 
               WHEN $Username = R.Owner THEN R.RID
               ELSE 0
            END) AS SumOfOwner
      , SUM(CASE
               WHEN $Username = R.Owner AND Status = 'Active' THEN R.RID
               ELSE 0
            END) AS SumOfOwnerAndActive
   FROM ETable AS E
   INNER JOIN RTABLE as R ON E.No = R.ENo
   GROUP BY E.No, E.Description

Beginner at PHP syntax, mySQL_query error

3 Answers3