How to display COUNT,MIN,MAX queries?

Anon

Hi,

I badly need some help. I already got some ideas from you but none of them worked.

I just want to display the result of some complicated queries using COUNT,MIN,MAX.

Eg.: SELECT MIN(COLUMN1-COLUMN2) FROM TABLE ;

That's OK. It works in MySQL but now I want it to appear on my page.

I used the following code:

<?
$dbhost = MYHOST ;
$dbuser = MYUSER ;
$dbpass = MYPASSWORD ;
$db = mysql_connect($dbhost,$dbuser,$dbpass) or die("Error while connecting!") ;
$dbname = MYDBASE ;
mysql_select_db($dbname,$db) ;
$query = "select min(COLUMN1) from TABLE" ;
$result = mysql_query($query,$db) ;
list(count) = mysql_fetch_row($result) ;
echo $count ;
?>

It doesn't work and I don't have an idea. Can you please help me?

Thanks, Akos

Anon

try something like this

$query = "SELECT MIN(COLUMN1-COLUMN2) AS RESULTCOLUMN FROM TABLE";

$result = mysql_query($query,$db) ;
while ) = mysql_fetch_row($result) ;
echo $count ;
?>

Anon

Hi Eric,

Thanks for the answer. I tried what you wrote but it didn't work. I think you left out some things. So I modified your code:

$result = mysql_query($query,$db) ;
while ($count = mysql_fetch_row($result)
{
echo $count ;
}

But it's still the same. Can you be more specific about your solution?

Thanks, Akos

Anon

But it's still the same

However, you have yet to tell us exactly what "the same" is.

Saying "This doesn't work" is completely uninformative, since there
are 4 separate calls to 4 different mysql interface functions, any
one of which can fail (and in more than one way, too). So whenever
you find yourself starting to say "this doesn't work", remember to
instead describe what it is you are and aren't seeing.

I do see one error in your source code:

  list(count) = mysql_fetch_row($result) ;

That first variable needs to be $count.

Anon

Hi,

The problem is not solved yet. By "this doesn't work" I mean that it doesn't display anything. I don't think there is a syntax error because when I make this query in MySQL, it gives the result back (eg.: the number of rows). So I think I just can't make PHP to get the result of the query displayed.

The error message I get is the following:

Warning: Supplied argument is not a valid MySQL result resource in /home/davida/public_html/stat.php on line 17

Can you give me anything that works? I mean a code that is displaying COUNT(), MIN() or MX() results?

Thanks in advance.

Bye, Akos

Anon

if you put AS RESULTCOLUMN to your select statement the MIN(COLUMN1-COLUMN2) will be returned in RESULTCOLUMN.. maybe this works..

$query = "SELECT MIN(COLUMN1-COLUMN2) AS RESULTCOLUMN FROM TABLE";

$result = mysql_query($query,$db) ;
$count = mysql_fetch_array($result) ;
echo $count["resultcolumn"];
?>

Anon

Thanks Eric,

It's beautiful!!! Finally, it works. Now another question. I hope you know the answer to this question as well. There are two TIMESTAMP(14) columns in a table. I can display them with the help of DATE_FORMAT() in the following way:

SELECT DATE_FORMAT(COLUMNNAME,'%Y-%m-%d %T') AS DATE_TIME FROM TABLE ;

That's fine. Now you showed me how to get the minimum value of a column.

Now, if I want to display the least difference between the two TIMESTAMP columns what shall I do:

SELECT DATE_FORMAT(MIN(COLUMNNAME),'%Y-%m-%d %T') AS MIN_DATE_TIME FROM TABLE ;

It didn't give an error message but it always returned NULL. What's the problem? Can you tell me?

Thanks, Akos