[SOLVED] Returning results that equal the least times

[soycharliente]

I have a site that tracks places that someone has eaten. I currently have a list that displays all the unique locations that he has eaten at for a specific day of the week. Right now it just shows the places in order of how many times with a limit on how many to show. They could be any numbers. I want to show all the locations that have been eaten at the least number of times (all with same number). Like, for Tuesday, if there are locations that have been eaten at 1 time, show all the places that are 1. But if the least number of times for that day is 4, then show all the locations with 4 times eaten. I hope that makes sense.

 

I really am lost for how to go about this. Code help, or even help with just explaining the theory behind what I need to write will work. I'd be glad to write the code myself if someone could help me understand how to do it.

 

Here's my current code.

<?php
$today = date("l");
$limit = 5;
$sql = "SELECT loc, COUNT(loc) AS tally FROM $table
WHERE DAYNAME(thedate)='$today'
GROUP BY loc
ORDER BY tally ASC
LIMIT $limit";
?>

 

db table setup:

id - int( PRIMARY
loc - varchar(255)
thedate - date UNIQUE
satisfaction - int(3)
price - float

 

 

[fenway]

Well, you could use a HAVING clause to make tally = whatever you want.

[gizmola]

Agree with Fenway -- in some fashion determine your lowest, so this is basically the first ordered result of the group by, then you can find all of them by adding HAVING (COUNT(*) = number you want to match. 

 

You'll get a result set of only the loc's with the same count.

[soycharliente]

The thing is that I don't know what the lowest number is. It could change based on how many times he eats at a location.

 

I looked up HAVING and came up with this, but I get an error.

 

Error: Unknown column 'tally' in 'having clause'

<?php
$query = "SELECT loc, COUNT(loc) AS tally FROM donger
WHERE DAYNAME(thedate)='$today'
GROUP BY loc
HAVING MIN(tally)";
?>

 

I changed it to this and got a different error.

 

Error: Invalid use of group function

<?php
$query = "SELECT loc, COUNT(loc) AS tally FROM donger
WHERE DAYNAME(thedate)='$today'
GROUP BY loc
HAVING MIN(COUNT(loc))";
?>

 

[fenway]

The thing is that I don't know what the lowest number is. It could change based on how many times he eats at a location.

Wait a sec... if he eats 4 times at a location, then it's 4... the MIN() won't make it less.

 

I'm confused.

[soycharliente]

Let me see if I can use a different example to explain.

 

Let's say I have a list of places with a number associated with it. The numbers can change. At any given moment, I want to query for the smallest number out there, and then return a list of all the places that have that number.

 

Now think of that number as the number of times that place has been eaten at. For all the places he's eaten, what's the smallest number (it could be 1, it could be 2, it could be 12), and return all the places that have that many times eaten there.

 

I don't want to hard code the number.

 

Does that make better sense?

[fenway]

Not really... I'm not suggesting you hard-code the number... I was confused by your suggestion to combine min and count.  min of a single number is that number.

 

Unless you mean the min of *all* places, not each palce.

[soycharliente]

Unless you mean the min of *all* places

 

I think this is what I'm trying to do.

 

Example 1:

Burger King - 2

McDonald's - 4

Wendy's - 4

Arby's - 12

Taco Bell - 2

Kroger - 3

Krystal - 3

Checkers - 5

[/td]

Output would be:

- Burger King

- Taco Bell

Example 2:

Burger King - 12

McDonald's - 14

Wendy's - 14

Arby's - 12

Taco Bell - 12

Kroger - 13

Krystal - 13

Checkers - 15

[td]

Output would be:

- Burger King

- Taco Bell

- Arby's

 

Does this help more?

[fenway]

Yes, now I see what you mean... you can't accomplish that in a single statement... you'll have to join the table back to it, and then use MIN().

[soycharliente]

JOIN the table to itself? Well gosh. Do you have a link/example that might explain this type of situation?

[fenway]

I'm a bit sleepy this morning... maybe something like this (untested):

 

SELECT loc, tallly FROM 
( SELECT loc, COUNT(loc) AS tally FROM $table
WHERE DAYNAME(thedate)='$today'
GROUP BY loc ) AS counts
GROUP BY loc
HAVING tally = MIN(tally)

 

[soycharliente]

SELECT loc, tallly FROM 
( SELECT loc, COUNT(loc) AS tally FROM $table
WHERE DAYNAME(thedate)='$today'
GROUP BY loc ) AS counts
GROUP BY loc
HAVING tally = MIN(tally)

 

That didn't work. I got the same error as before. "Invalid use of group function."

 

I restructured the database because my queries were getting a little out of hand. It made a lot of queries much, much easier. I'm still having trouble with this one, but I think that it's possibly easier now.

 

I'm using this query now with no results being returned.

SELECT * FROM lunch_loc HAVING tally=MIN(tally) ORDER BY tally, loc ASC

 

This is what the new structure looks like.

 

lunch_loc

-----------

id

loc

menu

map

tally

[/td]

[td]

lunch_data

-----------

id

pid

thedate

rating

spent

comment

 

Maybe this new structure will make it easier to accomplish what I'm trying to do. I don't want to use 2 queries if I don't have to. Obviously I could use two and get the same effect, but can anyone help?

[soycharliente]

I'm trying to use this query now with a syntax error starting at the nest select statement.

SELECT * FROM lunch_loc WHERE tally=( SELECT MIN(tally) FROM lunch_loc )

 

I ran the second select alone and got a number back. I ran the out select with the number and got the results.

 

Anyone know why they don't work together?

[fenway]

Syntax error? Waht version?

[soycharliente]

PHP Version 5.2.5

MySQL Version 5.0.18

[fenway]

PHP Version 5.2.5

MySQL Version 5.0.18

Very strange... really, a syntax error? I was guessing unsupported subqueries... you sure it's v5?  I suppose you could re-write it as a join:

 

SELECT * FROM lunch_loc AS t1
INNER JOIN ( SELECT MIN(tally) AS mintally FROM lunch_loc ) AS t2 ON ( t2.mintally = t1.tally )

[soycharliente]

Yea man. Still getting a syntax error. I called my hosting company and asked them if they had plans to upgrade. They said no. Could the version be the problem?

 

Here's the output from my phpMyAdmin interface where I'm running it directly against the db.

 

SQL query:

SELECT *

FROM lunch_loc AS t1

INNER JOIN (

SELECT MIN( tally ) AS mintally

FROM lunch_loc

) AS t2 ON ( t2.mintally = t1.tally )

 

MySQL said:

#1064 - 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 MIN(tally) AS mintally FROM lunch_loc ) AS t2 ON ( t2.mi

[soycharliente]

I'm just going to use 2 queries. This is turning out to be much more work than I intended. I don't have that many users so it's not really going to put a lot of stress on the server.

 

thanks for trying to help.

[soycharliente]

I found out what the problem was.

 

Even though my account is setup for MySQL 5.0.18, the db was configured for 4.1

When you setup the db, it gives you two options to choose from, 4.1 and 5.0

It's kind of hidden and it's been so long since I setup a db that I forgot about it. I just did a backup and restore and my db is now running 5.0

 

Sub-queries are working