0

I am learning PDO and I am facing an issue which I do not have a clue about. So, I have a category table (Guitar, Drums etc) and another product table (having product of each type of categories). I am learning MVC and I am trying to list all the products for a particular category.

This is what I have for my controller which is still incomplete:

<?php
include './model/database.php';
include './model/category.php';
include './model/product.php';
// What is the default action
if(isset($_POST['action'])){  
    $action=$_POST['action'];
}elseif(isset($_GET['action'])){
    $action=$_GET['action'];  
}
else {
    $action='product_list'; // Default Action 
}
if($action==='product_list'){
    if(isset($_GET['category_id'])){
            $category_id=$_GET('category_id');
} else {
    $category_id=1;
       }
}
$category_name=get_category_name($category_id);
$catagories=get_categories();
$products= get_product_by_category($category_id);
include('product_display.php');

?>

This is my categories.php file:

<?php
    function get_categories(){
        global $db;
        $query="SELECT * FROM categories ORDER BY categoryID";
        $categories=$db->query($query); //line 5
        return $categories;
    }
    function get_category_name($category_id){
        global $db;
        $query="SELECT categoryName FROM categories
                WHERE categoryID=$category_id";
        $db=$db->query($query);
        $category=$db->fetch();
        $category_name=$category['categoryName'];
        return $category_name;
    }
?>

This is my database.php file :

<?php 
$dsn='mysql:host=localhost;dbname=my_guitar_shop1';
$user='mgs_user';
$password='pa55word';
try {
   $db= new PDO($dsn, $user, $password);
} catch (Exception $ex) {
    $error_message=$ex->getMessage();
    include 'database_error.php';
    exit();   
}
?>

It gives me an error:

Call to undefined method PDOStatement::query() in C:\xampp\htdocs\mvcfirst\model\category.php on line 5

halfer
  • 19,824
  • 17
  • 99
  • 186
Knownow
  • 353
  • 1
  • 4
  • 17

1 Answers1

1

This is exactly why the consensus is that globals are evil. In your database.php file you're setting the $db variable to a PDO object:

$db= new PDO($dsn, $user, $password);

And then in your categories.php file you overwrite the $db variable with a PDOStatement object:

$db=$db->query($query);

PDO::query() returns a PDOStatement object, which does not have a query() method. Thus the error you're getting.

On another note, you need to be using prepared statements with bound parameters or else your code is vulnerable to SQL injection attacks. You can also use PDOStatement::fetchColumn() to select a single column:

function get_category_name($category_id){
    global $db; // Yuck!
    $query="SELECT categoryName FROM categories
            WHERE categoryID = ?";
    $stmt=$db->prepare($query);
    $stmt->execute([$category_id]);
    return $stmt->fetchColumn();
}
Mike
  • 23,542
  • 14
  • 76
  • 87