Page 1 of 1

Counting rows

Posted: Sun Oct 27, 2002 5:06 pm
by billman
How can I get something like this to work?

Code: Select all

$result = mysql_query ("select * from table");
while ($row = mysql_fetch_object ($result))
{
$dscore = strtok ($row->score, "-");
$oscore = strstr ($row->score, "-");
$oscore = ereg_replace ("\-", "", $oscore);
if ($dscore > $oscore)
{
show me the money! err, rows
}
else
{
}
}
$row->score is formatted like xxx-xxx (ie: 500-300), the strok and strstr seperates them to make them their own variable. My question is: how can I find the number of rows that contain a score $dscore that is higher then $oscore? I can't (at least I think I can't) find this out through the mysql_query since it finds out what row->score is AFTER the query.

Something like:
if ($dscore > $oscore)
{
echo count ($dscore);
}

Unfortuantly that will just say "111", which is half right, since it's displaying 1 three times, and there are 3 rows that match what I'm looking for, but how can I make it display "3" instead? I'm awful with arrays, I haven't used them much before, and I suspect that's what I'm gonna be using for this :oops:

try this

Posted: Sun Oct 27, 2002 6:54 pm
by phpScott
insted of

if ($dscore > $oscore)
{
echo count ($dscore);
}


try this
before your while loop declare $dscoreTotal=0; and $oscoreTotal=;

if ($dscore > $oscore)
{
$dscoreTotal ;
}
{
$oscoreTotal
}


then at the end of your script
echo "dscores ".$dscoreTotal." orscore ".$orscoreTotal."<br>\n;

phpScott

Posted: Sun Oct 27, 2002 7:16 pm
by billman
I'm not exactly sure what that code does, it just gives me 0 (suppose to be 3) :?

Code: Select all

$dscore=0;
$oscore=0; 
while ($row = mysql_fetch_object ($result))
&#123;
$dscore = strtok ($row->score, "-");
$oscore = strstr ($row->score, "-");
$oscore = ereg_replace ("\-", "", $oscore);
if ($dscore > $oscore) 
&#123; 
$dscore;
&#125; 
else
&#123; 
$oscore;
&#125; 
&#125;
echo "$dscore";
Modified yours a bit. It just displays the first $dscore in the column, not the total amount of rows like I want.

missed something

Posted: Mon Oct 28, 2002 1:13 am
by phpScott
sorry I can't get this to display the plus sign. There should be two plus signs after $dscoreTotal and $oscoreTotal; This will increment $dscoreTotal by one.

Code: Select all

if ($dscore &gt; $oscore) 
{ 
$dscoreTotal  ; 
} 
{ 
$oscoreTotal  ; 
} 

?&gt;

Posted: Mon Oct 28, 2002 1:36 am
by billman
Ah, I was wondering why there was an extra space between the variable and the semi-colon :lol:

Anyways, worked liked a charm and it's doing exactly what I want. I knew it would be a simple thing that I overlooked, thanks :wink:

Posted: Mon Oct 28, 2002 1:45 am
by volka
if you need both part as separate values why don't you store them like that? Two fields, both ints, called dscore and oscore and you can do all this with simple sql.
  • how many records with dscore>oscore are there
    :arrow: SELECT count(*) from table WHERE dscore>oscore
  • get all those record
    :arrow: SELECT * from table WHERE dscore>oscore
  • sum them up
    :arrow: SELECT SUM(dscore), SUM(oscore) from table WHERE dscore>oscore
  • maximum scores
    :arrow: SELECT MAX(dscore), MAX(oscore) from table
and so on....

Posted: Mon Oct 28, 2002 3:15 am
by billman
volka wrote:if you need both part as separate values why don't you store them like that? Two fields, both ints, called dscore and oscore and you can do all this with simple sql.
When I designed the database and entered all the rows, I didn't plan on retreiving the way I am now. I was going to pull the info just as $row->score and it would display it as xxx-xxx, but the new site I'm working on is much different than the old one. I didn't feel like modifying 30plus (damn forum bug) rows :P

Posted: Mon Oct 28, 2002 5:10 am
by volka
you can do it step-by-step, when ever you want ;)
  • First alter the table to add the fields (allow NULL value)
  • change INSERT,UPDATE,REPLACE-statements to also insert the numerical values
  • write a simple script that updates existing records
  • replace the SELECT statements and/or insert code right after the ..._fetch_... statements that will add the score-field to the result set, created by $...dscore.'-'.$...oscore (would be easier with the array-version of ..._fetch_... ;) ), even if there is already such a field.
  • find code that uses $...score and replace it by code that uses $...dscore and $...oscore
when certain that all code has been replaced you can change the db-field-settings to NOT NULL and remove the $...score=$dscore.'-'.$oscore

I think it's worth it.....