stumbling block

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

stumbling block

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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
Last edited by Benjamin on Wed Jan 19, 2011 4:17 am, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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];
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post by social_experiment »

Code: Select all

<?php 
// add this
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
  ?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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?
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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];
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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 />";
}
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
twistajones53
Forum Newbie
Posts: 8
Joined: Tue Jan 18, 2011 4:05 pm

Re: stumbling block

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: stumbling block

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply