Printing an array with some MySQL records

Question

Not sure if the subject is suitable for the question.

I have a table which holds some data with year info as shown below.

table mydata
+----+------------+----------+
| id | year       | some_data|
+----+------------+----------+
|  1 | 2011       | x        |
|  2 | 2012       | y        |
|  2 | 2009       | x        |
|  2 | 2007       | z        |
|  1 | 2009       | b        |
|  3 | 2011       | a        |
|  2 | 2007       | n        |
|  3 | 2006       | h        |
|  2 | 2007       | t        |
+----+------------+----------+

Here is my problem. I'm trying to fetch data of certain id from mydata table with year information. However I need to show records in range of certain years, like past 6 years and next 6 years of present.

For example;

id = 2

+----+------------+----------+
| id | year       | some_data|
+----+------------+----------+
|  2 | 2007       | z        |
|  2 | 2007       | n        |
|  2 | 2009       | x        |
|  2 | 2012       | y        |
+----+------------+----------+

And I need to show data like that in HTML table structure:

+------------+----------+
| year       | some_data|
+------------+----------+
| 2006       |          |
| 2007       | z        |
|            | n        |
|            | t        |
| 2008       |          |
| 2009       | x        |
| 2010       |          |
| 2011       |          |
| 2012       | y        |
| 2013       |          |
| 2014       |          |
| 2015       |          |
| 2016       |          |
| 2017       |          |
| 2018       |          |
+------------+----------+

I don't know in which year there are duplicated records and which years are absent. So I always try my solutions with more than one queries or lots of for loops. And doesn't work as I desired.

And ideas?

eggyal · Accepted Answer · 2012-09-07 12:08:53Z

1

Fetch sorted data from the database, then construct your output table in PHP by looping over the years that you wish to output.

For example, using a PDO object $dbh:

$qry = $dbh->prepare('
  SELECT   year, some_data
  FROM     mydata
  WHERE    year BETWEEN :start AND :end
  ORDER BY year
');

$qry->bindParam(':start', $range_start);
$qry->bindParam(':end'  , $range_end  );

$current_year = intval(date('Y'));
$range_start  = $current_year - 6;
$range_end    = $current_year + 6;

if ($qry->execute()) {

  $row = $qry->fetch();

  echo '<table><tr><th scope="col">year</th><th scope="col">some_data</th></tr>';
  for ($y = $range_start; $y <= $range_end; $y++) {
    echo "<tr><th scope=\"row\">$y</th><td><ul>";
    while ($row && $row['year'] == $y) {
      echo '<li>' . htmlentities($row['some_data']) . '</li>';
      $row = $qry->fetch();
    }
    echo "</ul></td></tr>";
  }
  echo '</table>';

}

edited Sep 7, 2012 at 12:08

answered Sep 7, 2012 at 11:53

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tuze Over a year ago

$row = $qry->fetch(); in while statement! Don't know why i didn't even think about it. That saved my day. Thanks!

Collectives™ on Stack Overflow

Printing an array with some MySQL records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related