dilly ding dilly dong dilly ding dilly dong - 6 months ago 7
SQL Question

Using a SELECT Query to retrieve data from a database using a multidimensional array

I want to use a SELECT Query to retrieve data from a database, that displays the list of categories and the number of DVDs in each category using a multidimensional array.

Table Name: dvds_table
dv_id dv_caid dv_name
1 4 Matrix Reloaded
2 2 Johnny English
3 4 The Recruit
4 4 Minority Report
5 3 Two Weeks Notice
6 2 Bend It Like Beckham

Table Name: categories_table
ca_id ca_name
2 Comedy
4 Action
1 Drama
3 Romance
5 TV


This is what I've come up with so far, excuse my basic knowledge:

<?php

$link = mysqli_connect("localhost", "root", "", "dvddb");

if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

$categories=getNumDvdsInCategories();


function getNumDvdsInCategories() {


$this->db->select('*');
$this->db->from('categories_table');
$this->db->order_by('ca_name', 'DESC');

$query = $this->db->get();
return $query->result();
}


$this->load->model("model");
$data['results'] = $this->model->list_categories();
$this->load->view('categories_list_view', $data);


?>

<html>
<body>
<table border=1>
<tr>
<td>Category ID</td>
<td>Category Name</td>
<td>Num. DVDs</td>
</tr>

<?php foreach ($categories as $category) { ?>
<tr>
<td><?php echo $category['ca_id']; ?></td>
<td><?php echo $category['ca_name']; ?></td>
<td><?php echo $category['num']; ?></td>
</tr>
<?php } ?>

</table>
</body>
</html>

Answer

I Belief that this code will give the results you are looking for. Please Google for information about mysql COUNT which counts the number of rows in a selection, and LEFT JOIN which joins rows from two tables together in a selection.

I've tried to keep as much from the original code in tact. Only the query part has changed. I can advice you to read-up about the SQL.

You can play around with the AS aliases or removed them. Please note they will affect the $catetory key names. And maybe you want to remove the ID from the selection. But that's up to you.!

<?php
// * These where the create queries that I have used to make a similar table as you.
// create table dvds_table ( dv_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dv_caid INT NOT NULL, FOREIGN KEY (dv_caid) references categories_table(ca_id), dv_name varchar(20));
// create table categories_table (ca_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ca_name varchar(20));

$link = mysqli_connect('localhost', 'root', '', 'dvddb');

if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// * use $sql example below if you dont want the ID (Then also remove $category['ID'] in your HTML). 
// * Also you can remove the AS aliases or rename them as you see fit (then also change the $category key names).
// $sql = 'SELECT categories_table.ca_name AS Category, COUNT(dvds_table.dv_caid) AS Amount FROM categories_table LEFT JOIN dvds_table ON dvds_table.dv_caid = categories_table.ca_id GROUP BY categories_table.ca_name ORDER BY categories_table.ca_name DESC;';

$sql = 'SELECT  categories_table.ca_id AS ID,
            categories_table.ca_name AS Category,
            COUNT(dvds_table.dv_caid) AS Amount
            FROM categories_table
            LEFT JOIN dvds_table
            ON dvds_table.dv_caid = categories_table.ca_id
            GROUP BY categories_table.ca_id
            ORDER BY categories_table.ca_name DESC;';
$result = mysqli_query($link, $sql);

?>

<html>
<body>
<table border=1>
    <tr>
        <td>Category ID</td>
        <td>Category Name</td>
        <td>Num. DVDs</td>
    </tr>
    <?php foreach ($result as $category) { ?>
    <tr>
        <td><?php echo $category['ID']; ?></td>
        <td><?php echo $category['Category']; ?></td>
        <td><?php echo $category['Amount']; ?></td>
    </tr>
<?php } ?>
</table>
</body>
</html>

<?php
mysqli_free_result($result);
mysqli_close($link);
?>