RE: [PHP] Get the lowest value out of different fields
Frank Keessen mailto:[EMAIL PROTECTED] on Wednesday, September 03, 2003 12:14 PM said: I hope you can help me with this: I hope so too! TEST fieldnameValue's testid 1 testf1 3 testf2 4 testf3 0 testf4 2 testf5 0 (so this is one record!) Waaahh? Please write out your table definition in a standard format. Like this: (Doesn't have to be all fancy with the fancy characters, but it should be horizontal and not vertical at the least.) ++++++ | testid | testf1 | testf2 | testf4 | testf5 | ++++++ | 1 | 3 | 4 | 0 | 2 | ++++++ See how much easier that is to read? (And you don't have to label the fieldname's or the value's.) I want to display the lowest value, except 0.. So the SQL statement will be SELECT testf1, testf2, testf3, testf4, testf5 FROM test where testid='1' . Your query looks fine. I can't figure out if i can do this into a SQL statement or put it in an Array and sort it.. What do you mean you can't figure out if i can do this into a SQL statement? You already did. Please help! I'd really like to but I'm not sure what you want to do! Try explaining it again please. Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Get the lowest value out of different fields
[snip] I have a tabel in my database like this TEST fieldnameValue's testid 1 testf1 3 testf2 4 testf3 0 testf4 2 testf5 0 (so this is one record!) I want to display the lowest value, except 0.. So the SQL statement will be SELECT testf1, testf2, testf3, testf4, testf5 FROM test where testid='1' . [/snip] Use http://us2.php.net/min -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Get the lowest value out of different fields
Hi All, I hope you can help me with this: I have a tabel in my database like this TEST fieldnameValue's testid 1 testf1 3 testf2 4 testf3 0 testf4 2 testf5 0 (so this is one record!) I want to display the lowest value, except 0.. So the SQL statement will be SELECT testf1, testf2, testf3, testf4, testf5 FROM test where testid='1' . I can't figure out if i can do this into a SQL statement or put it in an Array and sort it.. Please help! If you really want to do this in an SQL statement and you're using mySQL you can nest IFs, but it's really ugly. For example... SELECT IF(IF(IF(IF(testf1 testf2 || testf2 = 0, testf1,testf2) testf3 || testf3 = 0,IF(testf1 testf2, testf1,testf2), testf3) testf4 || testf4 = 0, IF(IF(testf1 testf2 || testf2 = 0, testf1,testf2) testf3 || testf3 = 0,IF(testf1 testf2, testf1,testf2), testf3), testf4) testf5 || testf5 = 0,IF(IF(IF(testf1 testf2 || testf2 = 0, testf1,testf2) testf3 || testf3 = 0,IF(testf1 testf2, testf1,testf2), testf3) testf4 || testf4 = 0, IF(IF(testf1 testf2 || testf2 = 0, testf1,testf2) testf3 || testf3 = 0,IF(testf1 testf2, testf1,testf2), testf3), testf4),testf5) from TEST where testid =1; I didn't test that, and I really wouldn't use it. Maybe there's a prettier way, check the function refrence for the DB you are using. I'd just pull the results into an array and use PHP to figure it out. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Get the lowest value out of different fields
Hi all,
Thanks for the quick response.. Did a little bit more investigation: (and
made additions to my table)
++++++
| testid | testf1 | testf2 | testf4 | testf5 | frankid
++++++
| 1 | 3 | 4 | 0 | 2 |10
++++++
| 2 | 8 | 7 | 0 | 6 |10
++++++
| 3 | 3 | 4 | 0 | 2 |8
++++++
(The layout is from mr. Chris W. Parker!)
O.k. i dropped my lines below: As you can see, i'm selecting FRANKID 10 and
i want to display the lowest value (2 rows are selected)! As you can see a
stole a function from www.php.net/min, Now this is working fine for the
first record!!! BUT how can i make the function loop through the whole
result?
---
if (mysql_num_rows($result3) 0)
{
$test = mysql_fetch_array($result3);
echo minnum($test);
}
--
Whole code:
?
include(../conf/config.php);
$connection = mysql_connect($host, $user, $pass) or die (Unable to
connect!);
// select database
mysql_select_db($db) or die (Unable to select database!);
$query3 = SELECT `testf1` , `testf2` , `testf3` , `testf4` , `testf5`
, FROM `test`
WHERE frankid = '10';
$result3 = mysql_query($query3) or die (Error in query: $query3. .
mysql_error());
function minnum($numarray){
//dont use min(), it contains a bug!
$min=0;
if ( ! is_array($numarray) ) $numarray = func_get_args();
if(is_array($numarray)==true){
$min=max($numarray);
for($z=0;$zcount($numarray);$z++){
$curval=floatval($numarray[$z]);
if(($curval != 0) ($curval $min)){
$min=$curval;
}
}
}
return $min;
}
if (mysql_num_rows($result3) 0)
{
$test = mysql_fetch_array($result3);
echo minnum($test);
}
?
Regards,
Frank
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Get the lowest value out of different fields
BUT how can i make the function loop through the whole
result?
---
if (mysql_num_rows($result3) 0)
{
$test = mysql_fetch_array($result3);
echo minnum($test);
}
--
Someone will probably come up with something a little cleaner, but quick and
dirty...
if (mysql_num_rows($result3) 0)
{
while ($row = mysql_fetch_array($result3)){
$min[] = minnum($test);
}
$overall_min = minnum($min);
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Get the lowest value out of different fields
[snip]
O.k. i dropped my lines below: As you can see, i'm selecting FRANKID 10
and
i want to display the lowest value (2 rows are selected)! As you can see
a
stole a function from www.php.net/min, Now this is working fine for the
first record!!! BUT how can i make the function loop through the whole
result?
[/snip]
You answered your own questionwrap the thing in a while() loop
while($foo = mysql_fetch_object($result3)){
...wrappered items...
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Get the lowest value out of different fields
From: Frank Keessen [EMAIL PROTECTED] I hope you can help me with this: I have a tabel in my database like this TEST fieldnameValue's testid 1 testf1 3 testf2 4 testf3 0 testf4 2 testf5 0 (so this is one record!) Those are columns, right? Anytime you have columns named like that it means you have a horrible database design.You're going to be better of pulling this into PHP and finding the min() number. Here's one way to do it in SQL, but you see how complex it is with just three columns: select case when a0 and b0 and c0 then least(a,b,c) when a0 and b0 then least(a,b) when a0 and c0 then least(a,c) when b0 and c0 then least(b,c) when a0 then a when b0 then b when c0 then c end from test; ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
