2

The code:

 $review = mysql_query("SELECT conceptID, MIN(nextReview) FROM userconcepts WHERE userID='$userID'");
 $nrows = mysql_num_rows($review);
 echo "$nrows<br />\n";

The query works when the table has such entries and returns the correct column values. However, when the table is empty, as I can confirm right now in HeidiSQL, mysql_num_rows still returns 1, but the column values are empty. (The problem still remains if the table has other values for different userIDs).

I expect this query to return the empty set sometimes during normal operations, and I want to take action based on the existence of a result, but I also want to use the result if it exists. Any idea why this code is not working as I expect it to work (I expect it to return 0 if the table is empty)?

user2129992
  • 21
  • 1
  • 2
  • [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. – Kermit Mar 03 '13 at 23:48
  • 1
    @AarolamaBluenk How would that solve the user's problem at all? – aaaaaa123456789 Mar 03 '13 at 23:48
  • 2
    @aaaaaa123456789 That’s why it’s not an answer. – poke Mar 03 '13 at 23:49
  • http://stackoverflow.com/questions/7767684/proper-way-to-ask-if-mysql-num-rows-in-php – Jim Black Mar 03 '13 at 23:52

2 Answers2

4

First of all, the query has a very simple problem: you're showing the conceptID field, but not grouping by it. If you want to show a field on a SELECT that uses aggregate functions, you should show it; not doing so is an error, and will make many engines not execute your query.

That aside, whenever you have an aggregate function, and you don't group by anything (i.e., don't add a GROUP BY clause), the result is one row. Regardless of the amount of rows in the table.

aaaaaa123456789
  • 5,541
  • 1
  • 20
  • 33
  • I don't want to find the minimum for each conceptID. I don't want to find the minimum for each userID. I just want the conceptID and nextReview from the row with the minimum nextReview for a fixed userID. Could you explain this error in greater detail? What would be the appropriate code to use? – user2129992 Mar 03 '13 at 23:59
  • The query you posted is definitely not the one that does that. You should get the minimum with one query, and use another one to find the conceptID where nextReview is that value. You could do that in a single query, but it's more trouble than it's worth. – aaaaaa123456789 Mar 04 '13 at 00:01
  • The code works when there's an entry. Why search twice? Can you elaborate on how that would be better? – user2129992 Mar 04 '13 at 00:04
  • Because it's poor style at the very least. Either way, checking the field with the minimum for nulls should tell you if there was a value or not. – aaaaaa123456789 Mar 04 '13 at 00:08
  • Making two search queries instead of one seems like poor style to me. Omitting "GROUP BY" just means the query groups by all the rows, which is exactly what I want. Yes, I check empty($nextReview), but I wanted to know why I wasn't able to use mysql_num_rows, which seems to be the agreed-upon best practice. Now I know that, for some strange reason, aggregate functions always return at least one row. – user2129992 Mar 04 '13 at 00:23
  • If your table has more than 1 row for the same user but has different conceptid then it might return a random one. If every row for the same user always have the same conceptid, then there is a problem with the database design. – iWantSimpleLife Mar 04 '13 at 01:05
  • Sorry. Chrome browser went hay wire posted twice. – iWantSimpleLife Mar 04 '13 at 01:05
0

The reason why is because when a SQL engine executes a query with only aggregation functions, then it returns one row. So:

select count(*)
from table
where 1 = 2

is going to return 1 row with the value 0. This is the way that all SQL engines work.

Your query is a little different:

select conceptID, MIN(nextReview)
FROM userconcepts
WHERE userID='$userID'"

In most SQL dialects, you would get an error of the from "conceptID not in group by clause" or something like that. That is, the query would have a syntax error.

MySQL supports this. It will return the minimum value of nextReview (from the rows that meet the where condition) along with an arbitrary value of conceptID (from those same rows). In this case, there are no rows, so the values will be set to NULL.

Perhaps, you want one row per conceptId. That query would be:

select conceptID, MIN(nextReview)
FROM userconcepts
WHERE userID='$userID'
group by conceptId
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786