...

View Full Version : Resolved Print the other rows as blank and not just the ones that meet a queries...



MoreBloodWine
12-20-2010, 01:27 AM
Testing Page: http://www.eojmarket.com/referrals/ref.php

Ok, so if you check out the above link for my testing page you will see that there's one row with data being shown since that one row meets the main queries search criteria. Well, as you'll notice, the code is set to show a maximum of 20 rows, or however many I have set in the $limit figure.

So here's what I want to know, assuming it's possible.

How can I print the other remaining rows but print blank... the other rows would idealy print as the one row does now with it's data but only print with the Place figure which means the Referrer ID and Number of Referrals td's would be printed blank.

So as it stands there should be 19 blank rows with the Place figure as the only td being filled.

Then they would obviously fill as new data comes in that meets the queries search criteria.

Hope this made sense and Ty for your help !

Ps; If possible please reply with a find this and replace with this sort of answer as SMF breaks up all the line breaking in my code... well most of it any way.



<?php

$contestbegin = strtotime('12/1/2010');
$contestend = strtotime('12/31/2010 11:59:59pm');

$prize = "New Copy of Uncharted 2: Among Thieves, GoTY Edition";
$prizepic = "u2goty.jpg";
$limit = 20;

$username="USER";
$password="PASS";
$database="DBASE";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die ("Unable to select database");

// Query Begin //
$sql = "SELECT u1.referred_by, u2.registered, COUNT(*) AS referral_count "
. " FROM ucard AS u1, ucard AS u2 "
. " WHERE IFNULL(u1.referred_by,'') != '' "
. " AND u1.referred_by = u2.psnid AND u2.registered BETWEEN $contestbegin AND $contestend "
. " GROUP BY u1.referred_by, u2.registered "
. " ORDER BY referral_count DESC LIMIT $limit";
$data = mysql_query($sql) or die(mysql_error() . '<br />Query: ' . $sql);

$request = mysql_query("SELECT COUNT(DISTINCT(`referred_by`)) FROM `ucard` WHERE `referred_by`!='' AND registered BETWEEN $contestbegin AND $contestend");
list($numreferrers) = mysql_fetch_row($request);
mysql_free_result($request);
// Query End //

function addOrdinalSuffix($number) {
if (in_array(($number % 100),array(11,12,13))){
return $number.'th';
}
else {
switch (($number % 10)) {
case 1:
return $number.'st';
break;
case 2:
return $number.'nd';
break;
case 3:
return $number.'rd';
break;
default:
return $number.'th';
break;
}
}
}

echo '
<body style="background-color: #000000; color: #FFFFFF;">
<center>
';

echo '
<table border="3" bordercolor="#9E853D">
';

if(time() > $contestend){
echo '
<tr align="center">
<td colspan="4" style="border: 1px solid; border-color: #9E853D;">
------- Sorry, but this contest ended on '.date('n/j/Y', strftime($contestend)).' ! -------
</td>
</tr>
';
}

echo '
<tr align="center">
<td colspan="3" style="border: 1px solid; border-color: #9E853D;">Referrel Contest Leaderboard<br />&nbsp;Number of Participants: '.$numreferrers.', Showing the top '.$limit.'&nbsp;</td>
<td style="border: 1px solid; border-color: #9E853D;">1st Place Prize:<br />&nbsp;'.$prize.'&nbsp;</td>
</tr>
';

echo '
<tr align="center">
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Place&nbsp;
</td>
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Referrer ID&nbsp;
</td>
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Number of Referrals&nbsp;
</td>
<td rowspan="'.($limit + 1).'" style="border: 1px solid; border-color: #9E853D;">
<img width="407" height="515" src="http://www.eojmarket.com/referrals/prizes/'.$prizepic.'">
</td>
</tr>
';

$place = 0;

while($row = mysql_fetch_array($data)){

$place++;
$registered = $row["registered"];

echo '
<tr>
<td style="border: 1px solid; border-color: #9E853D;">
'.addOrdinalSuffix($place).'
</td>
<td style="border: 1px solid; border-color: #9E853D;">
<a href="',"http://www.myu2sig.com/usergeneratedimages/", $row["referred_by"],".png", '" target="new_page">', $row["referred_by"], '</a>
<font size="1">(';
if ($registered >= 1){
echo date('n/j/Y g:ia', strftime($registered));
}else{
echo 'Not Recorded';
}
echo ')
</font>
</td>
<td style="border: 1px solid; border-color: #9E853D;">
'.$row["referral_count"].'
</td>
</tr>
';
}

echo '
</table>
';

echo '
</center>
</body>
';

?>

Fumigator
12-20-2010, 05:28 PM
So you want a fixed number of rows in your table, regardless of how many rows are in your resultset? This would be pretty easy to take care of by replacing your while($row = mysql_fetch_array($data)) with a for ($i = 0; $i < 20; $i++) loop. Something like:



for ($i = 0; $i < 20; $i++) {
if ($i < mysql_num_rows($data)) {
$row = mysql_fetch_array($data);
//generate your <tr> row full of data here
} else {
//generate your empty <tr> row here
}
}

MoreBloodWine
12-20-2010, 07:52 PM
Guess I forgot to notify on this post, wasnt aware you had replied. In any case, I "think" O get what your saying, I just need to make sure I put the right code in the right place.

Edit: I guess that since I would want the data to change along with the set limit the 20 in there could be replace with $limit which is set at the top of the file right ?

Anyway, I'm gonna try my luck at formatting this right, but if your feeling really generous I wont stop you ;-)

All the pertinent code is in the original post !
So you want a fixed number of rows in your table, regardless of how many rows are in your resultset? This would be pretty easy to take care of by replacing your while($row = mysql_fetch_array($data)) with a for ($i = 0; $i < 20; $i++) loop. Something like:



for ($i = 0; $i < 20; $i++) {
if ($i < mysql_num_rows($data)) {
$row = mysql_fetch_array($data);
//generate your <tr> row full of data here
} else {
//generate your empty <tr> row here
}
}

MoreBloodWine
12-20-2010, 08:09 PM
NM, I got it working but there's one other thing I'd like to do...

Check out my testing page: http://www.eojmarket.com/referrals/test.php

You'll see that it looks really good now and not have stretched out rows like it did before the change. Well, the Referrer ID & Referral Count rows are blank when there's not data to show like I'd expect but the "boxes" don't show from rows 2 through 20. How would I get them to show up so we don't have that big black open space ?

Here's current code wit hthe most recent changes...



<?php

$contestbegin = strtotime('12/1/2010');
$contestend = strtotime('12/31/2010 11:59:59pm');

$prize = "New Copy of Uncharted 2: Among Thieves, GoTY Edition";
$prizepic = "u2goty.jpg";
$limit = 20;

$username="USER";
$password="PASS";
$database="DBASE";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die ("Unable to select database");

// Query Begin //
$sql = "SELECT u1.referred_by, u2.registered, COUNT(*) AS referral_count "
. " FROM ucard AS u1, ucard AS u2 "
. " WHERE IFNULL(u1.referred_by,'') != '' "
. " AND u1.referred_by = u2.psnid AND u2.registered BETWEEN $contestbegin AND $contestend "
. " GROUP BY u1.referred_by, u2.registered "
. " ORDER BY referral_count DESC LIMIT $limit";
$data = mysql_query($sql) or die(mysql_error() . '<br />Query: ' . $sql);

$request = mysql_query("SELECT COUNT(DISTINCT(`referred_by`)) FROM `ucard` WHERE `referred_by`!='' AND registered BETWEEN $contestbegin AND $contestend");
list($numreferrers) = mysql_fetch_row($request);
mysql_free_result($request);
// Query End //

function addOrdinalSuffix($number) {
if (in_array(($number % 100),array(11,12,13))){
return $number.'th';
}
else {
switch (($number % 10)) {
case 1:
return $number.'st';
break;
case 2:
return $number.'nd';
break;
case 3:
return $number.'rd';
break;
default:
return $number.'th';
break;
}
}
}

echo '
<body style="background-color: #000000; color: #FFFFFF;">
<center>
';

echo '
<table border="3" bordercolor="#9E853D">
';

if(time() > $contestend){
echo '
<tr align="center">
<td colspan="4" style="border: 1px solid; border-color: #9E853D;">
------- Sorry, but this contest ended on '.date('n/j/Y', strftime($contestend)).' ! -------
</td>
</tr>
';
}

echo '
<tr align="center">
<td colspan="3" style="border: 1px solid; border-color: #9E853D;">Referrel Contest Leaderboard<br />&nbsp;Number of Participants: '.$numreferrers.', Showing the top '.$limit.'&nbsp;</td>
<td style="border: 1px solid; border-color: #9E853D;">1st Place Prize:<br />&nbsp;'.$prize.'&nbsp;</td>
</tr>
';

echo '
<tr align="center">
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Place&nbsp;
</td>
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Referrer ID&nbsp;
</td>
<td style="border: 1px solid; border-color: #9E853D;">
&nbsp;Number of Referrals&nbsp;
</td>
<td rowspan="'.($limit + 1).'" style="border: 1px solid; border-color: #9E853D;">
<img width="407" height="515" src="http://www.eojmarket.com/referrals/prizes/'.$prizepic.'">
</td>
</tr>
';

$place = 0;

for ($i = 0; $i < $limit; $i++) {

$place++;

if ($i < mysql_num_rows($data)) {

$row = mysql_fetch_array($data);
$registered = $row["registered"];

echo '
<tr>
<td style="border: 1px solid; border-color: #9E853D;">
'.addOrdinalSuffix($place).'
</td>
<td style="border: 1px solid; border-color: #9E853D;">
<a href="',"http://www.myu2sig.com/usergeneratedimages/", $row["referred_by"],".png", '" target="new_page">', $row["referred_by"], '</a>
<font size="1">(';
if ($registered >= 1){
echo date('n/j/Y g:ia', strftime($registered));
}else{
echo 'Not Recorded';
}
echo ')
</font>
</td>
<td style="border: 1px solid; border-color: #9E853D;">
'.$row["referral_count"].'
</td>
</tr>
';
} else {
echo '
<tr>
<td style="border: 1px solid; border-color: #9E853D;">
'.addOrdinalSuffix($place).'
</td>
<td style="border: 1px solid; border-color: #9E853D;">
</td>
<td style="border: 1px solid; border-color: #9E853D;">
</td>
</tr>
';
}
}

echo '
</table>
';

echo '
</center>
</body>
';

?>

djm0219
12-20-2010, 08:16 PM
Add a required space to the empty cells.



<td style="border: 1px solid; border-color: #9E853D;">&nbsp;</td>
<td style="border: 1px solid; border-color: #9E853D;">&nbsp;</td>

MoreBloodWine
12-20-2010, 09:11 PM
Add a required space to the empty cells.



<td style="border: 1px solid; border-color: #9E853D;">&nbsp;</td>
<td style="border: 1px solid; border-color: #9E853D;">&nbsp;</td>


http://www.eojmarket.com/referrals/ref.php

Worked like a charm, the really funny part though is that I should have thought of that. I sort of did except that I used an actual space and not the &nbsp; which prints either way heh



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum