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.