Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    3

    Unanswered: SQL-String problem

    Hi there,

    i´ve got the following SQL-Problem: I am working on a picture gallery and wanted to display the last 6 photos uploaded but only 1 photo per user.

    The table have:
    --------------------
    fotoid, userid, picturename, thumbname, category, dbtimestamp

    but the String:
    --------------------
    SELECT fotoid, userid, picturename, thumbname, category, dbtimestamp FROM gallery WHERE category <> 99 GROUP BY fotoid,userid ORDER BY fotoid DESC LIMIT 0, 6
    --------------------

    wont work. it displays 2 or more photos from a user...

    this one wont work too... gives me an error...

    ---------------------------------------
    SELECT fotoid, userid, picturename, thumbname, category, dbtimestamp FROM gallery WHERE (((fotoid) In (SELECT Max(fotoid) AS Maxfromfotoid, userid FROM gallery GROUP BY userid))) ORDER BY dbtimestamp DESC LIMIT 0, 6
    ----------------------------------------

    any ideas?

    thanx for your help

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    What error do you get for the second query? What version of MySQL are you on? If it is older than 4.1 you can't do subqueries.

  3. #3
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by Timeout
    this one wont work too... gives me an error...

    ---------------------------------------
    SELECT fotoid, userid, picturename, thumbname, category, dbtimestamp FROM gallery WHERE (((fotoid) In (SELECT Max(fotoid) AS Maxfromfotoid, userid FROM gallery GROUP BY userid))) ORDER BY dbtimestamp DESC LIMIT 0, 6
    ----------------------------------------
    What error?

    --
    felix

  4. #4
    Join Date
    Oct 2005
    Posts
    3
    The error is...

    mysql error: 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 'SELECT Max(fotoid) AS Maxfromfotoid, userid FROM gallery
    mysql error number: 1064
    mysql version: 4.0.15-nt

  5. #5
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by Timeout
    The error is...
    mysql error: 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 'SELECT Max(fotoid) AS Maxfromfotoid, userid FROM gallery
    mysql error number: 1064
    mysql version: 4.0.15-nt
    As guelphdad already said earlier: you can't do subqueries in your version of MySQL.

    Perhaps http://dev.mysql.com/doc/refman/4.1/...group-row.html
    can enlighten you.

    --
    felix

  6. #6
    Join Date
    Oct 2005
    Posts
    3
    Now i updated mySql and got this error...

    mysql error: Operand should contain 1 column(s)
    mysql error number: 1241
    mysql version: 4.1.14-nt

  7. #7
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by Timeout
    Now i updated mySql and got this error...
    Replace
    Code:
    SELECT Max(fotoid) AS Maxfromfotoid, userid FROM gallery GROUP BY userid
    with

    Code:
    SELECT Max(fotoid) AS Maxfromfotoid FROM gallery GROUP BY userid
    --
    felix

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •