Page 1 of 1

I need help with syntax

Posted: Fri Nov 13, 2009 2:56 pm
by p10select
I added an if else statement in my code to single out a user and the details of that user but it comes out giving my an unlimited number of results with everyone having that user name only but having the details of the first record in the query. I'm sure there is a better way to position the if else statement to do what I want but I'm having trouble figuring it out. I am posting the code below.

<?
$query = 'SELECT * , sum( points ) , count( week ) , min( points ) , max( points ) FROM `SeasonTotals` GROUP BY name ORDER BY `sum( points )` DESC ';
$result=mysql_query($query);
$fnum=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $fnum) {
//$st=0;
$p=$i-1;
$spleader=mysql_result($result,0,"sum( points )");
$rname=mysql_result($result,$i,"name");
$points=mysql_result($result,$i,"sum( points )");
$weeks=mysql_result($result,$i, "count( week )");
$min=mysql_result($result,$i, "min( points )");
$max=mysql_result($result,$i, "max( points )");
$pb=mysql_result($result,$p,"sum(points)");
$behind=$spleader-$points;
$extra=1 ;
$pos=$i+$extra ;

if ($rname="JG4TMR") {
?>

<tr>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$pos"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$rname"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$points"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$behind"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$weeks"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$min"; ?></font></b></td>
<td align="center"><b><font face="Arial, Helvetica, sans-serif"><? echo "$max"; ?></font></b></td>
</tr>

<?
} else {

++$i;
}
}

echo "</table>";
?>

Re: I need help with syntax

Posted: Fri Nov 13, 2009 3:37 pm
by Mark Baker
Try using a WHERE clause in your SQL statement.... learn to do that, and you'll take a massive step forward in your development

Re: I need help with syntax

Posted: Fri Nov 13, 2009 10:28 pm
by p10select
If you are referring to using the where statement in my query line then I can't just use the name I'm looking for there. I need to list all results of the query and choose to show the one who's name matches the if statement so that I can show position # which is calculated in the loop.

Maybe I can't do it this way but I was hoping. :)

Re: I need help with syntax

Posted: Fri Nov 13, 2009 10:46 pm
by superdezign
You could use a WHERE to find the item, and then use COUNT to determine how many entries came before that item.

Code: Select all

SELECT * FROM `table` WHERE `field` = 'value';

Code: Select all

SELECT count(*) AS `totalRowsBeforeItem` FROM `table` WHERE `primary_key` < $primaryKeyValue;
This assumes you get the $primaryKeyValue from the first query and use it in the second one.

Re: I need help with syntax

Posted: Fri Nov 13, 2009 11:47 pm
by p10select
Interesting thought there. I had yet to consider that as an option.

I'll work on that and get back with you.

thanks!!!!