mazola Posted May 18, 2010 Share Posted May 18, 2010 Good day I am fairly new to php and have a problem. I have two tables in mysql, that is dividendparams and dividendhistorydetails. dividendparams has fields paramid,paramname while dividendhistorydetails has fields detailsid,paramid,year,dividenditem. I would like to query data from both tables and display as follows; Year1 Year2 Year3 Year4 Paramname1 Dividenditem1 Dividenditem2 Dividenditem3 Dividenditem4 Paramname2 Dividenditem5 Dividenditem6 Dividenditem7 Dividenditem8 Paramname3 Dividenditem9 Dividenditem10 Dividenditem11 Dividenditem12 Paramname4 Dividenditem13 Dividenditem14 Dividenditem15 Dividenditem16 Paramname5 Dividenditem17 Dividenditem18 Dividenditem19 Dividenditem20 the number of paramname and year can increase, thereby increasing the dividenditem too. Any help on this will be appreciated. Thank you Quote Link to comment https://forums.phpfreaks.com/topic/202190-create-multidimensional-array-from-sql-query-and-display-in-html-table/ Share on other sites More sharing options...
RichardRotterdam Posted May 18, 2010 Share Posted May 18, 2010 I don't think you need a multi dimensional array for that a simple 2d array would be sufficient. So far your table structure seems like so: dividendparams - paramid - paramname dividendhistorydetails - detailsid - paramid (fk?) - year - dividenditem Here the field paramid in table dividendhistorydetails looks like a foreign key. Lookup how to use JOINS in sql. Quote Link to comment https://forums.phpfreaks.com/topic/202190-create-multidimensional-array-from-sql-query-and-display-in-html-table/#findComment-1060270 Share on other sites More sharing options...
Psycho Posted May 18, 2010 Share Posted May 18, 2010 NOt tested, so there may be some syntax issues: <?php //Query the data $query = "SELECT p.paramname, h.year, h.dividenditem FROM `dividendparams` p JOIN `dividendhistorydetails` as h ON p.paramid = h.paramid ORDER BY p.paramname, h.year" $result = mysql_query($query); //Create arrays for the values $params = array(); $years = array(); //Proces the results while($row = mysql_fetch_assoc($result)) { $params[$row['paramname']][$row['year']] = $row['dividenditem']; if(!in_array($row['year'], $years) { $years[] = $row['year']; } } sort($years); //Create header row $output = "<tr>\n"; $output .= "<th>Parameter</th>\n"; foreach($years as $year) { $output .= "<th>{$year}</th>\n"; } $output .= "</tr>\n"; //Create the data rows foreach($params as $paramName => $data) { $output .= "<td>{$paramName}</td>\n"; foreach($years as $year) { $dividendItem = $data[$year]; $output .= "<td>{$dividendItem}</td>\n"; } } ?> <html> <body> <table border="1"> <?php echo $output; ?> </table> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/202190-create-multidimensional-array-from-sql-query-and-display-in-html-table/#findComment-1060285 Share on other sites More sharing options...
mazola Posted May 19, 2010 Author Share Posted May 19, 2010 Hello mjdamato, Thanks a million times! It worked just the way I wanted. Quote Link to comment https://forums.phpfreaks.com/topic/202190-create-multidimensional-array-from-sql-query-and-display-in-html-table/#findComment-1060439 Share on other sites More sharing options...
Psycho Posted May 19, 2010 Share Posted May 19, 2010 My pleasure. In the future, please mark topics as solved when you get a solution. There is a green button on the bottom left of posts that you start for this purpose. I've marked this one for you. Quote Link to comment https://forums.phpfreaks.com/topic/202190-create-multidimensional-array-from-sql-query-and-display-in-html-table/#findComment-1060557 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.