How to return all database rows with certain field value.

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'm not 100% clear on what you want to show, but almost certainly the best way to do it is to have a query that returns the data that you want in the order that you want it.

Ideally you want to query the database, and then format the response to fit what you want to show. If you can't do that, take a look at your query to see if it's really returns what you want.

Answer 2

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

Answer 3

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.

Answer 4

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.