Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0

    Table Structure Inquiry


    Code:
    $q = 'SELECT y.*, (SUM(`rating`)/COUNT(`rating`)) AS `avg`, COUNT(`rating`) AS `num_rat` FROM `groupcomic` `y` LEFT JOIN `yrat` ON `sid` = `skey` where panels>=3 GROUP BY `sid` ORDER BY `avg` DESC LIMIT 0,5';

    This line seems to be causing problems in one of my scripts in that it keeps referring me to this line and saying there is an invalid MYSQL result resource. I'm fairly certain my table structure is off. Can someone please tell me, based on this line, what fields I should have in my table and and what type they should be?

    I know the table name is groupcomic.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    there are actually two tables, not just one

    run the SELECT statement directly in mysql, i.e. not from php, and see what error message you get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by r937
    there are actually two tables, not just one

    run the SELECT statement directly in mysql, i.e. not from php, and see what error message you get
    Yes, there are two tables I see, groupcomic and yrat. I just don't know the proper field types and structure for those tables based on this query.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    I ran the statement in MYSQL and got this:

    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$q = 'SELECT y.*, (SUM(`rating`)/COUNT(`rating`)) AS `avg`, COUNT(`rating`) AS `' at line 1
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    I would assume it has something to do with the num_rat field, but I honestly have no idea. I had it set up as INT (11).
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Padgoi
    I ran the statement in MYSQL and got this:
    you ran the php statement which assigns the SELECT statement to a string variable

    run just the SELECT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Padgoi
    I just don't know the proper field types and structure for those tables based on this query.
    run this query for each table --
    Code:
    SHOW CREATE TABLE tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by r937
    run this query for each table --
    Code:
    SHOW CREATE TABLE tablename
    The problematic table is definitely the groupcomic one and here's the table schema for that:

    groupcomic CREATE TABLE `groupcomic` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `title` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `creator` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `added` tinyint(1) NOT NULL,
    `panels` tinyint(1) NOT NULL,
    `nsfw` tinyint(1) NOT NULL,
    `file` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `parent` int(11) NOT NULL,
    `num_rat` int(11) NOT NULL,
    `user` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `skey` int(11) NOT NULL,
    `rating` int(11) NOT NULL,
    `image` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `tmp_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `new_music` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `ip` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by r937
    you ran the php statement which assigns the SELECT statement to a string variable

    run just the SELECT statement
    Ok, so I did that and got this error:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    this is your query, slightly reformatted --
    Code:
    SELECT y.*
         , SUM(rating)/COUNT(rating) AS avg
         , COUNT(rating) AS num_rat 
      FROM groupcomic y 
    LEFT OUTER
      JOIN yrat 
        ON sid = skey 
     WHERE panels >= 3 
    GROUP 
        BY sid 
    ORDER 
        BY avg DESC LIMIT 0,5
    there is nothing obviously wrong at this point

    could you do the SHOW CREATE TABLE for the other table too please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by r937
    this is your query, slightly reformatted --
    Code:
    SELECT y.*
         , SUM(rating)/COUNT(rating) AS avg
         , COUNT(rating) AS num_rat 
      FROM groupcomic y 
    LEFT OUTER
      JOIN yrat 
        ON sid = skey 
     WHERE panels >= 3 
    GROUP 
        BY sid 
    ORDER 
        BY avg DESC LIMIT 0,5
    there is nothing obviously wrong at this point

    could you do the SHOW CREATE TABLE for the other table too please
    yrat CREATE TABLE `yrat` (
    `rating` int(11) NOT NULL,
    `user` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `image` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `username` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `skey` int(11) NOT NULL,
    `ip` int(11) NOT NULL,
    `num_rat` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    okay, there's the problem -- both tables have a rating column

    which one are you trying to average?

    because it's not at all obvious which one of these is the "one" table and which is the "many" table in the one-to-many relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by r937
    okay, there's the problem -- both tables have a rating column

    which one are you trying to average?

    because it's not at all obvious which one of these is the "one" table and which is the "many" table in the one-to-many relationship
    Ok, so I dropped the rating field from the groupcomic table and I'm having the exact same error.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    16
    Rep Power
    0
    Originally Posted by Padgoi
    Ok, so I dropped the rating field from the groupcomic table and I'm having the exact same error.
    Here's the website . . . you can test it out as much as you want and you will see the errors that are showing up.

    http://wootability.com/newstrips.php
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Padgoi
    Ok, so I dropped the rating field from the groupcomic table and I'm having the exact same error.
    you removed the column??

    okay, so it's obviously the yrat rating that you're trying to average...

    try this --
    Code:
    SELECT groupcomic.*
         , SUM(yrat.rating)/COUNT(yrat.rating) AS yavg
         , COUNT(yrat.rating) AS num_rat 
      FROM groupcomic  
    LEFT OUTER
      JOIN yrat 
        ON yrat.skey = groupcomic.sid 
     WHERE groupcomic.panels >= 3 
    GROUP 
        BY groupcomic.sid 
    ORDER 
        BY yavg DESC LIMIT 0,5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo