stumbling block
Moderator: General Moderators
-
twistajones53
- Forum Newbie
- Posts: 8
- Joined: Tue Jan 18, 2011 4:05 pm
stumbling block
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.
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.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
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
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
TIA
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.
Reason: Added [syntax=php] tags.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
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];
?>“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
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];
?>
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];
?>
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
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
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?
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
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];
?>
<?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];
?>
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
Try this
Hth
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;
?>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.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?
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
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 />";
}
?>
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 />";
}
?>
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
Yestwistajones53 wrote:is it possible to just sum one column and only display the sum?
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
)
*/
?>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
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.
just for formatting/display purposes
thanks again - i have created over 10 tables since you got me past this block.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: stumbling block
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.
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