idontkno Posted April 21, 2012 Share Posted April 21, 2012 How would I force a certain table format and then populate it with the SQL results in their respective columns and rows? i.e. +------------------+----------+----------+----------+ | | Col 1 | Col 2 | Col 3 | +-------------------+----------+----------+----------+ | row 1 lab data | 16777216 | | 573 | +-------------------+----------+----------+----------+ | row 2 lab data | 23454235 | 87247247 | 65743 | +-------------------+----------+----------+----------+ | row 3 lab data | 16777216 | 47832364 | | +-------------------+----------+----------+----------+ Currently I'm just using a while loop to populate it horizontally, but that doesn't work for row 1. <?PHP while($row = mysql_fetch_array($result) { ?> blah blah blah html here <?PHP echo $row[0]; ?> more html <?PHP echo $row[1]; ?> and so forth <?PHP } ?> It ends up producing this: +------------------+----------+----------+----------+ | | Col 1 | Col 2 | Col 3 | +-------------------+----------+----------+----------+ | row 1 lab data | 16777216 | 573 | | +-------------------+----------+----------+----------+ | row 2 lab data | 23454235 | 87247247 | 65743 | +-------------------+----------+----------+----------+ | row 3 lab data | 16777216 | 47832364 | | +-------------------+----------+----------+----------+ Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/ Share on other sites More sharing options...
requinix Posted April 21, 2012 Share Posted April 21, 2012 First I have a couple questions: why are there three columns and what are they for? Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339255 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 First I have a couple questions: why are there three columns and what are they for? Time range. There could be any number of columns but 3-4 is the norm. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339256 Share on other sites More sharing options...
kicken Posted April 21, 2012 Share Posted April 21, 2012 I would put your data into an array structure which represents your table and just leave the empty columns blank in the array. Then you can make a simple loop to show the array as a table. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339257 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 I would put your data into an array structure which represents your table and just leave the empty columns blank in the array. Then you can make a simple loop to show the array as a table. I thought of that, but I can't think of a way to do it without still having it fudge up and leave the empty keys last in the array. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339258 Share on other sites More sharing options...
PFMaBiSmAd Posted April 21, 2012 Share Posted April 21, 2012 The array for each row that mysql_fetch_array returns will have entries for each column that is listed in your select statement, even if the value the query produces is a null value or an empty string. If a html table cell is empty, you should output a as the content in the <td></td> tags for that cell so that the browser will render the cell with any borders or style you are using, but that would not cause the the example 3rd cell value to be in the 2nd column in the output. About the only way you could get the 3rd cell's value to be in the 2nd column is if you are not outputting the <td></td> tags for each cell. If you are only outputting two <td></td> <td></td> for that row, there's no way that the browser will ever know the second one is for the 3rd column in the table. What's your actual code that is producing the incorrect output table. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339285 Share on other sites More sharing options...
xyph Posted April 21, 2012 Share Posted April 21, 2012 Just a heads up - CSS should handle the display of empty cells now, not the browser. (IE6/7 nope) http://www.quirksmode.org/css/tables.html#emptycells And the default should be 'show' Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339329 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 The array for each row that mysql_fetch_array returns will have entries for each column that is listed in your select statement, even if the value the query produces is a null value or an empty string. If a html table cell is empty, you should output a as the content in the <td></td> tags for that cell so that the browser will render the cell with any borders or style you are using, but that would not cause the the example 3rd cell value to be in the 2nd column in the output. About the only way you could get the 3rd cell's value to be in the 2nd column is if you are not outputting the <td></td> tags for each cell. If you are only outputting two <td></td> <td></td> for that row, there's no way that the browser will ever know the second one is for the 3rd column in the table. What's your actual code that is producing the incorrect output table. The problem is the returned SQL result only contains 3 columns rather than a full 4 because I don't have the data for the 3rd column. <?PHP if(!$r101 = mysql_query("SELECT * FROM labc101 WHERE date=$curDay ORDER BY period ASC", $link)) { throw new Exception("Catastrophic Failure.", 10); } mysql_close($link); ?> <tbody valign="top"> <tr> <td style="color: #5E9DC8; font-weight: bold;">C101</td> <?PHP while($c101 = mysql_fetch_array($r101)) {?> <td><?PHP echo $c101[0]; ?><br /><?PHP echo $c101[1]; ?><br /><?PHP echo $c101[2]; ?></td> <?PHP }?> </tr> Just a heads up - CSS should handle the display of empty cells now, not the browser. (IE6/7 nope) http://www.quirksmode.org/css/tables.html#emptycells And the default should be 'show' Well that would be nice except the target browser is IE 7... (I know, sucks doesn't it). Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339364 Share on other sites More sharing options...
litebearer Posted April 21, 2012 Share Posted April 21, 2012 show us your db table structure Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339367 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 show us your db table structure CREATE TABLE `labc101` ( `result1` varchar(16) NOT NULL, `result2` varchar(6) NOT NULL, `result3` varchar(15) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `period` int(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339370 Share on other sites More sharing options...
litebearer Posted April 21, 2012 Share Posted April 21, 2012 Being a firm believer in minimalist testing, try this example <?PHP $query = "SELECT * FROM labc101 WHERE date='$curDay' ORDER BY period ASC"; $result = mysql_query($query, $link) or die {throw new Exception("Catastrophic Failure.", 10); if(mysql_num_rows<1) { echo "No records"; exit(); } ?> <table> <tr><td></td><td>result1</td><td>result2</td><td>result2</td><td>id</td><td>date</td><td>period</td></tr> <?PHP while($row = mysql_fetch_array($result)) { ?> <tr> <td>C101</td> <td><?PHP echo $row[0]; ?></td> <td><?PHP echo $row[1]; ?></td> <td><?PHP echo $row[2]; ?></td> <td><?PHP echo $row[3]; ?></td> <td><?PHP echo $row[4]; ?></td> <td><?PHP echo $row[5]; ?></td> </tr> <?PHP } echo "</table>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339376 Share on other sites More sharing options...
PFMaBiSmAd Posted April 21, 2012 Share Posted April 21, 2012 The code you posted last, is putting the three values (result1,result2,result3) from each database row into one <td></td> cell in the html table row. Is that what you want to do? That doesn't match anything you have stated or shown. What you have implied you want is each of those three result values (some of which might be empty or null) to be in its own <td></td> table cell so that each database table row makes one html table row. This is what I replied about and would work if you were putting <td></td> tags around each value - echo "<td>{$c101[0]}</td><td>{$c101[1]}</td><td>{$c101[2]}</td>"; So far, your description and examples don't show what you are doing, what data you have and don't have, what result you are getting, and what result you expect. Either show real or mock-up data for enough rows in your database table and exactly where you want each value from each database row to be output in the html table. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339379 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 The code you posted last, is putting the three values (result1,result2,result3) from each database row into one <td></td> cell in the html table row. Is that what you want to do? That doesn't match anything you have stated or shown. What you have implied you want is each of those three result values (some of which might be empty or null) to be in its own <td></td> table cell so that each database table row makes one html table row. This is what I replied about and would work if you were putting <td></td> tags around each value - echo "<td>{$c101[0]}</td><td>{$c101[1]}</td><td>{$c101[2]}</td>"; So far, your description and examples don't show what you are doing, what data you have and don't have, what result you are getting, and what result you expect. Either show real or mock-up data for enough rows in your database table and exactly where you want each value from each database row to be output in the html table. Yes that is what I want. The problem is the cell row is being created horizontally but unable to handle a gap in the data. The SQL result is returning 3 rows to fill across 3 HTML columns while I have 4 columns to fill. You can put it as <td>$result</td> but that makes absolutely no difference. It'll produce 9 HTML columns while I have 12. How would I dump mock-up data inside without rewriting the rest of the table data? Essentially what I'm looking for is a way to have a table filled with the results from four separate experiments and grouped by the time intervals they were done at. The problem is when the results are submitted and there's no data for a given time period, i.e. period 3, and there is data for the next one, i.e. period 4, the results from period 4 incorrectly go under the period 3 heading. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339388 Share on other sites More sharing options...
PFMaBiSmAd Posted April 21, 2012 Share Posted April 21, 2012 Yes that is what I want. Both the code that litebearer posted and the line of code I posted will do what you want. However, your description you just wrote is confusing rows with columns (columns go across the page and rows go down the page.) I suggest you actually try what has been shown or suggested by litebearer and myself to see if you can get the output that you want. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339397 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 Yes that is what I want. Both the code that litebearer posted and the line of code I posted will do what you want. However, your description you just wrote is confusing rows with columns (columns go across the page and rows go down the page.) I suggest you actually try what has been shown or suggested by litebearer and myself to see if you can get the output that you want. His produces this ([] indicates mysql returned result's row # from SELECT): ------------------------------ C101 | r1[1] | r2[1] | r3[1] | ------------------------------ C101 | r1[2] | r2[2] | r3[2] | ------------------------------ C101 | r1[3] | r2[3] | r3[3] | ------------------------------ While I want this: ------------------------------ C101 | r1[1] | r2[1] | r3[1] | | r1[2] | r2[2] | r3[2] | | r1[3] | r2[3] | r3[3] | ------------------------------ However, since I have potential gaps in data, sometimes I get this (r3[2] in r2[2]'s spot): ------------------------------ C101 | r1[1] | r2[1] | r3[1] | | r1[2] | r3[2] | | | r1[3] | r2[3] | r3[3] | ------------------------------ My last ditch attempt was to just use mysql_result and manually enter the row and field, except that results in an incredibly long script generation. EDIT: My problem isn't in how my table is being generated, that part works fine; my problem is in getting the darn gap to correctly place itself between time intervals 2 and 4 rather than incorrectly dumping it at the end since MySQL doesn't return a series of empty strings or NULLs. If it did that my table would work fine, the problem is it isn't so I need PHP to make up for that and somehow insert a series of blanks in the array. EDIT2: Perhaps a screenshot would help a lot more than my horrible descriptions and drawings Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339416 Share on other sites More sharing options...
idontkno Posted April 21, 2012 Author Share Posted April 21, 2012 Sorry, I can't edit my last post so I have to double post. The attachment shows the bad cell that should be under period 4 instead of 3. As you can see, it'll work fine if the entire row has all the data required, but the problem is in the beginning posts of the data where it won't correctly go under the right period and potentially screw up everything. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339425 Share on other sites More sharing options...
litebearer Posted April 22, 2012 Share Posted April 22, 2012 This statement.... However, since I have potential gaps in data, sometimes I get this (r3[2] in r2[2]'s spot): leads me suspect the real issue is how you populate your db table. based upon the example I provided (and we will address the grouping later) r3[2] should NEVER be in r2[2]'s spot unless your insert query did not leave r2[2] empty. ie name, street, city - if my insert, for whatever reason, puts city in the street field, then I will have problems. Time to show us how and why you are inserting data into your db table. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339434 Share on other sites More sharing options...
idontkno Posted April 22, 2012 Author Share Posted April 22, 2012 This statement.... However, since I have potential gaps in data, sometimes I get this (r3[2] in r2[2]'s spot): leads me suspect the real issue is how you populate your db table. based upon the example I provided (and we will address the grouping later) r3[2] should NEVER be in r2[2]'s spot unless your insert query did not leave r2[2] empty. ie name, street, city - if my insert, for whatever reason, puts city in the street field, then I will have problems. Time to show us how and why you are inserting data into your db table. Why would it not? The example you provided was a completely different table than the one I was describing. You're populating the table horizontally basing the cell contents with field contents. I'm populating it with row contents instead of field contents. At this point I think you and I are describing different problems. Your solution handles non-existent field data, when I am looking for one that handles non-existent row data. For example, think of a hotel booking page where you have gaps in data where nobody has booked a certain room or whatever. All I've essentially done is replace rooms with data. And fyi, my insert is fine. I haven't even bothered making an insert page yet, and as of right now I'm manually dumping in the mock results via PMA so I know for a fact that my data is not being inserted into the wrong location. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339435 Share on other sites More sharing options...
PFMaBiSmAd Posted April 22, 2012 Share Posted April 22, 2012 Re: Reply #15 in this thread, where you posted some actual information about what you are trying to achieve and how it relates to the data and to the information in the thread. You would need to TEST the `period` value in each row and use that value to determine when to output empty <td></td> tags. You would need to store the last period value (initialize it before the start of the loop) and when each row is fetched, loop while the last period value is less than the row's period value. What is the lowest value for the period column, 1 ? Your output has legends like C101, C104, C106, ...,C109 and in the query you posted you have a table name labc101 that seems to correspond to the C101 data only. If you truly have separate tables for each of the C101, C104, C106, ..., C109 blocks of data, meaning you are executing a separate query for each of those blocks of data, STOP NOW. You need to fix your database design. You should NOT put similar data into separate tables. You will figuratively kill your database server trying to execute all those queries on one page to retrieve the data. It also means you are hard-coding repetitive blocks of code, that only differer in the source name of the data. So, not only will this take the page longer to retrieve the data, it will also take you longer to write the code, copy/paste blocks of code, make changes to each block, test to find the typo's in the code, and make repetitive changes to each block every time you must fix something. You need all the related data in one table with a column that indicates the data source C101, C104, C106, ...,C109. You will then use one query to retrieve the data you want in the order that you want it and then simply loop over that data with one set of code and output the information the way you want (you will simply detect a change in the source column to start a new section. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339441 Share on other sites More sharing options...
idontkno Posted April 22, 2012 Author Share Posted April 22, 2012 Re: Reply #15 in this thread, where you posted some actual information about what you are trying to achieve and how it relates to the data and to the information in the thread. You would need to TEST the `period` value in each row and use that value to determine when to output empty <td></td> tags. You would need to store the last period value (initialize it before the start of the loop) and when each row is fetched, loop while the last period value is less than the row's period value. What is the lowest value for the period column, 1 ? Your output has legends like C101, C104, C106, ...,C109 and in the query you posted you have a table name labc101 that seems to correspond to the C101 data only. If you truly have separate tables for each of the C101, C104, C106, ..., C109 blocks of data, meaning you are executing a separate query for each of those blocks of data, STOP NOW. You need to fix your database design. You should NOT put similar data into separate tables. You will figuratively kill your database server trying to execute all those queries on one page to retrieve the data. It also means you are hard-coding repetitive blocks of code, that only differer in the source name of the data. So, not only will this take the page longer to retrieve the data, it will also take you longer to write the code, copy/paste blocks of code, make changes to each block, test to find the typo's in the code, and make repetitive changes to each block every time you must fix something. You need all the related data in one table with a column that indicates the data source C101, C104, C106, ...,C109. You will then use one query to retrieve the data you want in the order that you want it and then simply loop over that data with one set of code and output the information the way you want (you will simply detect a change in the source column to start a new section. Yes the lowest value would be 1 and the highest would be 4 for now. I realize that it's horribly inefficient, but it's due to PHP's inability to retrieve the data as a multidimensional array. There's no way to actually sort the result cluster that's returned. I realize I could just leftjoin all the tables together or even dump it all into one table and add another column for the source, but in the end I'm still running the exact same number of queries. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339445 Share on other sites More sharing options...
kicken Posted April 22, 2012 Share Posted April 22, 2012 I realize that it's horribly inefficient, but it's due to PHP's inability to retrieve the data as a multidimensional array. There's no way to actually sort the result cluster that's returned. I realize I could just leftjoin all the tables together or even dump it all into one table and add another column for the source, but in the end I'm still running the exact same number of queries. Just because there may not be some magic built in function to do what you want does not mean PHP is incapable of doing it. You can store the data into an array of any dimension size you want as you fetch it from the database. If necessary, you can sort it using one of the many sorting functions, usort can handle pretty much any type of sort you want. This is just an example of how one might produce the table you want, while using a more proper database layout. Depending on your requirements you might even break it down further to having one row per result rather than using result1, result2, and result3 columns. <?php $sql = 'SELECT labName, result1, result2, result3, period FROM labResults ORDER BY labName, period'; $res = mysql_query($sql); $labResults = array(); while ($row=mysql_fetch_assoc($res)){ $lab = $row['labName']; $period = $row['period']; $labResults[$lab][$period] = array( 1=> $row['result1'], 2=> $row['result2'], 3=> $row['result3'] ); } ?> <table> <thead> <tr> <th>Period 1</th> <th>Period 2</th> <th>Period 3</th> <th>Period 4</th> </tr> </thead> <tbody> <?php foreach ($labResults as $labName=>$periods): ?> <tr> <th><?php echo $labName; ?></th> <?php //Use a for loop so we account for all four periods, even if one is missing in a particular lab result set for ($period=1; $period <= 4; $period++){ echo '<td>'; if (isset($periods[$period])){ foreach ($ as $resNum=>$resData){ echo 'Result Set '.$resNum.': '.$resData; } } else { echo ' '; } echo '</td>'; } ?> </tr> <?php endforeach; ?> </tbody> </table> Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339451 Share on other sites More sharing options...
PFMaBiSmAd Posted April 22, 2012 Share Posted April 22, 2012 ^^^ And the best point of the code kicken kindly posted is the design is now driven by the data and you don't ever have to change the code to add or remove any of the labName values. All you have to do is add or remove data in the database table. If you want or need to change the format or style of the output, you only have to change it in one set of code. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339456 Share on other sites More sharing options...
PFMaBiSmAd Posted April 22, 2012 Share Posted April 22, 2012 in the end I'm still running the exact same number of queries. No. You will only have one query that retrieves the data you want in the order that you want it. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339457 Share on other sites More sharing options...
idontkno Posted April 22, 2012 Author Share Posted April 22, 2012 I realize that it's horribly inefficient, but it's due to PHP's inability to retrieve the data as a multidimensional array. There's no way to actually sort the result cluster that's returned. I realize I could just leftjoin all the tables together or even dump it all into one table and add another column for the source, but in the end I'm still running the exact same number of queries. Just because there may not be some magic built in function to do what you want does not mean PHP is incapable of doing it. You can store the data into an array of any dimension size you want as you fetch it from the database. If necessary, you can sort it using one of the many sorting functions, usort can handle pretty much any type of sort you want. This is just an example of how one might produce the table you want, while using a more proper database layout. Depending on your requirements you might even break it down further to having one row per result rather than using result1, result2, and result3 columns. <?php $sql = 'SELECT labName, result1, result2, result3, period FROM labResults ORDER BY labName, period'; $res = mysql_query($sql); $labResults = array(); while ($row=mysql_fetch_assoc($res)){ $lab = $row['labName']; $period = $row['period']; $labResults[$lab][$period] = array( 1=> $row['result1'], 2=> $row['result2'], 3=> $row['result3'] ); } ?> <table> <thead> <tr> <th>Period 1</th> <th>Period 2</th> <th>Period 3</th> <th>Period 4</th> </tr> </thead> <tbody> <?php foreach ($labResults as $labName=>$periods): ?> <tr> <th><?php echo $labName; ?></th> <?php //Use a for loop so we account for all four periods, even if one is missing in a particular lab result set for ($period=1; $period <= 4; $period++){ echo '<td>'; if (isset($periods[$period])){ foreach ($ as $resNum=>$resData){ echo 'Result Set '.$resNum.': '.$resData; } } else { echo ' '; } echo '</td>'; } ?> </tr> <?php endforeach; ?> </tbody> </table> Thanks! I tried something similar to split the array but for some odd reason the array data kept getting jumbled up. By the way, what version of PHP are you using that allowed you to use just '$' in the foreach loop? Anyways, I touched it up a little bit to fit (Your foreach ended up making 12 copies). if (isset($periods[$period])){ for ($key=1; $key <=3; $key++){ switch($key){ case 1: echo 'Set 1: '; break; case 2: echo 'Set 2: '; break; case 3: echo 'Set 3: '; break; } echo $periods[$period][$key].'<br />'; Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339471 Share on other sites More sharing options...
kicken Posted April 22, 2012 Share Posted April 22, 2012 By the way, what version of PHP are you using that allowed you to use just '$' in the foreach loop? Anyways, I touched it up a little bit to fit (Your foreach ended up making 12 copies). I just wrote the code off the top of my head, didn't test it at all. Bound to be typos which is all that was. Quote Link to comment https://forums.phpfreaks.com/topic/261353-populating-a-table-with-gaps-in-the-sql-results/#findComment-1339477 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.