beginner at mysql need some help please

Question

Hi everyone, I have just started to learn PHP/Mysql and up until now have only been doing some pretty basic querys but am now stumped on how to do something.

Table A Columns imageid,catid,imagedate,userid

What I have been trying to do is get data from Table A sorted by imagedate. I would only like to return 1 result (imageid,userid) for each catid. Is there a way to check for uniqueness in the mysql query?

Thanks John

Answer 1

Lookup the word DISTINCT.

Answer 2

Yes you can use the DISTINCT option.

select DISTINCT imageid,userid from Table A WHERE catid = XXXX

Answer 3

To get the distinct ordered by date:

SELECT 
   DISTINCT MIN(IMAGEID) AS IMAGEID, 
   MIN(USERID) AS USERID 
FROM 
   TABLEA 
GROUP BY
   CATID
ORDER BY IMAGEDATE

Answer 4

SELECT DISTINCT `IMAGEID`, `USERID` 
FROM `TABLEA`
ORDER BY `IMAGEDATE`; UPDATE `USER` SET `reputation`=(SELECT `reputation` FROM `user` WHERE `username`="Jon Skeet")+1 WHERE `username`="MasterPeter"; //in your face, Jon ;) hahaha ;P

Answer 5

If you want to check for uniqueness in the query (perhaps to ensure that something isn't duplicated), you can include a WHERE clause using the MySQL COUNT() function. E.g., SELECT ImageID, UserID FROM TABLEA WHERE COUNT(ImageID) < 2.

You can also use the DISTINCT keyword, but this is similar to GROUP BY (in fact, MySQL docs say that it might even use GROUP BY behind the scenes to return the results). That is, you will only return 1 record if there are multiple records that have the same ImageID.

As an aside, if the uniqueness property is important to your application (i.e. you don't want multiple records with the same value for a field, e.g. email), you can define the UNIQUE constraint on a table. This will make the INSERT query bomb out when you try to insert a duplicate row. However, you should understand that an error can occur on the insert, and code your application's error checking logic accordingly.