displaying sql data 1

[SLMHC]

im wondering if there is a way to display the data of sql table row by row until it either reaches the last data element or reaches a predefined number, say 10.

i can, and have working a script that will display the data row by row untill it reaches the end of the data. I also can get it to count to 10 with out a problem. the issue is the script will always count to 10, so if there isnt enough data in the table it spits out empty lines.

here is my code that spits out empty lines:

<?php

include 'dbinfo.php';

include 'dbconnect.php';

$query="SELECT * FROM batting_rbi ORDER BY stat DESC";
$result=mysql_query($query);

//$num=mysql_numrows($result);
$num=10;

mysql_close();

?>

<table width="90%" border="1" cellspacing="1" cellpadding="1" bordercolor="#000000" bgcolor="#F8D8B8">
<tr>
<td class="tabletext"><h4>#</h4></td>
<td class="tabletext"><h4>Player</h4></td>
<td class="tabletext"><h4>Year</h4></td>
<td class="tabletext"><h4>Team</h4></td>
<td class="tabletext"><h4>Average</h4></td>
</tr>

<?php
$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$year=mysql_result($result,$i,"year");
$team=mysql_result($result,$i,"team");
$stat=mysql_result($result,$i,"stat");
$id=mysql_result($result,$i,"id");
?>

<tr>
<td class="tabletext"><? echo $i+1 ?></td>
<td class="tabletext"><? echo $name ?></td>
<td class="tabletext"><? echo $year ?></td>
<td class="tabletext"><img src="images/logos/<? echo $team ?>.bmp" alt="<? echo $team ?> Logo"></td>
<td class="tabletext"><? echo $stat ?></td>
</tr>

<?
$i++;
}
echo "</table>";

?>

-Dave

 

[KarveR]

this may help, its basially a count of how much was returned, so yoiu can use this along with LIMIT 10; to get a maximum row count of 10 or a count of rows should it be less than 10.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[vacunita]

How about a correctly placed IF.
$rows=$mysql_num_rows($result)

if($rows<=10){ $num=$rows;} else{$num=10};

If the amount of rows is less than ten, then it will set your $num variableat that number, otherwise, it will set it at 10. So when it prints the records, it will only print however many weres returned if less than 10, or 10 if there are more.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[SLMHC]

thanks for the IF statement idea...needed to clean up your code for syntax but it works great.

$rows=mysql_numrows($result);

if($rows <= 10)
  {
  $num = $rows;
  } 
else
  {
  $num = 10;
  }

i knew a quick post here would yield fruit. you can see the script in action @

http://il30.dave-tek.com

-Dave

 

[KarveR]

if you'd used
$query="SELECT * FROM batting_rbi ORDER BY stat DESC LIMIT 10";

you don't need any IF statement at all.

$num=mysql_num_rows($result);

this will never go above 10, but will be set correctly if its less.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[vacunita]

My mistake, inverted the colon and the final bracket. Still it is a clean way of doing it, and can be easiy modified to show any amount of records.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[SLMHC]

Karver,

The PHP help page you sent me to, didnt clearly(at least for me)so me anything useful. I am at the beginning baby steps of getting a handle on php+mysql and IF statetments are something I can do right now. Hence the use of Vacunita's idea.

Which way is better to go with? Or is it apples and oranges, it just depends on what you like better?

Another question came to mind about SELECT statements. Is it possible to sort by 'stat DESC' and say 'year'?

-Dave

 

[KarveR]

Apples and oranges, but if mysql queries can take away 3 or more lines of code from PHP I go with mysql doing the work. (set number required using LIMIT xx)


You can use stat DESC, and a date provided the date is in the correct format, or if you just store the year as an int it will also work.

SELECT blah .... stat DESC,date;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

*note - mysql related posts are best on the mysql forum - I answer there too, but am not in the same league as TonyGroves et al.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[vacunita]

As Karver said, it just Apples and Oranges. But Myself, I like straight forward queries, and any formating or filtering, gets done in PHP. I'd rather have longer commented PHP code, thn longer uncomented queries, in which you end up not knowing what is going on when you are nesting 5 different queries and joins. I just find it easier to debug PHP code than mysql queries. But thats just me. And as you go along you'll develop your own style.

By the way, always check the PHP online manual, it has alot of information to help you use the language and its functions.

http://www.php.net

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.