row counter for a php MySQL result

Hi I have the following code returning rows from my database, how can I add a counter to display the number of rows  i.e. - I want to add a column at the front end of the table saying "1". next row is "2" and so on.  thanx in advance


<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';


function displaytable($query) {
        
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
		
		// 		birthdate	street	city	state	zip							
		//   		BIRTHDATE	STREET	CITY	STATE	ZIP		  					
        echo "<table cellpadding=0 border=0 align='center' class='tableBody'>";
      
	    echo "<tr class='tableBody'><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
      
	    echo "<tr class='resultsBody'><th colspan='9'><hr></tr>";
		
        while(list($playerID, $firstName, $lastName, $yearSchool, $team, $position, $shoot, $x_description, $location) = mysql_fetch_row($result)) {
                echo "<tr class='resultsBody'><form action=\"./update3.php\" method=\"POST\">";
			echo "<input type=\"hidden\" size=\"4\" name=\"playerID\" value=\"$playerID\">";
			    echo "<td>$firstName</td>";
                echo "<td>$lastName</td>";
			echo "<input type=\"hidden\" size=\"4\" name=\"lastName\" value=\"$lastName\">";

                echo "<td>$yearSchool</td>";
                echo "<td>$team</td>";
                echo "<td>$position</td>";
				 echo "<td>$shoot</td>";
				 echo "<td>$x_description</td>";
				 echo "<td><input type=\"text\" size=\"10\" name=\"location\" value=\"$location\"></td>";
		
		
		
				 echo "<td><input type=\"submit\" name=\"submit\" value=\"Update\"></td>";

                echo "</form></tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 

 
}
 
?>
 
<?php
$query = "SELECT playerID, firstName, lastName, yearSchool, team, position, shoot, x_description, location FROM players2010 WHERE location = 'HM GREEN'  ORDER BY position ";
displaytable($query);
?>

Open in new window

franco_cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RooiVosCommented:
Just backup your database before applying these scripts.

If I understand you correctly, you want an ID field for each row? If so:

ALTER TABLE table_name ADD COLUMN id INTEGER AUTO_INCREMENT UNIQUE KEY;

Alternatively, are you maybe looking for http://www.tizag.com/mysqlTutorial/mysqlcount.php ?
0
shobinsunCommented:
Hi,

Just give this:

<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';


function displaytable($query) {
       
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
$count = 0;

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
               
                //              birthdate       street  city    state   zip                                                    
                //              BIRTHDATE       STREET  CITY    STATE   ZIP                                                    
        echo "<table cellpadding=0 border=0 align='center' class='tableBody'>";
     
            echo "<tr class='tableBody'><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
     
            echo "<tr class='resultsBody'><th colspan='9'><hr></tr>";
               
        while(list($playerID, $firstName, $lastName, $yearSchool, $team, $position, $shoot, $x_description, $location) = mysql_fetch_row($result)) {
                echo "<tr class='resultsBody'><form action=\"./update3.php\" method=\"POST\">";
                        echo "<input type=\"hidden\" size=\"4\" name=\"playerID\" value=\"$playerID\">";
                           
echo "<td>$count+1</td>";

 echo "<td>$firstName</td>";
                echo "<td>$lastName</td>";
                        echo "<input type=\"hidden\" size=\"4\" name=\"lastName\" value=\"$lastName\">";

                echo "<td>$yearSchool</td>";
                echo "<td>$team</td>";
                echo "<td>$position</td>";
                                 echo "<td>$shoot</td>";
                                 echo "<td>$x_description</td>";
                                 echo "<td><input type=\"text\" size=\"10\" name=\"location\" value=\"$location\"></td>";
               
               
               
                                 echo "<td><input type=\"submit\" name=\"submit\" value=\"Update\"></td>";

                echo "</form></tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 

 
}
 
?>
 
<?php
$query = "SELECT playerID, firstName, lastName, yearSchool, team, position, shoot, x_description, location FROM players2010 WHERE location = 'HM GREEN'  ORDER BY position ";
displaytable($query);
?>
0
franco_cAuthor Commented:
the table already has a playerID that is auto incremental, I am looking at just the results, to number the rows so I can say" Row 1 , DATA, DATA, DATA; Row 2 , DATA, DATA, DATA; Row 3 , DATA, DATA, DATA; etc.

yes a results counter. I will go look at the URL posted.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

shobinsunCommented:
Hi,

Also change the following line :

echo "<tr class='tableBody'><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";

to:


echo "<tr class='tableBody'><th>#</th><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
0
nasirbestCommented:
add one more column in your select query. like

@rownum:=@rownum+1 AS rownumber

and now just add following line *before* your select query.
mysql_query("set @rownum = 0");

Open in new window

0
franco_cAuthor Commented:
counter is just putting "0+1"

http://www.oshockey.org/2010/springleague/springTeamsList55.php

what am I doing wrong?
0
shobinsunCommented:
HI,

put :

$count = 0;


outside the function: displaytable().

Try it.

0
shobinsunCommented:
If it is not taking inside the function,

declare it as global outside the function as:

global count ;
count = 0;
0
franco_cAuthor Commented:
put :

$count = 0;


outside the function: displaytable().

Try it.


OK When I move the line - $count = 0;
outside of the function, it now shows "+1" instead of "0+1"

I am very new to all this stuff, sorry but I do not follow the next post you put up

" declare it as global outside the function as:

global count ;
count = 0;  "

Sorry,


0
shobinsunCommented:
Hi,

What I meant is:


<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';

global $count;
$count = 0;

function displaytable($query) {

....
....
....

echo "<td>$count+1</td>";

 echo "<td>$firstName</td>";
.....

....


Try this.

Regards.
0
shobinsunCommented:
Sorry,

Try :



<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';

global $count;
$count = 1;

function displaytable($query) {

....
....
....

echo "<td>$count++</td>";

 echo "<td>$firstName</td>";
.....

....


Try this.

Regards.
0
nasirbestCommented:
You can also try my solution

following I am only showing lines which I have changed
...
...                
            echo "<tr class='tableBody'><th>ID</th><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
     
            echo "<tr class='resultsBody'><th colspan='10'><hr></tr>";

        while(list($rowNumber, $playerID, $firstName, $lastName, $yearSchool, $team, $position, $shoot, $x_description, $location) = mysql_fetch_row($result)) {
            echo "<td>$rowNumber</td>";

...
...
...

mysql_query("set @rownum = 0");
$query = "SELECT @rownum:=@rownum+1 AS rowNumber, playerID, firstName, lastName, yearSchool, team, position, shoot, x_description, location FROM players2010 WHERE location = 'HM GREEN'  ORDER BY position ";
...

Open in new window

0
shobinsunCommented:
Hi,

This is the perfect solution:
.......
......

function displaytable($query) {
....

$count = 0;
.....
...

$count = $count+1;  
echo "<td>$count</td>";
.....
....

Try this.

Regards.
0
NerdsOfTechTechnology ScientistCommented:
added row count and implemented inside of your code...

needed a new <th> and respective <td> entry as well...


<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';


function displaytable($query) {
$rown=0;
        
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
                
                //              birthdate       street  city    state   zip                                                     
                //              BIRTHDATE       STREET  CITY    STATE   ZIP                                                     
        echo "<table cellpadding=0 border=0 align='center' class='tableBody'>";
      
            echo "<tr class='tableBody'><th>Row</th><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
      
            echo "<tr class='resultsBody'><th colspan='9'><hr></tr>";
                
        while(list($playerID, $firstName, $lastName, $yearSchool, $team, $position, $shoot, $x_description, $location) = mysql_fetch_row($result)) {
                echo "<tr class='resultsBody'><form action=\"./update3.php\" method=\"POST\">";
                        echo "<input type=\"hidden\" size=\"4\" name=\"playerID\" value=\"$playerID\">";
			    $rown++;
                            echo "<td>$rown</td>";
                            echo "<td>$firstName</td>";
                echo "<td>$lastName</td>";
                        echo "<input type=\"hidden\" size=\"4\" name=\"lastName\" value=\"$lastName\">";

                echo "<td>$yearSchool</td>";
                echo "<td>$team</td>";
                echo "<td>$position</td>";
                                 echo "<td>$shoot</td>";
                                 echo "<td>$x_description</td>";
                                 echo "<td><input type=\"text\" size=\"10\" name=\"location\" value=\"$location\"></td>";
                
                
                
                                 echo "<td><input type=\"submit\" name=\"submit\" value=\"Update\"></td>";

                echo "</form></tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 

 
}
 
?>
 
<?php
$query = "SELECT playerID, firstName, lastName, yearSchool, team, position, shoot, x_description, location FROM players2010 WHERE location = 'HM GREEN'  ORDER BY position ";
displaytable($query);
?>

Open in new window

0
NerdsOfTechTechnology ScientistCommented:
added comments and made the variable more human readable...
<?php
include './phpLibrary/config10.php';
include './phpLibrary/opendb10.php';


function displaytable($query) {
$row_num=0; // count rows to output row number
        
// execute query
$result = mysql_query($query) or die ("Error in query: $query ".mysql_error());
 
// see if any rows were returned
if (mysql_num_rows($result) > 0) {
        // yes
        // print them one after another
                
                //              birthdate       street  city    state   zip                                                     
                //              BIRTHDATE       STREET  CITY    STATE   ZIP                                                     
        echo "<table cellpadding=0 border=0 align='center' class='tableBody'>";
      
            echo "<tr class='tableBody'><th>Row</th><th>FIRST NAME</th><th>LAST NAME</th><th>YEAR SCHOOL</th><th>HS TEAM</th><th>POSITION</th><th>SHOOT</th><th>X_DESCRIPTION</th><th>TEAM</th><th>UPDATE</th></tr>";
      
            echo "<tr class='resultsBody'><th colspan='9'><hr></tr>";
                
        while(list($playerID, $firstName, $lastName, $yearSchool, $team, $position, $shoot, $x_description, $location) = mysql_fetch_row($result)) {
                echo "<tr class='resultsBody'><form action=\"./update3.php\" method=\"POST\">";
                        echo "<input type=\"hidden\" size=\"4\" name=\"playerID\" value=\"$playerID\">";
                            $row_num++; // increment row number before displaying
                            echo "<td>$row_num</td>";
                            echo "<td>$firstName</td>";
                echo "<td>$lastName</td>";
                        echo "<input type=\"hidden\" size=\"4\" name=\"lastName\" value=\"$lastName\">";

                echo "<td>$yearSchool</td>";
                echo "<td>$team</td>";
                echo "<td>$position</td>";
                                 echo "<td>$shoot</td>";
                                 echo "<td>$x_description</td>";
                                 echo "<td><input type=\"text\" size=\"10\" name=\"location\" value=\"$location\"></td>";
                
                
                
                                 echo "<td><input type=\"submit\" name=\"submit\" value=\"Update\"></td>";

                echo "</form></tr>";
        }
        echo "</table>";
}
 
else {
        // no
        // print status message
        echo "No rows found!";
}
 
// free result set memory
mysql_free_result($result);
 

 
}
 
?>
 
<?php
$query = "SELECT playerID, firstName, lastName, yearSchool, team, position, shoot, x_description, location FROM players2010 WHERE location = 'HM GREEN'  ORDER BY position ";
displaytable($query);
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
franco_cAuthor Commented:
Thank you to all of you that submitted options, I am sure if I kept at it I could weed out the small issues, but this answer did what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.