[mysql]使用选择查询使用多维数组从数据库检索数据

标签: MySQL PHP
发布时间: 2017/4/9 22:51:30
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想要使用选择查询来从数据库,使用多维数组的每个类别中显示的类别和数目 Dvd 列表中检索数据。

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

这是什么我已经跟上到目前为止,原谅我基本知识︰

<?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>

解决方法 1:

我相信,此代码会给出你正在寻找的结果。请谷歌有关 mysql 信息计数的计数中的选择,和左联接,联接两个表中的行在一起选定区域中的行数。

我试着保持尽可能多从原始代码中机智。只有查询部分发生了变化。我可以建议你去读了有关 SQL。

你可以玩弄作为别名或删除它们。请注意它们将会影响 $catetory 键名。也许你想要从选择中删除 ID。但这完全取决于你。 !

<?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);
?>
官方微信
官方QQ群
31647020