Question about MySQL query

PHP
1

I’m building an application that is meant for setting appointments. There will be radio buttons within the form that will enable the user to chose one of the three nearest days available. There will only be a limited number of appointments available for each day though, so I’m setting up a counter that will be passed to the database

I need to make a query that looks for the closest available date within a database, but cross-references that with the highest number in the counter. If the counter goes up to 10, that date will be retired and the next one will be chosen.

The best that I have so far is this, it was done up within MyphpAdmin:

SELECT *
FROM `Appointments`
WHERE `ApptDate` > "2011-09-17"
AND `ApptCount` <10
LIMIT 0 , 3

which will do everything but weed out the duplicates, which in turn will allow days to come through that should be blocked off.

I’ve tried this:

SELECT * 
FROM Appointments 
WHERE ApptDate>".$TodayDate." 
AND MAX(ApptCount)<10

but I get an error stating that you can’t use the MAX and WHERE functions together. I’ve tried the HAVING function but it appeared to only give me one return.

Is there a way to use the MAX function and less than sign to get what I want, or is there another method that would work better?

2

could you show a few rows of data please?

it’s hard to visualize why you feel you have to use MAX on the appcounts

3

Say I have these three entries within the database:

ID:1
ApptDate: 2011-09-18
ApptCount: 1

ID:2
ApptDate: 2011-09-18
ApptCount: 2

ID: 3
ApptDate: 2011-09-19
ApptCount: 1

If I don’t look for the maximum ApptCount,
First off, both ID1 and ID2 would be returned so I’d have the same date returned twice when I only need it once.

Second, if the ApptCount is over ten (if ten people have scheduled appointments for that day)that date should be taken out of circulation, that’s why I need the lesser than or something comparable.

4

so in your example, all 3 sample rows meet the criteria (greater than the 17th, and appcount less than 10)

so the query should return all three, right?

5

What I’m saying is that it does return all three, but I need for it to only return each date with the highest ApptCount entry.

In other words, only the ID2 and the ID3 should be returned in the scenario I laid out.

6

try this –


SELECT t.* 
  FROM ( SELECT id
              , MAX(ApptDate) AS latest 
           FROM Appointments
          WHERE ApptDate > '2011-09-17' 
            AND ApptCount < 10
         GROUP
             BY id ) AS m
INNER
  JOIN Appointments AS t
    ON t.id = m.id
   AND t.ApptDate = m.latest 
ORDER
    BY t.ApptDate LIMIT 3
7

whoa, that’s weird – there’s a bug in the forum software, i did not actually post the same code block twice, although it looks like i did, doesn’t it?

8

I had the same issue just a few minutes ago in the JS forum.

9

I finally got it to work the way I wanted:

SELECT a.id, a.ApptDate, a.ApptCount
				FROM (
				
					SELECT ApptDate, MAX( ApptCount ) AS count
					FROM Appointments
					WHERE ApptDate > '".$TomorrowDate."'
					GROUP BY ApptDate
				) AS b
				INNER JOIN Appointments AS a ON a.ApptDate = b.ApptDate
				AND a.ApptCount = b.count
				WHERE ApptCount <10
				ORDER BY ApptDate ASC
				LIMIT 0 , 3

It’s a pretty close to what you suggested r937, I just had to tweak it a little. I also decided to use a variable for tomorrow’s date rather than today’s date.

Thanks for all of your help.