Page 1 of 1

stumbling block

Posted: Tue Jan 18, 2011 4:10 pm
by twistajones53
example table
sport, decision
nba, 1

1 is for win
0 is for loss

i would like to end up with just a sum of the number of times a 1 is entered... or total wins
i would also like to end up with just a sum of the number of times a 0 is entered... or total losses

by each sport in the entire database

TIA
please include entire php code I would need to use (besides connecting to my db)

i would like to run each one separately inside a table that has already been created.

Re: stumbling block

Posted: Tue Jan 18, 2011 5:14 pm
by social_experiment
Paste some of the code that you have already created, it helps you more if you don't get spoonfed the code

Re: stumbling block

Posted: Wed Jan 19, 2011 4:04 am
by twistajones53
this is all i have. i have no clue how to print this out. am i even on the right track?

again, basically table is setup (i have used 1 for a win and 0 for losses)
sport | decision | etc.
nba | 1| etc.

i would like to just print out the number of wins in the database where the sport is nba

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("XXXX", "XXXXXX", "XXXXX") or die(mysql_error());
mysql_select_db("historicdb2") or die(mysql_error());
 	$query= "SELECT COUNT(*) FROM results WHERE decision LIKE '1' AND sport LIKE 'nba'";
 	$row = mysql_fetch_assoc($result); 
?> 

TIA

Re: stumbling block

Posted: Wed Jan 19, 2011 10:39 am
by social_experiment
twistajones53 wrote:i would like to just print out the number of wins in the database where the sport is nba

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("XXXX", "XXXXXX", "XXXXX") or die(mysql_error());
mysql_select_db("historicdb2") or die(mysql_error());
        $query= "SELECT COUNT(*) FROM results WHERE decision LIKE '1' AND sport LIKE 'nba'";
        $row = mysql_fetch_assoc($result); 
        // 
        echo $row[0];
?>
Hth

Re: stumbling block

Posted: Wed Jan 19, 2011 1:01 pm
by twistajones53
am i missing something? i don't get any errors, but nothing shows on the site when loaded

i have other php scripts on this page functioning properly and where i placed this is just blank

<?php
// Make a MySQL Connection
mysql_connect("x", "x", "x") or die(mysql_error());
mysql_select_db("historicdb2") or die(mysql_error());
$query= "SELECT COUNT(*) FROM results WHERE decision LIKE '1' AND sport LIKE 'nba'";
$row = mysql_fetch_assoc($result);
//
echo $row[0];
?>

Re: stumbling block

Posted: Wed Jan 19, 2011 3:58 pm
by social_experiment

Code: Select all

<?php 
// add this
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
  ?>

Re: stumbling block

Posted: Wed Jan 19, 2011 4:10 pm
by twistajones53
just tried and still nothing shows up on the site

this is the full code if i understood your suggestion correctly

<?php
// Make a MySQL Connection
mysql_connect("z", "z2", "z") or die(mysql_error());
mysql_select_db("historicdb2") or die(mysql_error());
$query= "SELECT COUNT(*) FROM results WHERE decision LIKE '1' AND sport LIKE 'nba'";
$row = mysql_fetch_assoc($result);
//
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
?>

the field we are counting is called 'decision' and i have it setup as field type 'integer' is that causing a problem. should it be something else?

Re: stumbling block

Posted: Wed Jan 19, 2011 4:13 pm
by twistajones53
also just tried this code adding back in your echo statement and still no result or error on the site

<?php
// Make a MySQL Connection
mysql_connect("x", "x", "x") or die(mysql_error());
mysql_select_db("historicdb2") or die(mysql_error());
$query= "SELECT COUNT(*) FROM results WHERE decision LIKE '1' AND sport LIKE 'nba'";
$row = mysql_fetch_assoc($result);
//
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row[0];
?>

Re: stumbling block

Posted: Wed Jan 19, 2011 11:54 pm
by social_experiment
Try this

Code: Select all

<?php
$query= "SELECT COUNT(decision) FROM results WHERE decision = '1' AND sport LIKE 'nba'";
$sql = mysql_query($query);
$countArray = mysql_fetch_array($sql);
$rows = $countArray[0];
//
echo $rows;
?>
twistajones53 wrote:the field we are counting is called 'decision' and i have it setup as field type 'integer' is that causing a problem. should it be something else?
No it's not the field that is causing the problem. I have done this using mysql_fetch_array() and the count is stored within the first element of the array.
Hth

Re: stumbling block

Posted: Wed Jan 19, 2011 11:59 pm
by twistajones53
BANG. that worked. i would kiss you. cant thank you enough

spit out the value 20. and when doing search thru phpmyadmin that is the exact count.

i have a follow up question

i currently have a working code that sums all of a value for each column (code pasted below)

so it shows all nba games added together, but seems to be requiring a GROUP BY and i just want to have that value entered into a cell of an existing table

is it possible to just sum one column and only display the sum? without fields names or group by function

<?php
$query = "SELECT sport, SUM(units) FROM results GROUP BY sport";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['sport']. " = ". $row['SUM(units)'];
echo "<br />";
}
?>

Re: stumbling block

Posted: Thu Jan 20, 2011 1:33 am
by social_experiment
twistajones53 wrote:is it possible to just sum one column and only display the sum?
Yes

Code: Select all

<?php
$query = "SELECT SUM(price) FROM table";
$sql = mysql_query($query) or die(mysql_error());
	
$array = mysql_fetch_array($sql);
$sum = $array[0];
/* 
i dumped the result of $array using print_r and it looks like this
incase you wonder why $array[0] is used

Array
(
    [0] => 118703667
    [SUM(price)] => 118703667
)
*/
?>
You use mysql_fetch_array() and the value you want (the sum of the column) is placed in the first element of the array which can be accessed by using the index of the specific element which is 0 for the first element.
Hth

Re: stumbling block

Posted: Thu Jan 20, 2011 1:54 am
by twistajones53
is there a way you know of to force the value to have 2 decimal places even if the value is 1.00

just for formatting/display purposes

thanks again - i have created over 10 tables since you got me past this block.

Re: stumbling block

Posted: Thu Jan 20, 2011 10:38 am
by social_experiment
You can take a look at number_format().

From the php manual:

string number_format ( float $number [, int $decimals ] )
string number_format ( float $number , int $decimals , string $dec_point , string $thousands_sep )
This function accepts either one, two, or four parameters (not three):

If only one parameter is given, number will be formatted without decimals, but with a comma (",") between every group of thousands.

If two parameters are given, number will be formatted with decimals decimals with a dot (".") in front, and a comma (",") between every group of thousands.

If all four parameters are given, number will be formatted with decimals decimals, dec_point instead of a dot (".") before the decimals and thousands_sep instead of a comma (",") between every group of thousands.