I alway get SQLSTATE[HY000]: General error: 25 bind or column index out of range

Question

I don't understand I alsways get this error: SQLSTATE[HY000]: General error: 25 bind or column index out of range and the code seems identical

so here is the get function:

function get_task_list($filter = null){
  include 'connection.php';
  $sql = 'SELECT tasks.*, projects.title as project FROM tasks'
       . ' JOIN projects ON tasks.project_id = projects.project_id';

  $where = "";
  if(is_array($filter)){
    switch($filter[0]) {
      case 'project':
        $where = " WHERE projects.project_id = ?";
        break;
      case 'category':
        $where = " WHERE category = ?";
        break;
    }
  }

  $orderBy = " ORDER BY date DESC";
  if($filter){
    $orderBy = " ORDER BY projects.title ASC, date DESC";
  }


  try{
    $results = $db->prepare($sql . $where . $orderBy);
    if(is_array($filter)){
      $results->bindValue(1, $filter[1]);
    }
    $results->execute();
  } catch (Exception $e) {
      echo 'Error!: ' . $e->getMessage() . '<br>';
      return array();
  }
  return $results->fetchAll(PDO::FETCH_ASSOC);
}

and the page where everything is called:

<?php
require 'inc/functions.php';

$page = "reports";
$pageTitle = "Reports | Time Tracker";
$filter = "all";

if(!empty($_GET['filter'])){
  $filter = explode(':', filter_input(INPUT_GET, 'filter', FILTER_SANITIZE_STRING));
}

include 'inc/header.php';
?>
<div class="col-container page-container">
    <div class="col col-70-md col-60-lg col-center">
        <div class="col-container">
            <h1 class='actions-header'>Reports</h1>
            <form class="form-container form-report">
              <label for="filter">Filter:</label>
              <select id="filter" name="filter">
                <option value="">Select one</option>
                <optgroup label="Project">
                <?php
                foreach(get_project_list() as $item){
                  echo "<option value='project:" . $item['project_id'] . "'>";
                  echo $item['title'] . "</option>\n";
                }
                ?>
                </optgroup>
                <optgroup label="Category">
                  <option value="Category:billable">Billable</option>
                  <option value="Category:Charity">Charity</option>
                  <option value="Category:Personnal">Personnal</option>
                </optgroup>
              </select>
              <input class="button" type="submit" value="Run">
            </form>
        </div>
        <div class="section page">
            <div class="wrapper">
                <table>
                  <?php 
                    $total = $project_id = $project_total = 0;
                    $tasks = get_task_list($filter);
                    foreach($tasks as $item){
                      if($project_id != $item['project_id']) {
                        $project_id = $item['project_id']; 
                        //$project_total += $item['time'];
                        echo "<thead>\n";
                        echo "<tr>\n";
                        echo '<th>' . $item['project'] . "</th>\n";
                        echo "<th>Date</th>\n";
                        echo "<th>Time</th>\n";
                        echo "</tr>\n";
                        echo "</thead>\n";
                      }
                      $total += $item['time'];
                      $project_total += $item['time'];
                      echo "<tr>\n";
                      echo "<td>" . $item['title'] . "</td>\n";
                      echo "<td>" . $item['date'] . "</td>\n";
                      echo "<td>" . $item['time'] . "</td>\n";
                      echo "</tr>\n";
                      if(next($tasks)['project_id'] != $item['project_id']) {
                          echo "<tr>\n";
                          echo "<th class='project-total-label' colspan='2'>Project Total</th>\n";
                          echo "<th class='project-total-number'>$project_total</th>\n";
                          echo "</tr>\n";
                          $project_total = 0;
                        }
                    }
                  ?>
                    <tr>
                        <th class='grand-total-label' colspan='2'>Grand Total</th>
                        <th class='grand-total-number'><?php echo $total; ?></th>
                    </tr>
                </table>
            </div>
        </div>
    </div>
</div>

<?php include "inc/footer.php"; ?>

Anybody can find the problem?

Answer 1 · 2018-04-09T23:28:46Z

April 9, 2018 11:28pm

 <option value="Category:billable">Billable</option>
                  <option value="Category:Charity">Charity</option>
                  <option value="Category:Personnal">Personnal</option>

value attribute - category should be lower case instead of upper case.

Answer 2 · 2024-03-19T13:39:31Z

March 19, 2024 1:39pm

Appreciate this is many years later, but am running through some PHP stuff for interest and I just hit the same bug.

I solved it by applying / extending Henry's code (thanks Henry!). To confirm, you need to set the value attributes inside the <option> tags to be EXACTLY the same as whatever is used in the switch. You can still use e.g. 'Category', but your switch would need to use uppercase as well, e.g.:

switch( $filter[0] ) {
  case 'project':
    $where = ' WHERE projects.project_id = ?';
    break;
  case 'Category': // this line is the difference!
    $where = ' WHERE category = ?';
    break;
  default:
    break;
}

You could also use strtolower on the value of $filter[0] to resolve this problem, e.g.:

switch( strtolower( $filter[0] ) ) {
  case 'project':
    $where = ' WHERE projects.project_id = ?';
    break;
  case 'category':
    $where = ' WHERE category = ?';
    break;
  default:
    break;
}

Hope that helps!

Welcome to the Treehouse Community

Looking to learn something new?

Christian Pineault

Christian Pineault

I alway get SQLSTATE[HY000]: General error: 25 bind or column index out of range

2 Answers

Henry Lin

Henry Lin

michaeldavies3

michaeldavies3