WHERE clause in the query doesn't work properly in CodeIgniter 3-CodePudding

I'm using CodeIgniter 3 for a project and wrote below function in the controller to pass some data to the view. It queries a table in the database to get blood types as labels and get the count of rows for each blood type where "isAvailable = 1". Then those data is passed into a view to render a chart. But as you can see those counts are wrong. It counts the rows even if "isAvailable = 0". What is the issue of my code and how do I fix that?

Function in the controller.

public function bloodTypesChart()
    {

        $chartData = [];
        $blood_types = $this->db->query("SELECT (BloodType) as blood_type FROM packets WHERE (isAvailable) = '1' GROUP BY blood_type")->result_array();

        foreach($blood_types as $bt)
        {
            $record =  $this->db->query("SELECT COUNT(PacketID) as count FROM packets WHERE BloodType = '{$bt['blood_type']}'")->result_array();

            foreach($record as $row) {
                $chartData['label'][] = $bt['blood_type'];
                $chartData['data'][] = $row['count'];
            }
        }
        $chartData['chart_data'] = json_encode($chartData);
        $this->load->view('insight',$chartData);
    }

View

<script>
    new Chart(document.getElementById("bar-chart"), {
        type: 'bar',
        data: {
            labels: <?= json_encode($label)?>,
            datasets: [
                {
                    label: "Donations",
                    backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850"],
                    data: <?= json_encode($data)?>
                }
            ]
        },
        options: {
            legend: { display: false },
            title: {
                display: true,
                text: 'Donations'
            }
        }
    });
</script>

CodePudding user response：

In your query because you have encapsulated only the field name in parenthesis, WHERE (isAvailable) = '1' evaluates to WHERE there is a field labeled isAvailable - which is true for every row in the table. Remove the parenthesis and it should work fine

$blood_types = $this->db->query("SELECT (BloodType) as blood_type FROM packets WHERE isAvailable = '1' GROUP BY blood_type")->result_array();

CodePudding user response：

You have this way too complicated, avoid using php loops to get data which you can get by a simple query.

just use this mysql query, counting the isAvailable rows, when true (1):

$sql=" SELECT BloodType as blood_type, COUNT(PacketID) as mycount 
       FROM packets 
       WHERE isAvailable = 1 
       GROUP BY blood_type
     ";

note: I've changed the alias count to mycount, since count is a reserved word.

your function would look so:

public function bloodTypesChart()
    {
        $sql=" SELECT BloodType as blood_type, COUNT(PacketID) as mycount 
            FROM packets 
            WHERE isAvailable = 1 
            GROUP BY blood_type
         ";    

        $chartData = [];
        $blood_types = $this->db->query($sql)->result_array();

        foreach($blood_types as $row)
        {
                $chartData['label'][] = $row['blood_type'];
                $chartData['data'][] = $row['mycount'];
        }
        $chartData['chart_data'] = json_encode($chartData);
        $this->load->view('insight',$chartData);
    }

here is a sql-fiddle executing the query using a simplified version of your database