PHP：多个SQL查询与一个怪物

I have some really funky code. As you can see from the code below I have a series of filters that I add to query. Now would it be easier to just have multiple queries, with it's own set of filters, then store the results in an array, or have this mess?

我有一些非常时髦的代码。从下面的代码中可以看出,我有一系列过滤器,我添加到查询中。现在,只有多个查询,使用它自己的一组过滤器,然后将结果存储在一个数组中,还是有这个混乱,会更容易吗?

Does anyone have a better solution to this mess? I need to be able to filter by keyword and item number, and it needs to be able to filter using multiple values, not know which is which.

有没有人有更好的解决方案?我需要能够按关键字和项目编号进行过滤,并且需要能够使用多个值进行过滤,而不知道哪个是哪个。

//Prepare filters and values
$values = array();
$filters = array();
foreach($item_list as $item){
    $filters[] = "ItemNmbr = ?";
    $filters[] = "ItemDesc LIKE ?";
    $filters[] = "NoteText LIKE ?";
    $values[] = $item;
    $values[] = '%' . $item . '%';
    $values[] = '%' . $item . '%';
}
//Prepare the query
$sql = sprintf(
    "SELECT ItemNmbr, ItemDesc, NoteText, Iden, BaseUOM FROM ItemMaster WHERE %s LIMIT 21",
    implode(" OR ", $filters)
);
//Set up the types
$types = str_repeat("s", count($filters));
array_unshift($values, $types);

//Execute it
$state = $mysqli->stmt_init();
$state->prepare($sql) or die ("Could not prepare statement:" . $mysqli->error);
call_user_func_array(array($state, "bind_param"), $values);
$state->bind_result($ItemNmbr, $ItemDesc, $NoteText, $Iden, $BaseUOM);
$state->execute() or die ("Could not execute statement");
$state->store_result();

3 个解决方案

#1

I don't see anything particularly monstrous about your query. The only thing I would do different is separate the search terms.

我没有看到任何关于你的查询特别可怕的东西。我唯一不同的就是分开搜索词。

Ie $item_list could be split in numeric items and text items.

即$ item_list可以拆分为数字项和文本项。

then you could make the search something like:

然后你可以使搜索类似于:

...WHERE ItemNmbr IN ( number1, number2, number3) OR LIKE .... $text_items go here....

IN is a lot more efficient and if your $item_list doesn't contain any text part... then you are just searching a bunch of numbers which is really fast.

IN效率更高,如果你的$ item_list不包含任何文本部分......那么你只是搜索一堆非常快的数字。

Now the next part if you are using a lot of LIKEs in your query maybe you should consider using MySQL Full-text Searching.

现在下一部分如果您在查询中使用了很多LIKE,那么您应该考虑使用MySQL全文搜索。

#2

You're answer depends on what exactly you need. The advantage of using only 1 query is that of resource use. One query takes only 1 connection and 1 communication with the sql server. And depending what what exactly you are attempting to do, it might take less SQL power to do it in 1 statement than multiple.

你的答案取决于你究竟需要什么。仅使用1个查询的优点是资源使用。一个查询只与sql server进行1次连接和1次通信。根据您尝试做什么,在1个语句中执行它可能需要更少的SQL权力而不是多个。

However, it might be more practical from a programmers point of view to use a few less complex sql statements that require less to create than 1 large one. Remember, ultimitaly you are programming this and you need to make it work. It might not really make a difference, script processing vs. sql processing. Only you can make ultimate call, which is more important? I would generally recommend SQL processing above script process when dealing with large databases.

但是,从程序员的角度来看,使用一些不太复杂的sql语句可能更实际,这些语句需要更少的创建而不是1个大的。请记住,ultimitaly你正在编程这个,你需要让它工作。它可能没有什么区别,脚本处理与sql处理。只有你才能进行终极通话,哪个更重要?在处理大型数据库时,我通常会建议在脚本进程上面进行SQL处理。

#3

A single table query can be cached by the SQL engine. MySQL and its ilk do not cache joined tables. The general rule for performance is to use joins only when necessary. This encourages the DB engine to cache table indexes aggressively and also makes your code easier to adapt to (faster) object databases--like Amazon/Google cloud services force you to.

SQL引擎可以缓存单个表查询。 MySQL及其同类版本不会缓存连接表。性能的一般规则是仅在必要时使用连接。这鼓励数据库引擎积极地缓存表索引,并使您的代码更容易适应(更快)对象数据库 - 如亚马逊/谷歌云服务迫使您。

#1