[Resolved] How do I number returned table rows?

blueroo

I wish to pull some data from MySQL and insert it into numbered table rows. For the life of me I can't get it to work.
This is what I am trying to explain:
1 data from 1st record
data from 1st record
data from 1st record
data from 1st record
data from 1st record

2 data from 2nd record
data from 2nd record
data from 2nd record
data from 2nd record
data from 2nd record

and so on.

Here is my code snippet:

[code=php]$result = @mysql_query("SELECT DISTINCT SupplierName, MIBPostalAddress, MIBStreetAddress, LocationLocalBranch, LocalBranchPostCode, MIBTelephone, MIBFax FROM miris_Suppliers

WHERE MICCMember = 'yes'
ORDER BY miris_Suppliers.SupplierName");

if (!$result) {
	echo ( "<h4>Error performing query: " . mysql_error() . "</h4>");
exit();
}

	if ($row = mysql_fetch_array($result) )

{

	do
{ 		// Declare each variable
		$suppliername = $row["SupplierName"];
		$mibpostaladdress = $row["MIBPostalAddress"];
		$mibstreetaddress = $row["MIBStreetAddress"];
		$locationlocalbranch = $row["LocationLocalBranch"];
		$localbranchpostcode = $row["LocalBranchPostCode"];
		$mibtelephone = $row["MIBTelephone"];
		$mibfax = $row["MIBFax"];

		printf("<table width=580 align=center cellspacing=0 cellpadding=0 border=0>");
		printf("<tr><td rowspan=5 valign=top><br />[B](number goes here)[/B]</td><td><b>%s</b></td></tr>", htmlspecialchars($row["SupplierName"]));
		if (strlen($mibpostaladdress) >0) {printf(" <tr><td>%s, %s. %s</td></tr>", htmlspecialchars($row["MIBPostalAddress"]), htmlspecialchars($row["LocationLocalBranch"]), $row["LocalBranchPostCode"]);}
		if (strlen($mibstreetaddress) >0) {printf(" <tr><td>%s, %s. %s</td></tr>", htmlspecialchars($row["MIBStreetAddress"]), htmlspecialchars($row["LocationLocalBranch"]), $row["LocalBranchPostCode"]);}
		if (strlen($mibtelephone) >0) {printf(" <tr><td>Telephone: %s</td></tr>", htmlspecialchars($row["MIBTelephone"]));}
		if (strlen($mibfax) >0) {printf(" <tr><td>Fax: %s</td></tr>", htmlspecialchars($row["MIBFax"]));}
		printf("</table>");

}

[/code]

Could kind coder help me out here?

Thanks

blueroo

steadyguy

This is how I would code the inner if statement (somewhat simplified for readability):

if ($row = mysql_fetch_array($result)) {
    $countRows = mysql_num_rows($result); //find out how many results you have
    print '<table>';
    for ($i = 0; $i < $countRows; $i++) {
        extract($row); //gives you variables named after your columns with the values from your rows
        print "<tr><td rowspan=\"5\">$i</td><td>$SupplierName</td></tr>";
        print "<tr><td>$MIBPostalAddress<br />$MIBStreetAddress</td></tr>";
        print "<tr><td>$LocationLocalBranch<br />$LocalBranchPostCode</td></tr>";
        print "<tr><td>Telephone: $MIBTelephone</td></tr>";
        print "<tr><td>Fax: $MIBFax</td></tr>";
    }
}

blueroo

Thanks steadyguy!

I carried out your suggestions by changing my code and the row numbering works from ZERO (I'll have to fix that), but now $SupplierName returns the first record 189 times instead of 189 different records.

This is my changed code:

		if ($row = mysql_fetch_array($result) )

{

	$countRows = mysql_num_rows($result); // find out how many results you have

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

		extract($row);

		printf("<table width=580 align=center cellspacing=0 cellpadding=0 border=0>");
		printf("<tr><td rowspan=5 valign=top width=30>$i</td><td><b>$SupplierName</b></td></tr>");
		if (strlen($MIBPostalAddress) >0) {printf("<tr><td>$MIBPostalAddress, $LocationLocalBranch. $LocalBranchPostCode</td></tr>");}
		if (strlen($MIBStreetAddress) >0) {printf("<tr><td>$MIBStreetAddress, $LocationLocalBranch. $LocalBranchPostCode</td></tr>");}
		if (strlen($MIBTelephone) >0) {printf(" <tr><td>Telephone: $MIBTelephone</td></tr>");}
		if (strlen($MIBFax) >0) {printf(" <tr><td>Fax: $MIBFax</td></tr>");}
		printf("</table><br />");

Unfortunately, I need to print the table tags for every record as the rowspan can be less than 5 at times.

Any more ideas?

blueroo

Pig

Maybe you left if off, but you don't have a closing }

Personally, I would use a different route:

$result = mysql_query("some sql stuff here");
$i=0;
while( $row = mysql_fetch_array($result) )
{
     echo(" 
          <table> <!--all your content stuff--> 
          $i
          $row[column_name]
     ");
     $i++;
}

steadyguy

blueroo ... that's because I forgot to include the mysql_fetch_array() inside the loop as well. Sorry about that.

$query = 'SELECT DISTINCT SupplierName, MIBPostalAddress, MIBStreetAddress, LocationLocalBranch, LocalBranchPostCode, MIBTelephone, MIBFax FROM miris_Suppliers WHERE MICCMember = 'yes' ORDER BY  miris_Suppliers.SupplierName'; 
$connection = mysql_connect('host', 'user', 'password');
if($connection) {
    mysql_select_db('dbName');
    if ($result = mysql_query($query)) {
        $countRows = mysql_num_rows($result);
        print '<table>'; 
        for ($i = 0; $i < $countRows; $i++) {
            $row = mysql_fetch_array($result);
            extract($row);
            //print the table ...
        }
        print '</table>';
    }
    else
        print 'Query invalid, or returned an empty result set!';
}
else
    print 'Unable to connect to Database!';

blueroo

Thanks to those who replied to this thread.

The completed script:

<?php

$dbcnx = @mysql_connect("$hostName", "$userName", "$passWord");
 if (!$dbcnx) {
    echo( "<p>Unable to connect to the " .
          "database server at this time.</p>" );
    exit();
  	}

mysql_select_db("$databaseName");

$query = "SELECT DISTINCT SupplierName, MIBPostalAddress, MIBStreetAddress, LocationLocalBranch, LocalBranchPostCode, MIBTelephone, MIBFax FROM miris_Suppliers      

WHERE MICCMember = 'yes' 
ORDER BY miris_Suppliers.SupplierName";

if (!$query) {
	echo ( "<h4>Error performing query: " . mysql_error() . "</h4>");
		exit();
	}

	if ($result = mysql_query($query)) {

		$countRows = mysql_num_rows($result); 

    	for ($i = 0; $i+n < $countRows; $n++) { 
        $row = mysql_fetch_array($result); 
        extract($row);

			printf("<table width=580 align=center cellspacing=0 cellpadding=0 border=0>");
			printf("<tr><td rowspan=5 valign=top width=30>" . ++$i . "</td><td><b>$SupplierName</b></td></tr>");
			if (strlen($MIBPostalAddress) >0) {printf("<tr><td>$MIBPostalAddress, $LocationLocalBranch. $LocalBranchPostCode</td></tr>");}
			if (strlen($MIBStreetAddress) >0) {printf("<tr><td>$MIBStreetAddress, $LocationLocalBranch. $LocalBranchPostCode</td></tr>");}
			if (strlen($MIBTelephone) >0) {printf(" <tr><td>Telephone: $MIBTelephone</td></tr>");}
			if (strlen($MIBFax) >0) {printf(" <tr><td>Fax: $MIBFax</td></tr>");}
			printf("</table><br />");

			}

	}

mysql_free_result($result);
mysql_close($dbcnx);

?>

blueroo