Counting rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Counting rows

Post 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:
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

try this

Post 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
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

missed something

Post 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;
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Post 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:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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....
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.....
Post Reply