Count Records/rows

[mmaserati]

I am getting errors on this very basic coount. When I run the query in MySql it works

when I go to display it in a page I get an error:
mysql_num_rows(): supplied argument is not a valid MySQL result resource in
line.....

the line it refers to is::confused: $num_rows = mysql_num_rows($SQL);



$SQL ="SELECT count(*) FROM t1m_users WHERE idspr = 5 ";

$num_rows = mysql_num_rows($SQL);

echo "$num_rows Rows\n";

[oesxyl]

I am getting errors on this very basic coount. When I run the query in MySql it works

when I go to display it in a page I get an error:
mysql_num_rows(): supplied argument is not a valid MySQL result resource in
line.....

the line it refers to is::confused: $num_rows = mysql_num_rows($SQL);



$SQL ="SELECT count(*) FROM t1m_users WHERE idspr = 5 ";

$num_rows = mysql_num_rows($SQL);

echo "$num_rows Rows\n";



$SQL ="SELECT count(*) FROM t1m_users WHERE idspr = 5 ";
$result = mysql_query($SQL) or die("things go wrong by default. you must check them");
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";


regards

[ShaneC]

$SQL ="SELECT count(*) FROM t1m_users WHERE idspr = 5 ";
$result = mysql_query($SQL) or die("things go wrong by default. you must check them");
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";
regards

To explain oesxy's response a little:

What you were trying to do in your code was analyze a string, not the query itself. In order to actually execute your request to the SQL database, you need to use the mysql_query function. Once you do that you can then get the number of rows.

[Daniel Israel]

To explain oesxy's response a little:

What you were trying to do in your code was analyze a string, not the query itself. In order to actually execute your request to the SQL database, you need to use the mysql_query function. Once you do that you can then get the number of rows.

Of course, in this query, the count is returned as a field, so the number of rows returned will always be 1, right?

Execute the query, the return should be 1 row of 1 field. That field will be the row count.

[mmaserati]

Shane / Daniel -

when I run the query in MySQL I get a row count of 2 which is correct.

However when run on th PHP page I get a result of 1

so somehow it seems like I am print out the number of rows returned and not the value of the row returned ? any help on that ??

Thanks in Advance.

[ShaneC]

Shane / Daniel -

when I run the query in MySQL I get a row count of 2 which is correct.

However when run on th PHP page I get a result of 1

so somehow it seems like I am print out the number of rows returned and not the value of the row returned ? any help on that ??

Thanks in Advance.

You're over complicating it! It's easy to get tied up with mySQL syntax but don't forget the wonders PHP is doing for you.

Your query is:

SELECT count(*) FROM t1m_users WHERE idspr = 5 "


You are selecting a count, but that is what mysql_num_rows is getting. Try this:


<?php

$sql = mysql_query( "SELECT * FROM t1m_users WHERE idspr = '5'" ) or die( mysql_error() );

$numRows = mysql_num_rows( $sql );

print( $numRows );
exit;

?>


That will select all rows from t1m_users that have an idspr of 5. Then, your $numRows variable will count those rows, and print them out below.

Try this out and let us know how it goes.

[oesxyl]

Shane / Daniel -

when I run the query in MySQL I get a row count of 2 which is correct.

However when run on th PHP page I get a result of 1

so somehow it seems like I am print out the number of rows returned and not the value of the row returned ? any help on that ??

Thanks in Advance.

probably this must be the name of mysql_num_rows -> mysql_numberof_rows, :)

to fetch multiple rows

if($result){
while($rows = mysql_fetch_assoc($result)){ // feach each row
foreach($rows as $fieldname => $value){ // walk to row and fecth each field
print $fieldname.": ".$value."<br/>";
}
}
}else{
// get rid of die from mysql_query and use here mysql_error() to show what's goes wrong.
}


regards

[oesxyl]

sorry is my fault, I miss what op want, :)



<?php

$sql = mysql_query( "SELECT * FROM t1m_users WHERE idspr = '5'" ) or die( mysql_error() );

$numRows = mysql_num_rows( $sql );

print( $numRows );
exit;

?>


this work but is not a good idea to use php for something what mysql do faster and more easy.
I guess the 'problem' is the field name, so the simplest solution is to use an alias:


SELECT (count(idspr)) as myfieldname FROM t1m_users WHERE idspr = 5


then just fetch the field 'myfieldname'

forget again the code, :)

$query = "SELECT (count(idspr)) as myfieldname FROM t1m_users WHERE idspr = 5";
$result = mysql_query($query);
if($result){
print mysql_result($result,0,'myfieldname');
}

regards

[mmaserati]

Thank you so much for not only getting me a solution(s), but to explain how/why.

I truly appreciate this type of help.

thank you Daniel Israel, oesxyl, and of course ShaneC. Who in this case really brought out the power of PHP v MySQL in code.

now it is on to step to and three :o)

[mmaserati]

anyone for the next step ?

I need to go 3 levels deep and this was the first level. So not only do I need the count but also the value of the field idspr (which is the sponsor id). I use the count to do a math for each level (Level one is *2, Level 2 is *3 and finally Level3 is *10). the filed value is so I can re query on the second and third level.

By this if on this first query it returns values of 15 and 50
the second query (Level) uses 15 and 50 in the where clause

same for the third query (Level)


here is the ugly I tried:


$sql = 'select'
. ' (select count(*) from t1m_users where idspr=\'5\' )'
. ' + (select count(*) from t1m_users where idspr in'
. ' (select id from t1m_users where idspr = \'1\')) * 2'
. ' + (select count(*) from t1m_users where idspr in '
. ' (select id from t1m_users where idspr in'
. ' (select id from t1m_users where idspr = \'1\'))) * 10';

$result = mysql_query($sql) or die(mysql_error());

[oesxyl]

1. avoid useless concatenate and escapes if you don't want to spend hours with debbuging simple things.

this is the same thing:

$sql = "select
(select count(*) from t1m_users where idspr='5' )'
+ (select count(*) from t1m_users where idspr in'
(select id from t1m_users where idspr = '1')) * 2'
+ (select count(*) from t1m_users where idspr in '
(select id from t1m_users where idspr in'
(select id from t1m_users where idspr = '1'))) * 10";


2. solve how much you can from this problem to mysql level, that means, I guess, to post in mysql forum, because the query seems to be pretty complicated. I suggest to give more details about what you want to select, what you want to compute and so on. I guess you must use some variables, not sure.

3. with the solution from mysql, one query or more, start implement the php part. As far I see the php part seems pretty done already, :)

regards

[mmaserati]

Thanks so much for the advice. The origin of this thread was to get the record count. I then realized that the values of the (idspr) sponsor id would be needed to complete this.

This is all to figure out commission through a down line (to 3 levels).

The first level pays $2.00 X the number of users.
The second level pays $3.00 X the number of users
The third level pays $10.00 X the number of users

Scenaro:

LEVEL ONE:
A member has an id of 5 and sponsor id (idspr) of 1, they also have 2 users under them (users 10,11).

LEVEL TWO
user 10 has 3 users under them (users 15,20,21)
user 11 has 2 users under them (users 35,36,29)

LEVEL THREE:
users 15 has 2 users under them (users 67,70,53)
users 20 has 0 users under them ()
users 21 has 3 users under them (users 77,89,90)
users 35 has 2 users under them ()
users 36 has 0 users under them ()
users 29 has 3 users under them (users 105,99,92)

Using the formula above
Level 1 = $ 4.00
Level 2 = $ 18.00
Level 3 = $ 90.00
The commission would be $112.00

each record in the table has an id and idspr (both int)
here in the thread I hard code idspr but in the php it has to be dynamic..obviously.

I will see what help I can get in the MySql side, but so far the help here has been really great. I know this has will require a type of recursive query so I can get an accurate row count before the math.

[oesxyl]

Thanks so much for the advice. The origin of this thread was to get the record count. I then realized that the values of the (idspr) sponsor id would be needed to complete this.

This is all to figure out commission through a down line (to 3 levels).

The first level pays $2.00 X the number of users.
The second level pays $3.00 X the number of users
The third level pays $10.00 X the number of users

Scenaro:

LEVEL ONE:
A member has an id of 5 and sponsor id (idspr) of 1, they also have 2 users under them (users 10,11).

LEVEL TWO
user 10 has 3 users under them (users 15,20,21)
user 11 has 2 users under them (users 35,36,29)

LEVEL THREE:
users 15 has 2 users under them (users 67,70,53)
users 20 has 0 users under them ()
users 21 has 3 users under them (users 77,89,90)
users 35 has 2 users under them ()
users 36 has 0 users under them ()
users 29 has 3 users under them (users 105,99,92)

Using the formula above
Level 1 = $ 4.00
Level 2 = $ 18.00
Level 3 = $ 90.00
The commission would be $112.00
you don't need a FOURTH LEVEL? I mean the $10 * number of users of users with id 67,70,53, 77,89,90,105,99,92

each record in the table has an id and idspr (both int)
here in the thread I hard code idspr but in the php it has to be dynamic..obviously.

I will see what help I can get in the MySql side, but so far the help here has been really great. I know this has will require a type of recursive query so I can get an accurate row count before the math.
in mysql forum are users which probably don't watch this forum and are able to find a solution. To be honest I don't know how to solve this, :) I mean a decent solution because in few query can be solved easy.

regards

[mmaserati]

you don't need a FOURTH LEVEL? I mean the $10 * number of users of users with id 67,70,53, 77,89,90,105,99,92


in mysql forum are users which probably don't watch this forum and are able to find a solution. To be honest I don't know how to solve this, :) I mean a decent solution because in few query can be solved easy.

regards

oh yes the fourth level is correct. I forgot that the first level is just loggin in. LOL

I appreciate your help I know this isnt an easy one.

[mmaserati]

Well nothing there. I mean it works but only partially because after the first level I dont know how to get every sub users idspr and what I have is using the wrong one.

so I have this:


$sID = $_SESSION['idspr'];

$sql = "select
(select count(*) from t1m_users where idspr='$sID' ) * 2 as level1,
(select count(*) from t1m_users where idspr in
(select id from t1m_users where idspr = '$sID')) * 3 as level2,
(select count(*) from t1m_users where idspr in
(select id from t1m_users where idspr in
(select id from t1m_users where idspr = '$sID'))) * 10 as level3";

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$total = $row['level1'] + $row['level2'] + $row['level3'];

echo $sql;





<fieldset>
<div align="left">
<legend>COMMISSION Overview</legend>
<br />
</div>
<table width=100% border=0 cellpadding=4 cellspacing=0 background="/images/page_bg.gif">
<tr>
<td><label><strong>Commission:</strong></label></td>
</tr>
<tr>
<td width="202">This Year:<span class="style1"> $ .00</span></td>
<td width="14"></td>
<td width="303">This Month:<span class="style1"> $<?php echo $total ?>.00</span></td>
<td width="241">Members in Downline: <?php//:( echo $level1 ?></td>
</tr>
</table>
</fieldset>

[oesxyl]

I can't test this so I don't know if it work. Try this way:
<?php
$sID = $_SESSION['idspr'];

$sql = "select
@fslevel := (select id from t1m_users where idspr = '".$sID."'),
@sdlevel := (select id from t1m_users where idspr in @fslevel),
(select count(*) from t1m_users where idspr='".$sID."') as level1,
(select count(*) from t1m_users where idspr in @fslevel) as level2,
(select count(*) from t1m_users where idspr in @sdlevel) as level3";

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$total = $row['level1'] * 2 + $row['level2'] * 3+ $row['level3'] * 10;

echo $sql;
?>
<fieldset>
<div align="left">
<legend>COMMISSION Overview</legend>
<br />
</div>
<table width=100% border=0 cellpadding=4 cellspacing=0 background="/images/page_bg.gif">
<tr>
<td><label><strong>Commission:</strong></label></td>
</tr>
<tr>
<td width="202">This Year:<span class="style1"> $ .00</span></td>
<td width="14"></td>
<td width="303">This Month:<span class="style1"> $<?php echo $total ?>.00</span></td>
<td width="241">Members in Downline: <?php//:( echo $level1 ?></td>
</tr>
</table>
</fieldset>


regards