Jump to content

[SOLVED] problem with 'group by' aspect of sql statement!


debuitls

Recommended Posts

 

Hi all,

 

This is an extension of a thread below which ran yesterday in php coding forum!

 

But I dont expect anyone to read through the 27 entries so just gonna summarise here and see if anyone with a fresh pair of eyes today can make any suggestions!

 

Bascially I have two tables bid table and proposal table.

 

I'm trying to print to screen all the proposals made by a particular user!

 

The problem is that only those proposals that have bids made on them are printing to screen.

 

So for example if four proposals are made by a user, I want all four to be printed, however currently if only two of them have bids on them there the only ones that are getting printed.

 

I'm pretty sure its something to do with the group by statement as when I remove it, it seems to work fine, but I consequently lose the critical "count bids" info.

 

Anyway here's the code.

 

SELECT proposal . * , SYSDATE( ) , GROUP_CONCAT( bid.proposalid ) AS 'bids', TIME_FORMAT( TIMEDIFF( tomorrowtime, SYSDATE( ) ) , '%H hours, %i minutes' ) AS timeleft
FROM proposal
LEFT OUTER JOIN bid ON proposal.proposalid = bid.proposalid
WHERE NOW( ) < proposal.tomorrowtime
AND proposal.username = '$username'
GROUP BY bid.proposalid

 

Been struggling with this for a while!

 

If you think of anything please let me know!

Link to comment
Share on other sites

Thanks artacus for getting back to me!

 

I saw my mistake last night and altered and it worked fine! (Should have marked this resolved)

 

As you said I was grouping by bid.proposalid when I should have been grouping by proposal.proposalid.

 

I was using GROUP_CONCAT( bid.proposalid )

 

and then then echoing

 

count(",",explode($row['bids']));

 

I posted this is in mysql forum also but no one got back to me!

 

Anyway, thanks very much for your help!

Link to comment
Share on other sites

I was using GROUP_CONCAT( bid.proposalid ) and then then echoing...

 

I figured that was what you were doing. That's just making more work for mysql and php. Just do it in sql like so:

 

COUNT( bid.proposalid ) AS bids

 

And don't sweat posting here. You wouldn't have gotten an answer from me as I never bother with the mysql forum. There's too many numbskulls asking PHP questions there because they don't understand the difference between PHP and MySQL.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.