Reformatting Category & Row Output

[phplearner]

I am trying to convert from a straight HTML (manual) format to output (display) some list data and keep it updated in a MySQL DB.

A sample snippet of the current format with .css is at:

 [url]http://www.xgenesis.com/sample.html[/url]

I am doing something similar for another list but with manual table headings and separate SQL code for EACH of the different category sections which is very time consuming to maintain. Here is what does work (snippet):

  $result = mysql_query("$sql")
       or die ("Couldn't execute query.");

$numofrows = mysql_num_rows($result);	   

  /* Display results in a table */
  echo "<center><strong><font size=\"+1\">AUDIENCE</font></strong></center>";
  echo "<center><TABLE cellspacing=\"2\" cellpadding=\"2\" BORDER=\"0\">\n";
  echo "<TR bgcolor=\"#009933\"><TD align=\"center\"><B>Audience 

Type</B></TD><TD><B>Total</B></TD></TR>\n";
  for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); //get a row from our result set
	$audience = $row['audience'];
    $total = $row['count'];
	if($i % 2) { //this means if there is a remainder
        echo "<TR bgcolor=\"#66cc66\">\n";  

    } else { //if there isn't a remainder we will do the else
        echo "<TR bgcolor=\"#98cc66\">\n";  // Original #ccffcc
    }
    echo "<TD align=\"left\">$audience</TD><TD align=\"right\">$total</TD>\n";
    echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE></center>\n";
echo "<br>";

What I can not figure out is how to hack up this code to make a separate table display EACH TIME the "Category" name/type CHANGES as per the sample.html layout (you can view the source).

I plan to SORT the MySQL output by Category name 1st ASC to simplify keeping the final tables in Alphabetic Category Name sequence, but am really stuck on how to do get a new separate table each time the category type name changes.

The simple db table will have the following columns:

 categorytype  
 vendorname
 phonenumber

So, each time $categorytype changes, a new formatted table needs to display for only those category $vendornames & $phonenumbers.

I got this far trying to hack-up the other script:

$result = mysql_query("$sql")
or die ("Couldn't execute query.");

$numofrows = mysql_num_rows($result);

for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
$categorytype = $row['categorytype'];
$vendorname = $row['vendorname'];
$phonenumber = $row['phonenumber'];

// ARRRRGGHHHHHH ... don't know where to go from here ;-(

Thanks for any help.

[nnichols]

Try this. I think it should work.

<?

$result = mysql_query("$sql") 
or die ("Couldn't execute query."); 

$numofrows = mysql_num_rows($result); 

$i = 0;

while($row = mysql_fetch_array($result)) { 
$i++;

$categorytype = $row['categorytype']; 
$vendorname = $row['vendorname']; 
$phonenumber = $row['phonenumber'];

if ($categorytype != $prev_cat && $prev_cat == NULL) {

// this condition occurs when it is the first iteration 'coz $prev_cat hasn't been set yet
echo "<TABLE>\n";
echo '<TR bgcolor="#009933"><TH colspan="2">' . $categorytype . "</TH></TR>\n";
echo '<tr><td>' . $vendorname . '</td><td>' . $phonenumber . "</td></tr>\n";

} elseif ($categorytype == $prev_cat) {

echo '<tr><td>' . $vendorname . '</td><td>' . $phonenumber . "</td></tr>\n";
// check to see if this is the last row of the result set - if so, close last table
if ($i == $numofrows) {
	echo "</TABLE>\n";
}

} else {
	echo "</TABLE>\n";
	echo "<TABLE>\n";
	echo '<TR bgcolor="#009933"><TH colspan="2">' . $categorytype . "</TH></TR>\n";
	echo '<tr><td>' . $vendorname . '</td><td>' . $phonenumber . "</td></tr>\n";

}


$prev_cat = $row['categorytype'];
}
?>

Hope this helps

[phplearner]

Hi, nnichols:

Thanks for your input/efforts here, but still a loose screw somewhere...I've re-tweaked & retweaked and now got everything to work EXCEPT the 'alternating row colors' when there are multiple vendors in a category.

I just put together a quick test db (dbname=catventel):

--TEST Table1

drop table if exists table1;
CREATE TABLE table1 (
ID int(11) NOT NULL auto_increment primary key,
categorytype varchar(30) default NULL,
vendorname varchar(40) default NULL,
phonenumber varchar(15) default NULL
);

INSERT INTO table1 (ID, categorytype, vendorname, phonenumber) VALUES
(1,'APPRAISERS','Appraiser Name','[phone]'),
(2,'CARPET CLEANING','Carpet Cleaner Name','[phone]'),
(3,'CHIMNEY & FIREPLACE','Chimney Service #1','[phone]'),
(4,'CHIMNEY & FIREPLACE','Chimney Service #2','[phone]'),
(5,'CHIMNEY & FIREPLACE','Chimney Service #3','[phone]');

RE:

<?
?>
<html>
<head>
<title>RECOMMENDED VENDORS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<STYLE type=text/css>FORM {
	DISPLAY: inline
}
TABLE {
    background-color: #000000;
}
TH {
    background-color: #000000;
	FONT-SIZE:10pt; COLOR: #FFFFFF; FONT-FAMILY: Verdana, Tahoma, Arial
}
TR {
	FONT-SIZE: 8.5pt; COLOR: #000000; FONT-FAMILY: Verdana, Tahoma, Arial
}
TD {
	FONT-SIZE: 8.5pt; COLOR: #000000; FONT-FAMILY: Verdana, Tahoma, Arial
}

.solidborder {
	BORDER-RIGHT: #999999 1px solid; BORDER-TOP: #999999 1px solid; BORDER-LEFT: #999999 1px solid; BORDER-BOTTOM: #999999 1px solid
}
.rowcolheader {
	background-color: #c1c1c1;
	font-family: Arial, Helvetica, sans-serif;
	font-size:14;
	font-weight: bold;
	text-decoration: none;
	vertical-align: middle; 
	color: #FFFFFF;
    LETTER-SPACING:  1px;
}
.datafontreg {
	FONT-SIZE: 8.5pt; COLOR: #000000; FONT-FAMILY: Verdana, Tahoma, Arial;
	text-decoration: none;
}


.datarow1 {
	BACKGROUND-COLOR: #f2f2f2
}
.datarow2 {
	BACKGROUND-COLOR: #d8d8d8
}
</STYLE>
</head>

<body>

<center>
<strong><font color="#000000" size="+2" face="Arial, Helvetica, sans-serif">RECOMMENDED VENDORS</font></strong>
</center>

<br>

<?

  $user="me";
  $host="localhost";
  $password="";
  $database="catventel";

  $connection = mysql_connect($host,$user,$password)
       or die ("couldn't connect to server");
  $db = mysql_select_db($database,$connection)
       or die ("Couldn't select database");

  $sql = "SELECT * FROM table1 ORDER BY categorytype ASC";

  $result = mysql_query("$sql")
       or die ("Couldn't execute query.");

$result = mysql_query("$sql") 
or die ("Couldn't execute query."); 

$numofrows = mysql_num_rows($result); 

$i = 0;

while($row = mysql_fetch_array($result)) { 
$i++;

$categorytype = $row['categorytype']; 
$vendorname = $row['vendorname']; 
$phonenumber = $row['phonenumber'];

if ($categorytype != $prev_cat && $prev_cat == NULL) {

// this condition occurs when it is the first iteration 'coz $prev_cat hasn't been set yet
echo "<CENTER><TABLE width=\"330\" cellspacing=\"1\" cellpadding=\"2\" BORDER=\"0\">\n";
echo "<TH colspan=\"2\">" . $categorytype . "</TH>\n";
echo "<TR class=\"rowcolheader\"><TD width=\"174\" align=\"center\"><B>VENDOR NAME</B></TD><TD width=\"135\" align=\"center\"><B>TELEPHONE</B></TD></TR>";
echo "<TR class=\"datarow1\"><td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td></TR>\n";

} elseif ($categorytype == $prev_cat) {

echo "<TR class=\"datarow1\"><td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td></tr>\n";
// check to see if this is the last row of the result set - if so, close last table
if ($i == $numofrows) {
    echo "</TABLE></CENTER>\n";
	echo "<br>";
}

} else {
    echo "</TABLE></CENTER>\n";
    echo "<CENTER><TABLE width=\"330\" cellspacing=\"1\" cellpadding=\"2\" BORDER=\"0\">\n";
    echo "<TH colspan=\"2\">" . $categorytype . "</TH>\n";
	echo "<TR class=\"rowcolheader\"><TD width=\"174\" align=\"center\"><B>VENDOR NAME</B></TD><TD width=\"135\" align=\"center\"><B>TELEPHONE</B></TD></TR>";
    echo "<TR class=\"datarow1\"><td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td></tr>\n";
	echo "<br>";

}

$prev_cat = $row['categorytype'];
}


?>

</body>
</html>
<?
?>

Needs to look like: http://www.xgenesis.com/sample.html

...the 'alternating row colors' in the last table example

Thanks if you can figure out what's missing 'cuz I can't !!!

[phplearner]

YIPPPEEEEEEE...it looks like I finally got it hacked to work.

Tnx again for the initial direction.

if ($categorytype != $prev_cat && $prev_cat == NULL) {

// this condition occurs when it is the first iteration 'coz $prev_cat hasn't been set yet
echo "<CENTER><TABLE width=\"330\" cellspacing=\"1\" cellpadding=\"2\" BORDER=\"0\">\n";
echo "<TH colspan=\"2\">" . $categorytype . "</TH>\n";
echo "<TR class=\"rowcolheader\"><TD width=\"174\" align=\"center\"><B>VENDOR NAME</B></TD><TD width=\"135\" align=\"center\"><B>TELEPHONE</B></TD></TR>";
echo "<TR class=\"datarow1\"><td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td></TR>\n";

} elseif ($categorytype == $prev_cat) {
    $r++;
    if($r % 2) { //this means if there is a remainder
        echo "<TR class=\"datarow1\">\n";  

    } else { //if there isn't a remainder we will do the else
        echo "<TR class=\"datarow2\">\n";
    }

echo "<td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td>\n";
// check to see if this is the last row of the result set - if so, close last table
if ($i == $numofrows) {
    echo "</TABLE></CENTER>\n";
	echo "<br>";
}

} else {
    echo "</TABLE></CENTER>\n";
    echo "<CENTER><TABLE width=\"330\" cellspacing=\"1\" cellpadding=\"2\" BORDER=\"0\">\n";
    echo "<TH colspan=\"2\">" . $categorytype . "</TH>\n";
	echo "<TR class=\"rowcolheader\"><TD width=\"174\" align=\"center\"><B>VENDOR NAME</B></TD><TD width=\"135\" align=\"center\"><B>TELEPHONE</B></TD></TR>";
    echo "<TR class=\"datarow1\"><td align=\"left\">" . $vendorname . "</td><td align=\"center\">" . $phonenumber . "</td></tr>\n";
	echo "<br>";
	$r="1";

}

$prev_cat = $row['categorytype'];
}