如何返回具有特定字段值的所有数据库行

Question

Hey, I have a database table called "projects" with the fields 'id', 'locationid', 'name' and 'year'. I query the database to retrieve all the values and store them in $data, before passing them into the relevant view.

In what part of the website, I have created a timeline (made of list items generated with a foreach). In this timeline, I was to associate projects with their corresponding 'year' value. So under 1999, I would find all the projects for 1999.

Right now I have an ugly hacked solution where inside the foreach which I use to generate the timeline I have ANOTHER foreach that goes through the projects table and checks to see if the 'year' field matches the current timeline year, and if so adds it.

Is there a more elegant way to query the $data I passed into the view (and therefore database field values) based on finding specific strings?

Essentially, if I could write it how I want to express it, I'm looking for something like this:

<?php foreach $year_range as $timeline_year : ?>
   <div class="projects_menu">
      <?php foreach $projects WHERE $projects->$year EQUAL $timeline_year : ?>
      <?php echo $projects->$name ?>
      <?php endforeach ?>
   </div>
<?php endforeach ?>

Answer 1

I may be misunderstanding the question, but it seems like you could do something like:

SELECT * FROM projects WHERE ($condition) ORDER BY YEAR ASC, name ASC

And then simply output the results in PHP:

foreach ($projects as $project) {
    /* output $project */
}

or

foreach ($year_range as $year) {
    /* output timeline, inserting projects if any occur in that year */
}

$condition might be something like:

projects.year >= $start_year AND projects.year <= $end_year

or

projects.year IN ($year_range)

or

projects.year IN ($year_range) and project.name LIKE '%$search_string%'

You'll have to be more specific about "finding specific strings" as I'm not sure where that would come into play in this problem.