Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP Integrating PHP with Databases Limiting Records in SQL Setting LIMITs

janeporter
PLUS
janeporter
Courses Plus Student 23,471 Points

getting a "bad query SQLSTATE[HY000]: General error: 1 near "(": syntax error" when I try clicking on a category.

here is my get_catalog_count() function in my functions.php file:

function get_catalog_count($category = null) {
  $category = strtolower($category);
  include("connection.php");

  try {
    $sql = "SELECT COUNT(media_id) FROM Media";
    if (!empty($category)) {
      $result = $db->prepare($sql . "WHERE LOWER(category) = ?");
      $result->bindParam(1,$category,PDO::PARAM_STR);
    } else {
      $result = $db->prepare($sql);
    }
    $result->execute();
  } catch (Exception $e) {
    echo "bad query ";
    echo $e->getMessage();
    exit;
  }

and here is my catalog.php file:

<?php
include("inc/functions.php");

$pageTitle = "Full Catalog";
$section = null;
$items_per_page = 8;

if (isset($_GET["cat"])) {
    if ($_GET["cat"] == "books") {
        $pageTitle = "Books";
        $section = "books";
    } else if ($_GET["cat"] == "movies") {
        $pageTitle = "Movies";
        $section = "movies";
    } else if ($_GET["cat"] == "music") {
        $pageTitle = "Music";
        $section = "music";
    }
}

if (isset($_GET["pg"])) {
  $current_page = filter_input(INPUT_GET,"pg",FILTER_SANITIZE_NUMBER_INT);  
}
if (empty($current_page)) {
  $current_page = 1;
}

$total_items = get_catalog_count($section);
$total_pages = ceil($total_items / $items_per_page);

//limit results in redirect
$limit_results = "";
if (!empty($section)) {
  $limit_results = "cat=" . $section . "&";
}    

//redirect too-large page numbers to the last page
if ($current_page > $total_pages) {
  header("location:catalog.php?" . $limit_results . "pg=".$total_pages);
}    

//redirect too-small page numbers to the first page  
if ($current_page < 1) {
  header("location:catalog.php?" . $limit_results . "pg=1");
}    

//determine the offset (number of items to skip) for the current page
//for example on page 3 with 8 items per page, the offset would be 16
$offset = ($current_page - 1) * $items_per_page;    

if (empty($section)) {
  $catalog = full_catalog_array($items_per_page,$offset);
} else {
  $catalog = category_catalog_array($section,$items_per_page,$offset);
}

include("inc/header.php"); ?>

<div class="section catalog page">

    <div class="wrapper">

        <h1><?php 
        if ($section != null) {
            echo "<a href='catalog.php'>Full Catalog</a> &gt; ";
        }
        echo $pageTitle; ?></h1>
        <div class="pagination">
          Pages:
          <?php            
            for ($i = 1;$i <= $total_pages;$i += 1) {
              if ($i == $current_page) {
                echo "<span>$i</span>";
              }
            }
          ?>
        </div>
        <ul class="items">
            <?php
            foreach ($catalog as $item) {
                echo get_item_html($item);
            }
            ?>
        </ul>

    </div>
</div>

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

when I go to click on any of the categories (books, movies, etc) I get the below error:

bad query SQLSTATE[HY000]: General error: 1 near "(": syntax error

Don't know what to make of it or how to fix it. Please help.

3 Answers

Hi Jane!

In your sql statement:

$result = $db->prepare($sql . "WHERE LOWER(category) = ?");

add space before word WHERE

$result = $db->prepare($sql . " WHERE LOWER(category) = ?");

What results did you get?

Best regards.

janeporter
PLUS
janeporter
Courses Plus Student 23,471 Points

that worked. thanks. i can't believe i missed that...

You welcome!

When you are writing huge amounts of code such things happen very often. To prevent them, or at least to find such small bugs try to test your code piece by piece(or more exactly to said "unit by unit").