[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

mysql php calculations - 1's and 0's or yes and no?

I want to do some calculations based on a users input of yes/no answers.  For example, what percentage of yes answers for the month of September, or the year 2006.  What's the best way to do this in a mysql database with php pages?  Should i store the information as "1" and "0" instead of "yes" and "no"?  Will that make calculations easier on the database/php scripts?  Please point me in the right direction, thanks!

I found this to count them, just don't know where to go from here:
select count(*) from tablename where answer = " Yes";
select count(*) from tablename where answer = " No";
0
bschwarting
Asked:
bschwarting
  • 14
  • 9
  • 4
  • +1
8 Solutions
 
nizsmoDeveloperCommented:
You should store them as simple boolean values, as you do not require them to be text or varchars. essentially what you are doing (checking for "yes" or "No") is exactly the same as if you store them as boolean values and checking them like this:

select count(*) from tablename where answer = 1;
select count(*) from tablename where answer = 0;

But the difference is it will probably be faster, but more importantly be more database efficient in terms of space used.
0
 
bschwartingAuthor Commented:
ok, i'll change it up to 1's and 0's

what is the best way to do a calculation on those numbers?  take September for example.
0
 
bschwartingAuthor Commented:
ok, after i changed it to 1's and 0's, now I'm trying to echo it back out without any success.

here is what I'm trying:
<?php if $answer = '1' echo 'Yes' else echo 'No' ?>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nizsmoDeveloperCommented:
<?php if $answer = '1' echo 'Yes' else echo 'No' ?>

change this to:

<?php if ($answer == '1') echo 'Yes'; else echo 'No'; ?>

you forgot the second equals sign. Also the brackets and 2 semi-colons.
0
 
fiboCommented:
Just a trick for this = trap in which we all fall frequently: reverse variable = constant to
constant = variable.
This will create an  error message which will alert you of the error, instead of keeping silent with another meaning than the intended one.

 '1' = $answer
would have been spotted at once, and
'1' == $answer does work as intended
0
 
karlwilburCommented:
Here's a short cut for the echo:
<?php echo ($answer == '1'?'Yes':'No'); ?>

or if short tags enabled:
<?=$answer == '1'?'Yes':'No'?>

As for calculations; Counts can be done like:
select count(*) from tablename where answer = 1 && dateAnswered >= "2007-09-01" && dateAnswered < "2007-10-01";
select count(*) from tablename where answer = 0 && dateAnswered >= "2007-09-01" && dateAnswered < "2007-10-01";

What other calculations did you have in mind?
0
 
bschwartingAuthor Commented:
hmmm, can't seem to get this to work.  when i put that in, i get blanks in the table on the php page.  i wonder if it has to do with my variable $answers.  i'm new to php, so explain this.

when you do a select statement to the database, is each column then automatically a variable?

here is the code, and $answers is never declared anywhere, but it works when the loop code kicks in.

$sql1 = "SELECT date, timesubmit, answer FROM tablename WHERE date_sub(CURDATE(), INTERVAL 1 DAY) <= date order by date desc";
$result = mysql_query($sql1) or die ("Error in query: $sql1. ".mysql_error());

if (mysql_num_rows($result) > 0) {
     echo "<table>";
          echo "<tr>";
          echo "<td width=100><b><center>Devotion Date</td>";
          echo "<td width=60><b><center>Answer</td>";
          echo "<td width=100><b><center>Time Submitted</td>";
          echo "</tr>";
     echo "</table>";
     while(list($date,$timesubmit,$answer)  = mysql_fetch_row($result)) {
     echo "<table cellpadding=1 cellspacing=0 border=1>";
          echo "<tr>";
          echo "<td width=100><center>$date</td>";
          echo "<td width=60><center>$answer</td>";
          echo "<td width=100><center>$timesubmit</td>";
          echo "</tr>";
     }
     echo "</table>";
}
else {
     echo "<br><br>None found.";

}
0
 
bschwartingAuthor Commented:
karlwilbur, i want to find out percentages basically.

how many yes answers in September (58% of the time in September you said yes, etc...)
same for the year, like 2006

so i would have to count all they yes answers, then divide by the total days in the month or year (365)
0
 
karlwilburCommented:
At first glance it appears that your code should work. However, I do it a little differently:

 while($row  = mysql_fetch_assoc($result)) {
     echo "<table cellpadding=1 cellspacing=0 border=1>";
          echo "<tr>";
          echo "<td width=100><center>$row['date']</td>";
          echo "<td width=60><center>$row['answer']</td>";
          echo "<td width=100><center>$row['timesubmit']</td>";
          echo "</tr>";
     }

It could be that you are not matching anything with your query. What datatype is the date field? Please give an example of a value in it.

0
 
karlwilburCommented:


$start_date = '2007-09-01';
$end_date = '2007-10-01';
$query = "select count(answer) as totalAnswers, sum(answer) as totalYes from tablename where dateAnswered >= '".$start_date."' && dateAnswered < '".$end_date."';
$result = mysql_exec($query) || die ("Error: ".mysql_error()."\n<br />\nQuery:".$query);

while($row  = mysql_fetch_assoc($result)) {
     $percentage = $row['totalYes'] / $row['totalAnswers'];  ?>  
     <table cellpadding=1 cellspacing=0 border=1>
          <tr>
               <td width=100><center><?=$percentage?></td>
         </tr>
     <?php
}
0
 
karlwilburCommented:
Opps, that was total answers not total days...but you get the idea right?
0
 
bschwartingAuthor Commented:
datatype is now set to integer.  i changed it from tinytext.  i now have "1" and "0" as values.  before i had "yes" and "no"
0
 
karlwilburCommented:
The date field is an integer?

You have:
$sql1 = "SELECT date, timesubmit, answer FROM tablename WHERE date_sub(CURDATE(), INTERVAL 1 DAY) <= date order by date desc";

if date is a integer, is it formated as "YYYYMMDD"? if so I think that you might need to use a UNIX_TIMESTAMP() function with zero padding.
0
 
bschwartingAuthor Commented:
answer is an integer

that's the one we are working with.
0
 
karlwilburCommented:
Oh, I see. Then when you say that you "get blanks in the table" do you mean that instead of "0" you are getting nothing for the answer but you age getting values for the others? If so, could it be that instead of '0' you have 'NULL'?

Do you have direct access to the database? like for example through MySQL Query Browser or a mysql terminal client? if so, try to execute the query directly against the database and see what you get.
0
 
bschwartingAuthor Commented:
sorry for falling off the earth.  been super busy.

i got the yes/no issue resolved with this:

if ($answer == '1') {
($answer2 = 'Yes');
}
else {
($answer2 = 'No');
}
0
 
bschwartingAuthor Commented:
now i just need to get the calculations to display.  here is what i have:

<?php
$countyes = "select count(answer) as zzz from tablename where answer = '1'";
$result2 = mysql_query($countyes) or die ("Error in query: $countyes. ".mysql_error());
?>
October = <?php echo $result2; ?>

It doesn't echo the count?  What am i doing wrong?

It echo's this - Resource id #464
0
 
fiboCommented:
<?php
$countyes = "select count(answer) as zzz from tablename where answer = '1'";
$result2 = mysql_query($countyes) or die ("Error in query: $countyes. ".mysql_error());
// $result2 is now the resource for the query connexion
$result0 =  mysql_num_rows($result2)
?>
October = <?php echo $result0; ?>

0
 
fiboCommented:
Oops missing ;

<?php
$countyes = "select count(answer) as zzz from tablename where answer = '1'";
$result2 = mysql_query($countyes) or die ("Error in query: $countyes. ".mysql_error());
// $result2 is now the resource for the query connexion
$result0 =  mysql_num_rows($result2);
?>
October = <?php echo $result0; ?>

0
 
bschwartingAuthor Commented:
ok, i tried that, but i get a count of 1 for some reason.

when i run the same sql on mysql via ssh, i get 15 as zzz
0
 
bschwartingAuthor Commented:
mysql> select count(answer) as zzz from tablename where answer = '1';
+-----+
| zzz |
+-----+
|  15 |
+-----+
1 row in set (0.00 sec)
0
 
karlwilburCommented:
I don't think that mysql_num_rows() will work since you are doing a count() you'll only get on row.

$query = "select count(answer) as intCount from tablename where answer = '1'";
$rh = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
$count =  mysql_result($rh, 0, 'intCount');
?>
October = <?=$count?>
0
 
bschwartingAuthor Commented:
karlwilbur,

you hit the nail on the head, i removed the count from the SQL query and got 15!  sweet.

i didn't try your option, which is more efficient on the database?
0
 
karlwilburCommented:
Sorry, bschwarting. I didn't see your last two posts before I posted my last.

mysql_num_rows() returns the number of rows in your result set. your post ID: 20157299 shows that there is only one row returned. You need to grab that value from the result set not the number of rows. There are several PHP functions which you could use for that, but I'd recommend that you use mysql_result as shown in my last post since it is the most straight forward for getting a single value from a resultset.

Here's the obligatory RTFM:
http://us3.php.net/manual/en/function.mysql-result.php
0
 
karlwilburCommented:
I like the count, because the whole resultset is not returned to PHP, just the count. One row vs several. Obviously using the count() function of MySQL is less efficifent when just considering the database processes because it has to do extra work, but all in all I'd go with the count().
0
 
bschwartingAuthor Commented:
ok, thanks, only thing i don't understand is the ",0" in this:

$count =  mysql_result($rh, 0, 'intCount');
0
 
bschwartingAuthor Commented:
or "0,"
0
 
fiboCommented:
yes, you're right, I just got lost into counting!

http://www.php.net/function.mysql-result

$count =  mysql_result($rh, 0, 'intCount'); // row 0, column intCount
could also be
$count =  mysql_result($rh, 0); // row 0, first column
0
 
bschwartingAuthor Commented:
thanks all!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 14
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now