SQL-String problem

Timeout · #1 (**permalink**) 10-10-05, 07:21

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

guelphdad · #2 (**permalink**) 10-11-05, 07:57

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.

felixg · #3 (**permalink**) 10-11-05, 08:51

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

Timeout · #4 (**permalink**) 10-12-05, 04:21

The error is...

Quote:

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

felixg · #5 (**permalink**) 10-12-05, 04:34

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

Timeout · #6 (**permalink**) 10-13-05, 10:09

Now i updated mySql and got this error...

Quote:

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

felixg · #7 (**permalink**) 10-13-05, 10:17

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

Internet Services

Web Development

Digital Marketing

Consumer Tech