Query about a query...

middleman666

hey people,

Been trying to do this for a while and don't seem to be getting anywhere so thought I'd ask for a bit of help.

I have two tables, Gallery and GalleryComments

What I'm trying to do is list out all the images that have comments given to them and I want them to list so the image with the newest comment will be first.

This is my current query:

$query = "SELECT g.filename filename, g.id id, g.date date, 
            g.category category, g.imagetext imagetext,
            DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
            DATE_FORMAT(g.date,'%H:%i') as time, 
            c.imageID imageID, c.date date
            FROM $TableName g, GalleryComments c
            WHERE g.username='".$username."' 
            AND c.imageID=g.id 
            AND c.imageID > 0 
            GROUP BY g.id 
            ORDER BY c.date DESC";

That does read out only the images that have comments on, but I'm having trouble listing them in order of latest comment.

Can anyone lend a hand?

Thanks.

Stryker250

I'm taking a shot in the dark with the names here but this should work

$query = "SELECT g.filename filename, g.id id, g.date date,
            g.category category, g.imagetext imagetext,
            DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
            DATE_FORMAT(g.date,'%H:%i') as time,
            c.imageID imageID, c.date date
            FROM $TableName g, GalleryComments c
            WHERE g.username='".$username."'
            AND c.imageID=g.id
            AND c.imageID > 0
            GROUP BY g.id
            ORDER BY g.date DESC, c.date DESC";

if that one doesnt work try this one

$query = "SELECT g.filename filename, g.id id, g.date date,
            g.category category, g.imagetext imagetext,
            DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
            DATE_FORMAT(g.date,'%H:%i') as time,
            c.imageID imageID, c.date date
            FROM $TableName g, GalleryComments c
            WHERE g.username='".$username."'
            AND c.imageID=g.id
            AND c.imageID > 0
            GROUP BY g.id
            ORDER BY c.date DESC, g.date DESC";

middleman666

thanks for the reply, the two order suggestions didn't seem to make a difference except putting them in order of image date.

The second one works if I take out the GROUP BY, but is there a way of doing it and leaving the GROUP BY in?

Thanks.

bogu

I have a few question for u:

g.username='".$username."'

This means that the image have comments?

g.id

This is autoincrement?

c.imageID

This is autoincrement or is the field that links with Gallery table?

Lars_Berg

remove the group by clause

middleman666

bogu wrote:
I have a few question for u:
g.username='".$username."'
This means that the image have comments?
g.id
This is autoincrement?
c.imageID
This is autoincrement or is the field that links with Gallery table?

Hi Bogu,
Yes the images have comments, these are held in GalleryComments table.

Yes, g.id is autoincrement.

No, c.imageID is not autoincrement - this number is the g.id of the image being commented on.

Lars Berg wrote:
remove the group by clause

Hi Lars,
the group by clause needs to be there so the same image is not displayed more than once so if an image has more than one comment, it groups those comments together so I am left with reading out the image with text under it saying 'comments (NUMBER OF COMMENTS)'.

Lars_Berg

SELECT g.filename, 
       g.id, 
       g.date,
       g.category,
       g.imagetext,
       DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
       DATE_FORMAT(g.date,'%H:%i') as time,
       (select max(date)
           from galleryComments c
          where c.imageID=g.id) as commentDate
      FROM $TableName g
     WHERE g.username='".$username."'
       and g.imageid > 0
     ORDER BY commentDate DESC

Your group by clause does not make sense, you can't tell which record you get from the comments table if there are multiple comments for an image.

middleman666

Lars Berg wrote:
SELECT g.filename, 
       g.id, 
       g.date,
       g.category,
       g.imagetext,
       DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
       DATE_FORMAT(g.date,'%H:%i') as time,
       (select max(date)
           from GalleryComments c
          where c.imageID=g.id) as commentDate
      FROM $TableName g
     WHERE g.username='".$username."'
       and c.imageID > 0
     ORDER BY commentDate DESC
EDIT: changed gallery to Gallery and g.imageid to c.imageID

Your group by clause does not make sense, you can't tell which record you get from the comments table if there are multiple comments for an image.

Thanks for the reply Lars.
I see what you mean about the 'group by' now and I get what your trying to do with the example code you've given, but it doesn't seem to find any results.

I also tried the following to match my initial coding, but include the (select):

	$query = "SELECT g.filename filename, g.id id, g.date date,
              g.category category, g.imagetext imagetext, 
              DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
              DATE_FORMAT(g.date,'%H:%i') as time,
             (select c.imageID imageID, c.date date, max(c.date)
              from GalleryComments c where c.imageID=g.id) as commentDate
              FROM $TableName g
              WHERE g.username='".$username."' and c.imageID > 0
              ORDER BY commentDate DESC";

That also returns no results.

Anyone know where it's going wrong?

Thanks.

Roger_Ramjet

The simplest fix for all this starts with the table design: if the comments table has an autoinc primary key then the sort order of that key is also the date order of the comments.

"SELECT  imageID, MAX(cid) AS cid FROM GalleryComments GROUP BY imageID ORDER BY cid DESC"

That will give you what you need to solve this problem; use it as a sub-query where ever you want in your query. My personal style of writing sql is different from yours so I would do it like this

"SELECT g.filename, g.id, g.date, g.category, g.imagetext,
       DATE_FORMAT(g.date,'%a, %D %M, %Y') as readdate,
       DATE_FORMAT(g.date,'%H:%i') as time,
       c.cid
FROM ".$TableName." AS g
INNER JOIN (SELECT  imageID, MAX(cid) AS cid FROM GalleryComments GROUP BY imageID) AS c
   ON g.id=c.imageID
WHERE g.username='".$username."'
ORDER BY cid DESC"

middleman666

Hey Roger, thanks for the reply.
I tried it out, but with no luck, I've been trying to tweak it for about an hour now changing cid to id or c.id etc. and I've been trying to use previous code examples merged with your one.

I just keep getting this:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

I don't see where its going wrong. I've echo'd the query and the $TableName and $username are fine. I've also tried using this echo with PHPMyAdmin to manualy query the database, but I just get 'page cannot be found' - not sure if this is the same for everyone, but my hosting normaly does this when theres something wrong with the query or no results are found.

Roger_Ramjet

What version of MySQL is it?

middleman666

My hosting says: MySQL 4.x.x-standard

Roger_Ramjet

Well, sub queries are not supported before 4.1 so find out what the x.x is.#

PS, I used cid as an example of an autoinc primary key on the GalleryComments table. If no such coilumn exists then create one. If it does exist then replace cid with whatever it is called.

middleman666

ok, I took a longer look to find the actual version number, but couldn't find it so I've opened a support ticket so they'll hopefully let me know soon.

cid - yea, I thought that was what you did, I've tried changing it to my actual autoinc which was just id, but had no luck.

Is there a way to do this without the use of sub-queries?

Thanks for the help.

EDIT: sorry, just found version on phpmyadmin : MySQL client version: 4.1.10

Roger_Ramjet

OK, crash course in query debugging.

First run the subquery as a query on its own. Does it return the expected results?

middleman666

$query = "SELECT imageID, MAX(id) AS id FROM GalleryComments GROUP BY imageID ORDER BY id DESC";

That selects the max id from GalleryComments and groups the id's by imageID and orders by id DESC.
So yes, the expected results are there.

Roger_Ramjet

Still need to confirm the SERVER version - don't trust phpmyadmin.

Simple test, run this query

$query = "SELECT * FROM (SELECT imageID, MAX(id) AS id FROM GalleryComments GROUP BY imageID ORDER BY id DESC) AS t";

If that returns the same results as the single query then it is 4.1 + and subqueries are supported.

middleman666

that found nothing...
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

on the phpmyadmin home page it has:
Server version: 4.0.27-standard

🙁 I'm guessing that means thats why it won't work and that I now hate my host more than I already do...

Sorry this has wasted your time and everyone else thats replied to it.

Is there a way round it?

Roger_Ramjet

Yes - move hosts. Seriously, any host who is still using less than 4.1 deserves to loose all their customers, sheer laziness not to have upgraded after 3 years n more. 4.1 is just too useful to not use, of course 5.1 has even greater advantages.

If you can not move hosting then writing this query without subqueries becomes problematic. It usually can be done, but it's a pain in the ass. Give me some time to think on it.

The other option is 2 queries: 1 to create a work table or memory table from that subquery that the 2nd can then use in a simple join.

Personally, I suggest you just go find a new host and get into php 5/mysql 5.1 - there are some great hosting deals around now as the market has become over supplied and very competitive (eg $5-$10 per month for unlimited web space, domain names and databases)

middleman666

I'm currently with:
http://nomonthlyfees.com

They were a pain to start off with, but then became less trouble and as I've never had to use sub queries, I was quite happy to renew my hosting for another year in Feb... now I wish I hadn't.

I have however been looking into new hosts recently and I've found this one:
http://www.dreamhost.com/hosting.html
Specifically the 'Crazy Domain Insane!' package. They have mysql 5, and the space and bandwidth increase each week.

Trouble is my lack of money and the site im currently building is for a project whos deadline is coming soon so I don't really want to be risking the change of host at the moment. I definatly won't be going back to NMF's next year tho.

It's not to much of a problem if this can't be done without sub-queries, its more one of those things that would be nice to have.