MichaelGallagher Posted November 2, 2007 Share Posted November 2, 2007 Hi guys, This is a problem i have been sweating over for the past month... it has been a hard time finding a solution, and just when i think i have it, something else goes wrong.. Basically i have dynamic content from a MYSQL database, and i would like to output this data into form elements arranged in a format resembling a table that has a FIXED HEADER and FIXED LEFT COLUMN. After trying several methods, i have settled on one, as shown in this link: Demo - http://www.cubido.at/Portals/7/docs/Blog/Karin/HTML%20and%20Javascript/ResizeColumns/Table.htm Documentation - http://www.cubido.at/Blog/tabid/176/EntryID/94/Default.aspx Download Files (source) - http://www.cubido.at/Portals/7/docs/Blog/Karin/HTML%20and%20Javascript/ResizeColumns.zip Now, the demo works fine, but implementing dynamic content, with looping is doing my head in.. i'm sure its possible, and i know im probably missing something simple, but I think maybe staring at this code for too long has hurt my brain. Here is my code MIXED with the Fixed Header code (forgive the roundabout solutions; i know its messy and inefficient): (NOTE: there are a lot of formatting variables set, so just ignore them or delete them.. I thought probably best to include ALL the code AS IT IS.... ALSO the conn.php is just setting up the sessions and other crap.. assume there are NO URL variables passed initially at all.) <?php include('conn.php'); // check for valid user if ( verify() ) { ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title>Fixed Headers</title> <link type="text/css" href="Style.css" rel="stylesheet" /> <script type="text/javascript" src="Script.js"></script> </head> <body> <h1>Fixed Headers (resize window to view scrollbars)</h1> <table class="scrollTable" id="scrollTable" cellpadding="0" cellspacing="0" border="0"> <tr> <td> <div class="corner"> <table cellpadding="0" cellspacing="0" border="0"> <tr> <th><div><a href='portfolio.php?sort=CompanyName&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br /><nowrap>Company Name</nowrap><br /><a href='portfolio.php?sort=CompanyName&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> </tr> </table> </div> </td> <td> <div class="headerRow"> <table cellpadding="0" cellspacing="0" border="0"> <tr> <th><div><a href='portfolio.php?sort=LastCallDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />Last<br />Call Date<br /><a href='portfolio.php?sort=LastCallDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div>Telstra<br />Monthly<br />Spend</div></th> <th><div>Other<br />Telco<br />Spend</div></th> <th><div>Telstra<br />Fixed<br />Lines</div></th> <th><div>Other Carrier<br />Fixed Lines</div></th> <th><div>Phone<br />System</div></th> <th><div>Installing<br />Dealer</div></th> <th><div><a href='portfolio.php?sort=EstPhoneSysPurchase&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />Est. Phone<br />Sys. Purchase<br /><a href='portfolio.php?sort=EstPhoneSysPurchase&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div>Fixed<br />Contract<br />Type</div></th> <th><div><a href='portfolio.php?sort=FixedContractStartDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />Fixed<br />Contract<br />Start Date<br /><a href='portfolio.php?sort=FixedContractStartDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div><a href='portfolio.php?sort=FixedContractEndDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />Fixed<br />Contract<br />End Date<br /><a href='portfolio.php?sort=FixedContractEndDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div>Single<br />Bill</div></th> <th><div>Telstra<br />Mobile</div></th> <th><div><a href='portfolio.php?sort=FirstMobileContractEndDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />First Mobile<br />Contract<br />End Date<br /><a href='portfolio.php?sort=FirstMobileContractEndDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div>Dealer<br />Code</div></th> <th><div>Other<br />Carriers'<br />Mobiles</div></th> <th><div>Other<br />Carrier</div></th> <th><div><a href='portfolio.php?sort=FirstContractEndDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'height='12' width='11'border='0'></a><br />First Contract<br />End Date<br /><a href='portfolio.php?sort=FirstContractEndDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div>Wireless<br />BB</div></th> <th><div>Industry<br />Type</div></th> <th><div>Employees</div></th> <th><div>Sites</div></th> <th><div><a href='portfolio.php?sort=LastMAECallDate&order=asc'><img src='../images/ascend.gif' height='12' width='11'border='0'></a><br />Last MAE Call Date<br /><a href='portfolio.php?sort=LastMAECallDate&order=desc'><img src='../images/descend.gif' height='12' width='11'border='0'></a></div></th> <th><div> </div></th> </tr> </table> </div> </td> </tr> <?php $strDB=mysql_connect($strServer,$strUser,$strPwd)or die ('Error connecting to mysql'); $database=mysql_select_db("$strDatabase",$strDB); $update=$_GET['update']; $cpid=$_GET['ClickPosID']; //begin updats statement if ($update=='yes'){ if ($LastCallDate=='') { $LastCallDate=('0000-00-00'); } else { $LastCallDate=$_POST["LastCallDate"]; } $TelstraMonthlySpend=$_POST["TelstraMonthlySpend"]; $OtherTelcoSpend=$_POST["OtherTelcoSpend"]; $TelstraFixed=$_POST["TelstraFixed"]; $OtherCarrierFixed=$_POST["OtherCarrierFixed"]; $PhoneSystem=$_POST["PhoneSystem"]; $InstallingDealer=$_POST["InstallingDealer"]; $EstPhoneSysPurchase=$_POST["EstPhoneSysPurchase"]; $FixedContractType=$_POST["FixedContractType"]; $FixedContractStartDate=$_POST["FixedContractStartDate"]; $FixedContractEndDate=$_POST["FixedContractEndDate"]; $SingleBill=$_POST["SingleBill"]; $TelstraMobile=$_POST["TelstraMobile"]; $FirstMobileContractEndDate=$_POST["FirstMobileContractEndDate"]; $DealerCode=$_POST["DealerCode"]; $OtherCarriersMobiles=$_POST["OtherCarriersMobiles"]; $OtherCarrier=$_POST["OtherCarrier"]; $FirstContractEndDate=$_POST["FirstContractEndDate"]; $WirelessBB=$_POST["WirelessBB"]; $IndustryType=$_POST["IndustryType"]; $Employees=$_POST["Employees"]; $Sites=$_POST["Sites"]; $LastMAECallDate=$_POST["LastMAECallDate"]; $sql="UPDATE portfolios SET LastCallDate='$LastCallDate', TelstraMonthlySpend=$TelstraMonthlySpend, OtherTelcoSpend=$OtherTelcoSpend, TelstraFixed=$TelstraFixed, OtherCarrierFixed=$OtherCarrierFixed, PhoneSystem='$PhoneSystem', InstallingDealer='$InstallingDealer', EstPhoneSysPurchase='$EstPhoneSysPurchase', FixedContractType='$FixedContractType', FixedContractStartDate='$FixedContractStartDate', FixedContractEndDate='$FixedContractEndDate', SingleBill='$SingleBill', TelstraMobile=$TelstraMobile, FirstMobileContractEndDate='$FirstMobileContractEndDate', DealerCode='$DealerCode', OtherCarriersMobiles=$OtherCarriersMobiles, OtherCarrier='$OtherCarrier', FirstContractEndDate='$FirstContractEndDate', WirelessBB='$WirelessBB', IndustryType='$IndustryType', Employees=$Employees, Sites=$Sites, LastMAECallDate='$LastMAECallDate' WHERE portfolios.ClickPosID = $cpid;"; $result = mysql_db_query($strDatabase, $sql) or die ("A MySQL error has occurred.<br />Your Query: " . $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error()); } // end update statement else { // GET URL SORT VARIABLES $index=$_GET['index']; //the index choice, 0-9 or a-z or blank $sortval= $_GET['sort']; $sortord= $_GET['order']; if ($index=='blank') { $indexquery=(' c.CompanyName =\' \' AND '); } else { $indexquery=(' c.CompanyName LIKE \''.$index.'%\' AND '); } if ($index=='0'){ $indexquery=(' c.CompanyName LIKE \'0%\' OR c.CompanyName LIKE \'1%\' OR c.CompanyName LIKE \'2%\' OR c.CompanyName LIKE \'3%\' OR c.CompanyName LIKE \'4%\' OR c.CompanyName LIKE \'5%\' OR c.CompanyName LIKE \'6%\' OR c.CompanyName LIKE \'7%\' OR c.CompanyName LIKE \'8%\' OR c.CompanyName LIKE \'9%\' AND '); } if ($index=='all'){ $indexquery=(''); } if (!$sortval) { $sortquery=(' ORDER BY CompanyName Asc'); } else { // Colour the sort by column grey if ($sortval=='CompanyName') { $sortCompanyName= ('bgcolor=#dddddd'); } if (!$sortval=='') { $sortquery= (' ORDER BY '. $sortval . ' ' . $sortord); } else { $sortquery= (''); } } $sql="SELECT * FROM portfolios p inner join customers c on p.ClickPosID = c.ClickPosID WHERE$indexquery c.SalesRep=$who$sortquery"; $result = mysql_db_query($strDatabase, $sql) or die ("A MySQL error has occurred.<br />Your Query: " . $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error()); while ($r = mysql_fetch_array($result)) { $yesWirelessBB=(''); $noWirelessBB=(''); $yesSingleBill=(''); $noSingleBill=(''); // Portfolios table fields $LastCallDate=$r["LastCallDate"]; $TelstraMonthlySpend=$r["TelstraMonthlySpend"]; $OtherTelcoSpend=$r["OtherTelcoSpend"]; $TelstraFixed=$r["TelstraFixed"]; $OtherCarrierFixed=$r["OtherCarrierFixed"]; $PhoneSystem=$r["PhoneSystem"]; $InstallingDealer=$r["InstallingDealer"]; $EstPhoneSysPurchase=$r["EstPhoneSysPurchase"]; $FixedContractType=$r["FixedContractType"]; $FixedContractStartDate=$r["FixedContractStartDate"]; $FixedContractEndDate=$r["FixedContractEndDate"]; $SingleBill=$r["SingleBill"]; $TelstraMobile=$r["TelstraMobile"]; $FirstMobileContractEndDate=$r["FirstMobileContractEndDate"]; $DealerCode=$r["DealerCode"]; $OtherCarriersMobiles=$r["OtherCarriersMobiles"]; $OtherCarrier=$r["OtherCarrier"]; $FirstContractEndDate=$r["FirstContractEndDate"]; $WirelessBB=$r["WirelessBB"]; $IndustryType=$r["IndustryType"]; $Employees=$r["Employees"]; $Sites=$r["Sites"]; $LastMAECallDate=$r["LastMAECallDate"]; // Companies table fields $CompanyName=$r["CompanyName"]; $ClickPosID=$r["ClickPosID"]; $CustClass = $r["CustClass"]; $TelstraExec = $r["TelstraExec"]; $ContactTitle = $r["ContactTitle"]; $ContactFName = $r["ContactFName"]; $ContactLName = $r["ContactLName"]; $ContactJobTitle = $r["ContactJobTitle"]; $ContactPhone = $r["ContactPhone"]; $ContactFax = $r["ContactFax"]; $ContactMobile = $r["ContactMobile"]; $ContactEmail = $r["ContactEmail"]; $SiteAddress = $r["SiteAddress"]; $SiteSuburb = $r["SiteSuburb"]; $SiteState = $r["SiteState"]; $SitePC = $r["SitePC"]; $PostalAddress = $r["PostalAddress"]; $PostalSuburb = $r["PostalSuburb"]; $PostalState = $r["PostalState"]; $PostalPC = $r["PostalPC"]; $CIDN = $r["CIDN"]; $color = ($i % 2) ? '#FFFFFF' : '#CCCCCC'; if ($CustClass==''){ $CustClass=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $CustClass=$CustClass; } if ($TelstraExec==''){ $TelstraExec=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $TelstraExec=$TelstraExec; } if ($ContactTitle==''){ if ($ContactFName==''){ if ($ContactLName==''){ $Contactmissing=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); } } } else { $contact=($ContactTitle.' '.$ContactFName.' '.$ContactLName); } if ($ContactJobTitle==''){ $ContactJobTitle=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $ContactJobTitle=$ContactJobTitle; } if ($ContactPhone=='0'){ $ContactPhone=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $ContactPhone=$ContactPhone; } if ($ContactFax=='0'){ $ContactFax=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $ContactFax=$ContactFax; } if ($ContactMobile=='0'){ $ContactMobile=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $ContactMobile=$ContactMobile; } if ($ContactEmail==''){ $ContactEmail=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $ContactEmail=$ContactEmail; } if ($SiteAddress==''){ if ($SiteSuburb==''){ $fullSiteAddress=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); } } if ($PostalAddress==''){ if ($PostalSuburb==''){ $fullPostalSuburb=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); } } if ($CIDN=='0'){ $CIDN=('<span style=\'font-size:12px; font-family:Arial, Helvetica, sans-serif; color:#ff9999; font-weight:bold\'>-missing-</span>'); }else { $CIDN=$CIDN; } $LastCallDate=strtotime($LastCallDate); // Get Todays Date and make UNIX Timestamp $today = strtotime(date('Y-m-d')); // Calculate 1 month ago from today $one_month = 31 * 24 * 60 * 60; $one_month_ago = ($today - $one_month); // Calculate 2 months ago from today $two_month = 2 * 31 * 24 * 60 * 60; $two_month_ago = ($today - $two_month); // Check if the LastCallDate is a 1, 2 or more months ago and if so colour Green, Yellow or Red respectively if (!$LastCallDate) { $LastCallDateinputcolour=(' style="background-color:#ccccff"'); } else { if ($LastCallDate >= $one_month_ago) { $LastCallDateinputcolour=(' style="background-color:#ccffcc"'); } if ($LastCallDate < $one_month_ago) { if ($LastCallDate >= $two_month_ago) { $LastCallDateinputcolour=(' style="background-color:#ffffcc"'); } } if ($LastCallDate < $two_month_ago) { $LastCallDateinputcolour=(' style="background-color:red"'); } } // Convert UNIX Timestamp back to yyyy-mm-dd date if (!$LastCallDate=='') { $LastCallDate=(date('Y-m-d', (int) $LastCallDate)); } // Check if the LastMAECallDate is a 1, 2 or more months ago and if so colour Green, Yellow or Red respectively if ($LastMAECallDate=='0000-00-00'){ $LastMAECallDate=''; } else { $LastMAECallDate=strtotime($LastMAECallDate); } if (!$LastMAECallDate) { $LastMAECallDateinputcolour=(' style="background-color:#ccccff"'); } else { if ($LastMAECallDate >= $one_month_ago) { $LastMAECallDateinputcolour=(' style="background-color:#ccffcc"'); } if ($LastMAECallDate < $one_month_ago) { if ($LastMAECallDate >= $two_month_ago) { $LastMAECallDateinputcolour=(' style="background-color:#ffffcc"'); } } if ($LastMAECallDate < $two_month_ago) { $LastMAECallDateinputcolour=(' style="background-color:red"'); } } // Convert UNIX Timestamp back to yyyy-mm-dd date if (!$LastMAECallDate=='') { $LastMAECallDate=(date('Y-m-d', (int) $LastMAECallDate)); } echo " <form class='niceform' action='portfolio.php?ClickPosID=$ClickPosID&update=yes' method='POST' name='submit' id='submit' enctype='multipart/form-data'> <tr> <td valign='top'> <div class='headerColumn'> <table cellpadding='0' cellspacing='0' border='0'> <tr> <th><div> <a href='#$ClickPosID' id='$ClickPosID'></a><nowrap>$CompanyName</nowrap> </div></th> </tr> </table> </div> </td> <td> <div class='body'> <table cellpadding='0' cellspacing='0' border='0'> <tr style='background-color:$color;'><th><div> <td><div><input$LastCallDateinputcolour size='7' type='text' name='LastCallDate' value='$LastCallDate'><br />yyyy-mm-dd</div></td> <td><div><input size='3' type='text' id='TelstraMonthlySpend' name='TelstraMonthlySpend' value='$TelstraMonthlySpend'></div></td> <td><div><input size='3' type='text' id='OtherTelcoSpend' name='OtherTelcoSpend' value='$OtherTelcoSpend'></div></td> <td><div><input size='3' type='text' id='TelstraFixed' name='TelstraFixed' value='$TelstraFixed'></div></td> <td><div><input size='3' type='text' id='OtherCarrierFixed' name='OtherCarrierFixed' value='$OtherCarrierFixed'></div></td> <td><div><input size='10' type='text' id='PhoneSystem' name='PhoneSystem' value='$PhoneSystem'></div></td> <td><div><input size='10' type='text' id='InstallingDealer' name='InstallingDealer' value='$InstallingDealer'></div></td> <td><div><input size='7' type='text' id='EstPhoneSysPurchase' name='EstPhoneSysPurchase' value='$EstPhoneSysPurchase'><br />yyyy-mm-dd</div></td> <td><div><input size='10' type='text' id='FixedContractType' name='FixedContractType' value='$FixedContractType'></div></td> <td><div><input size='7' type='text' id='FixedContractStartDate' name='FixedContractStartDate' value='$FixedContractStartDate'><br />yyyy-mm-dd</div></td> <td><div><input size='7' type='text' id='FixedContractEndDate' name='FixedContractEndDate' value='$FixedContractEndDate'><br />yyyy-mm-dd</div></td> "; if ($SingleBill=='yes'){ $yesSingleBill=('selected'); } if ($SingleBill=='no'){ $noSingleBill=('selected'); } if ($SingleBill==''){ $noselectSingleBill=('selected'); } echo " <td><div><select size='1' class='width_320' id='SingleBill' name='SingleBill'> <option value='' $noselectSingleBill>Select</option> <option value='yes' $yesSingleBill>Yes</option> <option value='no' $noSingleBill>No</option> </select></div></td> <td><div><input size='5' type='text' id='TelstraMobile' name='TelstraMobile' value='$TelstraMobile'></div></td> <td><div><input size='10' type='text' id='FirstMobileContractEndDate' name='FirstMobileContractEndDate' value='$FirstMobileContractEndDate'><br />yyyy-mm-dd</div></td> <td><div><input size='6' type='text' id='DealerCode' name='DealerCode' value='$DealerCode'></div></td> <td><div><input size='4' type='text' id='OtherCarriersMobiles' name='OtherCarriersMobiles' value='$OtherCarriersMobiles'></div></td> <td><div><input size='15' type='text' id='OtherCarrier' name='OtherCarrier' value='$OtherCarrier'></div></td> <td><div><input size='10' type='text' id='FirstContractEndDate' name='FirstContractEndDate' value='$FirstContractEndDate'><br />yyyy-mm-dd</div></td> "; if ($WirelessBB=='yes'){ $yesWirelessBB=('selected'); } if ($WirelessBB=='no'){ $noWirelessBB=('selected'); } if ($WirelessBB==''){ $noselectWirelessBB=('selected'); } echo " <td><div><select size='1' id='WirelessBB' name='WirelessBB'> <option value='' $noselectWirelessBB>Select</option> <option value='yes' $yesWirelessBB>Yes</option> <option value='no' $noWirelessBB>No</option> </select></div></td> <td><div><input size='15' type='text' id='IndustryType' name='IndustryType' value='$IndustryType'></div></td> <td><div><input size='3' type='text' id='Employees' name='Employees' value='$Employees'></div></td> <td><div><input size='3' type='text' id='Sites' name='Sites' value='$Sites'></div></td> <td><div><input$LastMAECallDateinputcolour size='7' type='text' id='LastMAECallDate' name='LastMAECallDate' value='$LastMAECallDate'><br />yyyy-mm-dd</div></td> <td><div><input type='submit' name='Save Changes' onclick=\"if(confirm('Are you sure you want to save changes?')){return true}else{return false}\" value='Save' style='background:#00CC66; color:#FFFFFF; font-weight:bold' /></div></td> </div></th> </tr> </table> </td> </tr> </form>"; $i++; } } mysql_close($strDB); echo " </table> </div> </body> </html> <script language=\"javascript\"> paddingLeft = 10; paddingRight = 10; paddingTop = 1; paddingBottom = 1; ScrollTableRelativeSize( document.getElementById(\"scrollTable\"), 50, 120); /*ScrollTableAbsoluteSize( document.getElementById(\"scrollTable\"), 500, 550);*/ </script>"; } ?> I suppose you can delete the crap you don't need... ANY help is MUCH appreciated! Kind regards, Michael Gallagher Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted November 2, 2007 Share Posted November 2, 2007 Your rproblem lies in the fact that your data lies in a different table to your headers.... now there is LOTS of code in there that looks unnecessary... you don't need a div in each cell, nor the inline-styles nor lots of other stuff... you could save your self A LOT of hassel by getting this right before you proceed... A well structured table will look like this <table summary="summary of data in table - what it means etc."> <thead> <tr> <th>header1</th> <th>header2</th> </tr> </thead> <tbody> <tr> <td>Data 1</td> <td>Data 2</td> </tr> </tbody> </table> I your case all you need is to put you loop inside the <tbody> tags and echo out each row on ech loop... to style them use css (you can give the <th> elements an id or a class (make sure teh id is NOT anywwhere else on teh page) and you can give the <td> elements a class - each to be used in the style sheet to add the style you want. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.