Counting rows issue

Smokey · January 17, 2012

I know this has to be something simple, either I'm just not getting it or its so late that my mind isnt working properly, anyway, i am building a list of users referrals so they can keep track of the people they have referred to the game, the problem is it only adds the first person they referred (first in the table) to the list. I cannot for the life of me figure out what i am doing wrong, I'm having the same issue with listing the item (in shop) locations which i am listing on the iteminfo page so users know where the items are located, it will list only the first location in the db ( i have 10 countries with 10 cities in each so some items are available in more than 1 city) if i can figure out the referral list i'm sure i can get the latter worked out. so heres the code for the referral list:

<?php


include "globals.php";

print "
<table cellspacing='0' width='100%' border='1'>
	<tr>
<td style='border:1px solid #A39B83;' bgcolor='#7D7764' colspan='4'><b><center><font size=2>Your Referrals</b></td></tr>
	<tr>
<tr>
</tr><tr><th style='border:1px solid #7D7764;' bgcolor='#948D76' width='20%'>ID</th><th style='border:1px solid #7D7764;' bgcolor='#948D76' width='20%'>Referred</th><th style='border:1px solid #7D7764;' bgcolor='#948D76' width='20%'>Level</th><th style='border:1px solid #7D7764;' bgcolor='#948D76' width='20%'>Age</th></tr>
";

$ref=$db->query("SELECT * FROM referals WHERE refREFER = $userid");
$rs=$db->fetch_row($ref);

if(!$db->num_rows($ref) ) 
{
$refer="No Referals";   
}
else
{
$refer="{$rs['refREFED']}";
}

$qs=$db->query("SELECT * FROM users WHERE userid = {$rs['refREFED']}");
$rf=$db->fetch_row($qs);
print "
<tr><td style='border:1px solid #7D7764;' bgcolor='#C9C9C9'><center>{$rf['userid']}</a></td><td style='border:1px solid #7D7764;' bgcolor='#C9C9C9'><center><a href='viewuser.php?u={$rf['userid']}'>{$rf['username']}</a></td> <td style='border:1px solid #7D7764;' bgcolor='#C9C9C9'><center>{$rf['level']}</td> <td style='border:1px solid #7D7764;' bgcolor='#C9C9C9'><center>{$rf['daysold']} Days Old</td></tr>

<tr><th style='border:1px solid #A39B83;' bgcolor='#7D7764'colspan=4></th></tr>
</table><br> ";
?>

any help would be much appreciated.

Newbie123 · January 17, 2012

You need to run your query through a loop to retrieve all the results you want; I'd post the code for you but I don't have time at the moment.

Look up - http://php.net/manual/en/control-structures.while.php

That should help you. :)

Smokey · January 17, 2012

I must be putting this together wrong.

<?php


include "globals.php";
$sql       = 'SELECT COUNT(refID) FROM referals WHERE refREFER = ' . $userid;
$rs        = mysql_query($sql);
$referals = array_shift(mysql_fetch_row($rs));
mysql_free_result($rs);

   $sql = <<<SQL

          SELECT userid, username
            FROM users
       LEFT JOIN referals ON refREFED = userid
           WHERE refREFER = $userid
        ORDER BY level DESC, username

  SQL;

   echo '

       <table border="1">
           <tr>
               <th>ID</th>
               <th>Name</th>
               <th>Level</th>
           </tr>

   ';

   $rs = mysql_query($sql);

   WHILE ($row = mysql_fetch_row($rs))
   {
       list($id, $name, $level) = $row;

       $name  = htmlentities($name, ENT_QUOTES, 'utf-8');
       $level = number_format($level);

       echo <<<HTML

           <tr>
               <td>$id</td>
               <td>$name</td>
               <td>$level</td>
           </tr>

   HTML;
   }

   mysql_free_result($rs);

   echo '<br style="clear:both;" /></table>';

$h->endpage();
exit;
?>

It works until i get down to the HTML part, i get an unexpected $end error, if i change the HTML part to a regular echo the file will work...but it will only show $id, $name, $level instead of their actual info like it isnt being defined.

mixmaster · January 17, 2012

<?php 

include "globals.php";
$sql       = 'SELECT COUNT(refID) FROM referals WHERE refREFER = ' . $userid;
$rs        = mysql_query($sql);
$referals = array_shift(mysql_fetch_row($rs));
mysql_free_result($rs);

   $sql = "

          SELECT userid, username
            FROM users
       LEFT JOIN referals ON refREFED = userid
           WHERE refREFER = $userid
        ORDER BY level DESC, username

  ";

   echo '

       <table border="1">
           <tr>
               <th>ID</th>
               <th>Name</th>
               <th>Level</th>
           </tr>

   ';

   $rs = mysql_query($sql);

   WHILE ($row = mysql_fetch_row($rs))
   {
       list($id, $name, $level) = $row;

       $name  = htmlentities($name, ENT_QUOTES, 'utf-8');
       $level = number_format($level);

       echo "

           <tr>
               <td>".$id."</td>
               <td>".$name."</td>
               <td>".$level."</td>
           </tr>

   ";
   }

   mysql_free_result($rs);

   echo '<br style="clear:both;" /></table>';

$h->endpage();
exit;
?>

Try that

Smokey · January 17, 2012

Well you learn something everyday, i did not know this, i havent been coding very long at all :P I took out the whitespace and viola, it worked, though it is showing their level as 0 but a little work and im sure i can figure that out, thanks Octarine, i appreciate the help :)

Edit, the level part was because it wasnt retrieving level from the usertable, easy fix, thanks alot, and mixmaster that worked just the same as Octarines, thank you too

Edited January 17, 2012 by Smokey

mixmaster · January 17, 2012

No problem

Smokey · January 17, 2012

indeed, it was an easy thing to look over and even easier to rectify, it gave the the base i needed and i can now stop slamming my head into the desk :P

mixmaster · January 17, 2012

@Smokey, my apologies; I missed the level field in the original SELECT statement.
I seldom test these things out days, it's all of the top of my head so the occasional

slip up I'm sure can be forgiven.

@mixmaster, no need for exit or ?>.

1: I didn't put them in. 2: Ofcourse there is a need for the ?>:p

Edited January 17, 2012 by mixmaster

Smokey · January 17, 2012

No i put the exit in, the layout i paid $200 for (wont do that again) from w3theory (Peter) will cut off the bottom of the page if i dont exit.

mixmaster · January 17, 2012

No i put the exit in, the layout i paid $200 for (wont do that again) from w3theory (Peter) will cut off the bottom of the page if i dont exit.

Why wont you be buying from peter again ?

mixmaster · January 17, 2012

hmmm i always just asumed you started php with <?php and ended the script with ?> other wise it wouldn't work

mixmaster · January 18, 2012

Rarely been on php.net (only to look at functions) but thanks for letting me know :)

Smokey · January 18, 2012

No its not that i wont be buying from Peter again, i have hosting through him and have no problems with it, except it seems to be down at the moment, I just meant next time I'll make sure I explain in detail exactly what i want as we had some confusion with the layout i bought.

mixmaster · January 18, 2012

No its not that i wont be buying from Peter again, i have hosting through him and have no problems with it, except it seems to be down at the moment, I just meant next time I'll make sure I explain in detail exactly what i want as we had some confusion with the layout i bought.

Yes im also with his hosting , i think this is the third time its been down in 2 weeks

Counting rows issue

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation