使用 PHP 计算 Oracle 中的 SELECTED 行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4369129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting number of SELECTED rows in Oracle with PHP
提问by rfgamaral
I'm doing this project for university, which is basically a movie database and for a couple of queries I need to know how many rows were selected. For now, there's 2 situations where I need this:
我正在为大学做这个项目，它基本上是一个电影数据库，对于几个查询，我需要知道选择了多少行。目前，有两种情况我需要这个：
- Display a single movieinformation. I want the count of selected rows to know if the database contains the selected movie by the user. Or is there a better solution for this?
- That selected movie has genres, I need to know how many so that I can construct a string with the genres separated by
|without adding one to the end of the string.
- 显示单个电影信息。我希望选定行的计数知道数据库是否包含用户选定的电影。或者有更好的解决方案吗？
- 所选的电影有流派，我需要知道有多少，以便我可以构造一个字符串，其中的流派用 分隔，
|而无需在字符串的末尾添加一个。
With MySQL this is easy, I just query the database and use mysql_num_rows()but oci_num_rows()doesn't work quite the same for the SELECT statement.
使用 MySQL 这很容易，我只是查询数据库并使用，mysql_num_rows()但oci_num_rows()对于 SELECT 语句的工作方式并不完全相同。
The only solution I found with OCI/PHP is this:
我在 OCI/PHP 中找到的唯一解决方案是：
if(is_numeric($mid) && $mid > 0) {
$stid = oci_parse($db,
'SELECT COUNT(*) AS NUM_ROWS
FROM movies
WHERE mid = '.$mid
);
oci_define_by_name($stid, 'NUM_ROWS', $num_rows);
oci_execute($stid);
oci_fetch($stid);
if($num_rows > 0) {
$stid = oci_parse($db,
'SELECT title, year, synopsis, poster_url
FROM movies
WHERE mid = '.$mid
);
oci_execute($stid);
$info = oci_fetch_assoc($stid);
$stid = oci_parse($db,
'SELECT COUNT(*) AS NUM_ROWS
FROM genres g, movies_genres mg
WHERE mg.mid = '.$mid.' AND g.gid = mg.gid'
);
oci_define_by_name($stid, 'NUM_ROWS', $num_rows);
oci_execute($stid);
oci_fetch($stid);
$stid = oci_parse($db,
'SELECT g.name AS genre
FROM genres g, movies_genres mg
WHERE mg.mid = '.$mid.' AND g.gid = mg.gid');
oci_execute($stid);
$genres_list = null;
while($row = oci_fetch_assoc($stid)) {
$genres_list .= $row['GENRE'];
if($num_rows > 1) {
$genres_list .= ' | ';
$num_rows--;
}
}
$Template->assignReferences(array(
'Index:LinkURI' => $link_uri,
'Movie:Title' => $info['TITLE'],
'Movie:Year' => $info['YEAR'],
'Movie:GenresList' => $genres_list,
'Movie:Synopsis' => $info['SYNOPSIS'],
'Movie:PosterURL' => $info['POSTER_URL'] // FIX: Handle empty poster link
));
$Template->renderTemplate('movieinfo');
} else {
// TODO: How to handle this error?
}
} else {
// TODO: How to handle this error?
}
But I don't like it. I always need to make 2 queries to count the rows and then select the actual data and there's too many lines of code just to count the rows.
但我不喜欢。我总是需要进行 2 次查询来计算行数，然后选择实际数据，但为了计算行数，代码行太多了。
This code doesn't show it (haven't done it yet cause I'm looking for a better solution) but I'll also need to do the same for the movie directors/writers.
这段代码没有显示它（还没有完成，因为我正在寻找更好的解决方案）但我还需要为电影导演/编剧做同样的事情。
Is there any better and simplersolution to accomplish this or this is the only way?
有没有更好更简单的解决方案来实现这一点，或者这是唯一的方法？
I could add separators in the fetch loop until it finishes and then use PHP functions to trim the last separator from the string, but for this project I'm forced to use SEQUENCES, VIEWS, FUNCTIONS, PROCEDURES and TRIGGERS. Do any of these help solving my problem?
我可以在 fetch 循环中添加分隔符，直到它完成，然后使用 PHP 函数从字符串中修剪最后一个分隔符，但是对于这个项目，我不得不使用 SEQUENCES、VIEWS、FUNCTIONS、PROCEDURES 和 TRIGGERS。这些是否有助于解决我的问题？
I know what SEQUENCES are, I'm using them already but I don't see how can they help.
我知道序列是什么，我已经在使用它们了，但我不知道它们有什么帮助。
For VIEWS, they probably wouldn't simplify the code that much (it's basically a stored query right?). For FUNCTIONS, PROCEDURES and TRIGGERS, as far as I understand them, I can't see how can they be of any help either.
对于 VIEWS，他们可能不会那么简化代码（它基本上是一个存储查询，对吗？）。对于 FUNCTIONS、PROCEDURES 和 TRIGGERS，据我所知，我也看不出它们有什么帮助。
Solutions?
解决方案？
采纳答案by DCookie
Why do the initial count at all? Just issue your select for the movie title. If it's not found, do your error processing. If it is, continue on! If you reallyneed the count, use an analytic to add the count to your query:
为什么要进行初始计数？只需发布您对电影标题的选择。如果未找到，请进行错误处理。如果是，请继续！如果您确实需要计数，请使用分析将计数添加到您的查询中：
'SELECT title, year, synopsis, poster_url
, COUNT(*) OVER (PARTITION BY mid) mcount
FROM movies
WHERE mid = '.$mid
The same goes for your genre selection.
您的类型选择也是如此。
EDIT:
编辑：
Oracle documentation on Analytic Functions link. I found that analytics are a bit difficult to get from the Oracle docs. Analytic functions by Example by Shouvik Basuprovides some simple guidance as to how to use them and helped me quite a bit.
有关分析函数链接的Oracle 文档。我发现从 Oracle 文档中获取分析有点困难。Shouvik Basu 的示例分析函数提供了一些关于如何使用它们的简单指导，并帮助了我很多。
回答by jaswanttak
You can try this
你可以试试这个
$conn = oci_pconnect('user', 'password', 'connectionstring');
$resource = oci_parse($conn, $sql);
oci_execute($resource, OCI_DEFAULT);
$results=array();
$numrows = oci_fetch_all($resource, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
Cheers
干杯
回答by Breezer
you can use the ocirowcountit should behave just like mysql_num_rows when making a select
您可以使用ocirowcount它在进行选择时应该像 mysql_num_rows 一样
回答by Bridget Arrington
oci_num_rows() Will give you the total row count if executed AFTER oci_fetch_array()
如果在 oci_fetch_array() 之后执行，oci_num_rows() 将为您提供总行数
