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. — user149341
– user149341, Commented Dec 5, 2011 at 20:19

Daniel A. White · Accepted Answer · 2011-12-05 20:11:51Z

3

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")

answered Dec 5, 2011 at 20:11

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:55:31Z

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, 2017 at 11:55

CommunityBot

11 silver badge

answered Dec 5, 2011 at 20:15

SuperTron

4,2537 gold badges40 silver badges62 bronze badges

Comments

Adam Wenger · Accepted Answer · 2011-12-06 06:43:17Z

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

Collectives™ on Stack Overflow

Beginner at PHP syntax, mySQL_query error

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related