?
Solved

row counter for a php MySQL result

Posted on 2010-03-23
16
Medium Priority
?
428 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:franco_c
  • 7
  • 4
  • 2
  • +2
16 Comments
 

Expert Comment

by:RooiVos
ID: 28420307
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
 
LVL 14

Expert Comment

by:shobinsun
ID: 28420499
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
 

Author Comment

by:franco_c
ID: 28420602
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 14

Expert Comment

by:shobinsun
ID: 28420623
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
 
LVL 6

Expert Comment

by:nasirbest
ID: 28421038
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
 

Author Comment

by:franco_c
ID: 28421144
counter is just putting "0+1"

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

what am I doing wrong?
0
 
LVL 14

Expert Comment

by:shobinsun
ID: 28421241
HI,

put :

$count = 0;


outside the function: displaytable().

Try it.

0
 
LVL 14

Expert Comment

by:shobinsun
ID: 28421364
If it is not taking inside the function,

declare it as global outside the function as:

global count ;
count = 0;
0
 

Author Comment

by:franco_c
ID: 28422110
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
 
LVL 14

Expert Comment

by:shobinsun
ID: 28422342
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
 
LVL 14

Expert Comment

by:shobinsun
ID: 28422421
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
 
LVL 6

Expert Comment

by:nasirbest
ID: 28423237
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
 
LVL 14

Expert Comment

by:shobinsun
ID: 28423309
Hi,

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

function displaytable($query) {
....

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

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

Try this.

Regards.
0
 
LVL 21

Expert Comment

by:NerdsOfTech
ID: 28425181
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
 
LVL 21

Accepted Solution

by:
NerdsOfTech earned 2000 total points
ID: 28425303
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
 

Author Closing Comment

by:franco_c
ID: 31706432
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question