Problem with retrieving selected data from MySQL

[Frenzyy]

Hi,

I'm developing a small web app for uni and one of the requirements is that it has to have a list, with data from a database, that can be sorted by clicking on a certain category and then only the items from that category will get displayed.

I've managed to retrieve the data from the database into a table on the website, however, the drop down menu to select which category you would like to look at, doesn't work. Meaning that it displays all the items from the database no matter what. I do not receive any errors and really have no idea how I could solve this problem.

Hopefully someone will be able to help me with this. 

Code for the function which is supposed to only display items from that category:

 

 //Get ItemEntity objects from the database and return them in an array.
    function GetItemByCategory($category) {
        // require 'dbconnect.php';


        //Open connection and Select database.     
        $con = mysqli_connect("localhost", "root", "", "login") or die(mysqli_error);


        $query = "SELECT * FROM items WHERE category LIKE '$category'";
        $result = mysqli_query($con, $query) or die(mysqli_error($con));
        $itemArray = array();


        //Get data from database.
        while ($row = mysqli_fetch_array($result)) {


            $name = $row[1];
            $category = $row[2];
            $place = $row[3];
            $date = $row[4];
            $description = $row[5];
            $image = $row[6];


            //Create item objects and store them in an array.
            $item = new ItemEntity(-1, $name, $category, $place, $date, $description, $image);
            array_push($itemArray, $item);
        }
        //Close connection and return result
        mysqli_close($con);
        return $itemArray;
    }


}

 

I'm still quite new to this so it might be a simple fix but like I said, I'm lost so that's why I've reached out.

Thanks in advance and let me know if you need any more code from me.

[Jacques1]

Before you jump to complex code, learn the basics. Opening a new database connection for every query is insane, dumping raw input into queries can leave you wide open to SQL injection attacks, your error handling is broken, your fetch logic is unnecessarily cumbersome etc.

 

Do you have to use mysqli? Because if you don't, I suggest you strike it from your memory and switch to PDO.

 

This is the modified function:

<?php

// use an additional function parameter for the database connection
function getItemByCategory(PDO $databaseConnection, $category)
{
    // use a prepared statement to safely pass $category to the query
    $itemsStmt = $databaseConnection->prepare('
        SELECT    -- always select *specific* columns
            name,
            category,
            place,
            date,
            description,
            image
        FROM
            items
        WHERE
            category = :category
    ');
    $itemsStmt->execute(['category' => $category]);

    $items = [];
    foreach ($itemsStmt as $item)
    {
        $items[] = new ItemEntity(-1, $item['name'], $item['category'], $item['place'], $item['date'], $item['description'], $item['image']);
    }

    return $items;
}

It's also important to learn basic debugging steps like inspecting variables with var_dump(). Then you can figure out what your code does. The problem description sounds like you aren't even calling the getItemByCategory() function.

[Frenzyy]

Before you jump to complex code, learn the basics. Opening a new database connection for every query is insane, dumping raw input into queries can leave you wide open to SQL injection attacks, your error handling is broken, your fetch logic is unnecessarily cumbersome etc.

 

Do you have to use mysqli? Because if you don't, I suggest you strike it from your memory and switch to PDO.

 

This is the modified function:

<?php

// use an additional function parameter for the database connection
function getItemByCategory(PDO $databaseConnection, $category)
{
    // use a prepared statement to safely pass $category to the query
    $itemsStmt = $databaseConnection->prepare('
        SELECT    -- always select *specific* columns
            name,
            category,
            place,
            date,
            description,
            image
        FROM
            items
        WHERE
            category = :category
    ');
    $itemsStmt->execute(['category' => $category]);

    $items = [];
    foreach ($itemsStmt as $item)
    {
        $items[] = new ItemEntity(-1, $item['name'], $item['category'], $item['place'], $item['date'], $item['description'], $item['image']);
    }

    return $items;
}

It's also important to learn basic debugging steps like inspecting variables with var_dump(). Then you can figure out what your code does. The problem description sounds like you aren't even calling the getItemByCategory() function.

Thanks for your reply. I guess I can use PDO but would that mean that I have to change all the files where I used mysqli to PDO or would it be ok if I just used it from now on?

Also, I used var_dump() and this is what I get:

var_dump($result); gives me this when I run it: object(mysqli_result)#4 (5) { ["current_field"]=> int(0) ["field_count"]=> int(7) ["lengths"]=> NULL ["num_rows"]=> int(3) ["type"]=> int(0) } Warning: Invalid argument supplied for foreach() in E:\XAMPP\htdocs\Coursework2\Controller\ItemController.php on line 40 Hopefully that helps.

 

//edit

This is the ItemController class btw

<?php

require ("Model/ItemModel.php");

/**
* Contains non-db related functions for the Item page
*/
class ItemController
{

function CreateItemDropdownList()
{
$itemModel = new ItemModel();
$result = "<form action = '' method = 'post' width = '200px'>
Please select a category: <select name = 'types' >
<option value = '%' >ALL</option>".$this->CreateOptionValues($itemModel->GetCategories())."
</select>
<input type = 'submit' value = 'Search' /></form>";

return $result;
}
function CreateOptionValues(array $valueArray)
{
$result = "";

foreach ($valueArray as $value) 
{
$result = $result . "<option value ='$value'>$value</option>";
}
return $result;
}

function CreateItemTables($categories)
{
$itemModel = new ItemModel();
$itemArray = $itemModel->GetItemByCategory($categories);
$result = "";

// Generate an item table for each item entity in the array
foreach ($itemArray as $key => $item) {
$result = $result . "<table class = 'itemTable'>
<tr>
<th rowspan='6' width = '150px' ><img runat = 'server' src = '$item->image'/></th>
<th width = '75px' >Name: </th>
<td>$item->name</td>
</tr>

<tr>
<th>Category: </th>
<td>$item->category</td>
</tr>

<tr>
<th>Place: </th>
<td>$item->place</td>
</tr>

<tr>
<th>Date: </th>
<td>$item->date</td>
</tr>

<tr>
<td colspan='2' >$item->description</td>
</tr>


</table>";
}
return $result;
}
}
?>

Edited March 30, 2017 by Frenzyy