是否最好减少对数据库的调用并将结果输出到数组中？

发布于 2024-09-06 06:25:15 字数 1284 浏览 5 评论 0原文

我正在尝试创建一种更简洁的方法来进行数百个数据库调用。我尝试将代码移植到一个完成所有查询工作的类中，而不是每次想要输出单个字段时都写出整个查询。这是我到目前为止所拥有的课程：

class Listing {

/* Connect to the database */
private $mysql;

function __construct() {
    $this->mysql = new mysqli(DB_LOC, DB_USER, DB_PASS, DB) or die('Could not connect');
}
function getListingInfo($l_id = "", $category = "", $subcategory = "", $username = "", $status = "active") {
    $condition = "`status` = '$status'";
    if (!empty($l_id)) $condition .= "AND `L_ID` = '$l_id'";
    if (!empty($category)) $condition .= "AND `category` = '$category'";
    if (!empty($subcategory)) $condition .= "AND `subcategory` = '$subcategory'";
    if (!empty($username)) $condition .= "AND `username` = '$username'";
    $result = $this->mysql->query("SELECT * FROM listing WHERE $condition") or die('Error fetching values');
    $info = $result->fetch_object() or die('Could not create object');
    return $info;
}
}

这使得我可以轻松地从一行中访问我想要的任何信息。

$listing = new Listing;
echo $listing->getListingInfo('','Books')->title;

这将输出“书籍”类别中第一个列表的标题。但如果我想输出该列表的价格，我必须再次调用 getListingInfo()。这会在数据库上进行另一个查询，并再次仅返回第一行。

这比每次编写整个查询要简洁得多，但我觉得我可能过于频繁地调用数据库。有没有更好的方法从我的类中输出数据，并且仍然可以简洁地访问它（可能将所有行输出到数组并返回数组）？如果是，如何？

原文

I'm trying to create a more succinct way to make hundreds of db calls. Instead of writing the whole query out every time I wanted to output a single field, I tried to port the code into a class that did all the query work. This is the class I have so far:

class Listing {

/* Connect to the database */
private $mysql;

function __construct() {
    $this->mysql = new mysqli(DB_LOC, DB_USER, DB_PASS, DB) or die('Could not connect');
}
function getListingInfo($l_id = "", $category = "", $subcategory = "", $username = "", $status = "active") {
    $condition = "`status` = '$status'";
    if (!empty($l_id)) $condition .= "AND `L_ID` = '$l_id'";
    if (!empty($category)) $condition .= "AND `category` = '$category'";
    if (!empty($subcategory)) $condition .= "AND `subcategory` = '$subcategory'";
    if (!empty($username)) $condition .= "AND `username` = '$username'";
    $result = $this->mysql->query("SELECT * FROM listing WHERE $condition") or die('Error fetching values');
    $info = $result->fetch_object() or die('Could not create object');
    return $info;
}
}

This makes it easy to access any info I want from a single row.

$listing = new Listing;
echo $listing->getListingInfo('','Books')->title;

This outputs the title of the first listing in the category "Books". But if I want to output the price of that listing, I have to make another call to getListingInfo(). This makes another query on the db and again returns only the first row.

This is much more succinct than writing the entire query each time, but I feel like I may be calling the db too often. Is there a better way to output the data from my class and still be succinct in accessing it (maybe outputting all the rows to an array and returning the array)? If yes, How?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

叹倦 2024-09-13 06:25:15

你真的有性能问题吗？

如果你当前的设置可以工作并且没有遇到性能问题，我就不会碰它。

这种数据库访问抽象可能会成为维护问题，并且可能不会提高性能。

此外，您还容易受到 SQL 注入 。

回复收藏 0 原文

汹涌人海 2024-09-13 06:25:15

您应该能够将查询中的整个对象存储到变量中，然后访问该对象中的单个值：

$object = $listing->getListingInfo('','Books');
$title = $object->title;
$price= $object->price;

但您也可以使用 fetch_assoc() 并返回整个关联数组：

$array = $listing->getListingInfo('','Books');
$title = $object['title'];
$price= $object['price'];

这将为您提供相同的结果，并且还可以使用仅对数据库进行一次查询。

编辑：如果 getListingInfo() 是唯一的函数，您应该考虑以下内容：

将函数重命名为prepareListingInfo()，并且在函数内仅准备查询并将其存储在类变量中。
添加 getNextListingInfo() 函数，它将返回一个对象或与下一行的关联数组。

使用这个新函数，您可以获得与查询匹配的每一行。

You should be able to store the whole object from the query into a variable and then access the single values from that object:

$object = $listing->getListingInfo('','Books');
$title = $object->title;
$price= $object->price;

But you can also use fetch_assoc() and return the whole assiciative array:

$array = $listing->getListingInfo('','Books');
$title = $object['title'];
$price= $object['price'];

This will give you the same results and also with only one query to the DB.

EDIT: If the getListingInfo() is the only function you should think of the following:

rename the function to prepareListingInfo() and within the function only prepare the query and store it in a class variable.
add a getNextListingInfo() function, which will return an object or associative array with the next row.

Using this new function, you can get every row that matches your query.

回复收藏 0 原文