How to return all database rows with certain field value

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 ?&g开发者_高级运维t;
      <?php endforeach ?>
   </div>
<?php endforeach ?>

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.

A complete dynamic solution would be to:

first sql query

SELECT `year`
FROM `projects`
GROUP BY `year`
ORDER BY `year` ASC

Then loop through that

while($row = mysql_fetch_array($r)) {
echo '<div class="projects_menu">';
echo $row['year'].' Projects';
$q2="SELECT * FROM `projects` WHERE `year`='{$year}' ORDER BY `name`";
//db connection $r2
while($row2 = mysql_fetch_array($r2)) {
 echo $row2['name'];
}
echo '</div>';
}

So that way it doesn't matter how many years you have to get.

Im pretty sure this is what you were after

Have you looked into CodeIgniter's Active Record syntax? Maybe something like:

$this->db->select("*")->from("projects")->where("year <=", $date);
$query = $this-<db->get();

That should return all of the records that are less than or equal to a $data argument.