天道酬勤,学无止境

PHP Oracle oci_num_rows result 0

I have a question about query in Oracle :

The problem is, the result of oci_num_rows is always 0.

and here is my code so far :

  $query = "SELECT IP_ADDRESS, STATUS FROM SEIAPPS_IP_ADDRESS WHERE IP_ADDRESS='$ip'";
  $result = oci_parse($c1, $query);
  oci_execute($result);
  echo $found = oci_num_rows($result);

To make it sure, I try to clear the condition "WHERE IP_ADDRESS='$ip'". Still same with the result is 0 whereas in my table there are some data.

Any advice ?

标签

评论

Use this:

$query = "SELECT IP_ADDRESS, STATUS FROM SEIAPPS_IP_ADDRESS WHERE IP_ADDRESS='$ip'";
$result = oci_parse($c1, $query);
oci_execute($result);
$numrows = oci_fetch_all($result, $res);
echo $numrows." Rows";

This function does not return number of rows selected! For SELECT statements this function will return the number of rows, that were fetched to the buffer with oci_fetch*() functions.

Try this to check either your the credentials of your connection are correct or not.

<?php
$conn = oci_connect("hr", "welcome", "localhost/XE");
if (!$conn) {
    $e = oci_error();   // For oci_connect errors do not pass a handle
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
?>

For anyone else who fell into this trap, here's a simple summary of what the oci_fetch_all method will return.

$s = oci_parse($conn, "
          SELECT 1 AS A FROM DUAL 
    UNION SELECT 2 AS A FROM DUAL 
    UNION SELECT 3 AS A FROM DUAL 
    UNION SELECT 4 AS A FROM DUAL
");
oci_execute($s);
echo "records fetched: ". oci_num_rows($s)."\n";

oci_fetch($s);
echo "records fetched: ". oci_num_rows($s)."\n";

oci_fetch($s);
echo "records fetched: ". oci_num_rows($s)."\n";

oci_fetch_all($s, $out);
echo "records fetched: ". oci_num_rows($s)." out count: ". count($out['A']) . "\n";

will output:

records fetched: 0
records fetched: 1
records fetched: 2
records fetched: 4 out count: 2

So oci_num_rows is incremental, each call to oci_fetch will increment the counter by one (assuming there is at least one more record to fetch), and oci_fetch_all will fetch all remaining rows and put them into the $out buffer.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • OCI/PHP 中使用 oci_num_rows 的问题(Issue in OCI/PHP using oci_num_rows)
    问题 当通过 SQL 进行完全相同的查询返回 228 时,为什么下面会显示0 results returned ? error_reporting(E_ALL); ini_set('display_errors', 1); include("connection.php"); if($conn){ $stid = oci_parse($conn, "SELECT COUNT(CustomerNo) FROM Customers"); oci_execute($stid); echo oci_num_rows($stid) . " rows returned.<br />\n"; oci_free_statement($stid); oci_close($conn); } 回答1 您必须获取结果,然后调用 oci_num_rows。 如 oci_num-rows 的 PHP 手册所述 - Note: This function does not return number of rows selected! For SELECT statements this function will return the number of rows, that were fetched to the buffer with oci_fetch*() functions. 因此你的 PHP
  • Issue in OCI/PHP using oci_num_rows
    Why would the below show 0 results returned, when the exact same query via SQL returns 228? error_reporting(E_ALL); ini_set('display_errors', 1); include("connection.php"); if($conn){ $stid = oci_parse($conn, "SELECT COUNT(CustomerNo) FROM Customers"); oci_execute($stid); echo oci_num_rows($stid) . " rows returned.<br />\n"; oci_free_statement($stid); oci_close($conn); }
  • how to fetch data from oracle database using PHP
    I am trying to create a class for executing oracle sql statements on PHP. here is my index.php where I am trying to call my function <?php include "dbaseconn/dbcontrol.php"; $DbControl = new DbControl; $DbControl->execute(" SELECT * FROM SAMPLE_TABLE"); foreach($DbControl->data as $items) { echo $items['SAMPLE_COLUMN_NAME']; } ?> and my dbcontrol.php for my function <?php class DbControl{ public $dbstr ='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(HOST = XX.XXX.XXX.XX)(PORT = XXXX)) ) (CONNECT_DATA = (SID = XXXXX) ) )'; public $user = "XXXXX"; public
  • How to get number of rows in php oci for SELECT statement
    I require help to get number of rows from select statement which contain where clause. I have tried various option. But it didn't work for me. I have shown my code snippet below. I'm getting o/p "0 rows fetched". But when I executed select query on Oracle DB, then it returns 1 row. Hence, please help me to get number of select statement in PHP through OCI. Environment: Windows, Php 7.3 and Oracle 12c Here is the Oracle query CREATE TABLE users ( no INTEGER NOT NULL, id VARCHAR2(200) NOT NULL, fullname VARCHAR2(200), email VARCHAR2(200), givenname VARCHAR2(200), first_login VARCHAR2(200), last
  • 使用PDO的行数(Row count with PDO)
    问题 周围有许多矛盾的说法。 在PHP中使用PDO获取行数的最佳方法是什么? 在使用PDO之前,我只是简单地使用了mysql_num_rows 。 fetchAll是我不想要的东西,因为有时我可能正在处理大型数据集,因此对我的使用不利。 你有什么建议吗? 回答1 $sql = "SELECT count(*) FROM `table` WHERE foo = ?"; $result = $con->prepare($sql); $result->execute([$bar]); $number_of_rows = $result->fetchColumn(); 这不是最优雅的方式,而且还涉及一个额外的查询。 PDO有PDOStatement::rowCount()这显然不MySQL的。 真痛苦 从PDO文档中: 对于大多数数据库,PDOStatement :: rowCount()不会返回受SELECT语句影响的行数。 而是使用PDO :: query()发出与所要SELECT语句相同的谓词的SELECT COUNT(*)语句,然后使用PDOStatement :: fetchColumn()检索将返回的行数。 然后,您的应用程序可以执行正确的操作。 编辑:上面的代码示例使用一个准备好的语句,在许多情况下,出于计数行的目的可能是不必要的,因此: $nRows = $pdo-
  • 从 oracle 将查询结果导出并下载到 PHP 中的 Excel 文件(Export and download query results to Excel file in PHP from oracle)
    问题 // Export results require_once('PHPExcel.php'); $query = "SELECT DISTINCT TITLE, PID, TYPE, SUM(DAYCOUNT) AS tot, ROUND(SUM(DAYCOUNT)/( SELECT SUM(DAYCOUNT) FROM REPORT_LIST_VIEW), 4) AS per FROM REPORT_LIST_VIEW WHERE DAYCOUNT > '0' GROUP BY TITLE, PID, TYPE ORDER BY tot DESC"; if ($result = db_query($query)) { // Create a new PHPExcel object $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('List of Cities'); // Loop through the result set $rowNumber = 1; while ($row = $result->fetchAssoc()) { $objPHPExcel->getActiveSheet()->fromArray($row,NULL,'A'.$rowNumber++); } /
  • 使用 oracle pdo 连接中的字段名称获取结果时出现未定义的索引错误(Undefined index error when getting results using the field name in oracle pdo connection)
    问题 <?php include ("pdo_mysql_connect.php"); // include ("pdo_oracle_connect.php"); $query="select city ,state from student"; $dataf = $pdoc->query($query); for($x=0;$x<3;$x++) { $resultf = $dataf->fetch(); echo ".- ".$resultf['city'] . " - " . $resultf['state'] . "<br />"; echo $resultf[0] . " | " . $resultf[1] . "<br />"; } ?> 将上述代码与 pdo mysql 连接一起使用,我得到了预期的结果。 英格兰.- 海得拉巴- 安得拉邦海得拉巴| 安得拉邦.- 北约克- 安大略北约克| 安得拉邦安大略 但是当引用带有字段名称时,oracle pdo 连接会出现未定义的索引错误。 注意:未定义索引:C:\xampp\htdocs\test.php.php 第 8 行中的城市 注意:未定义索引:C:\xampp\htdocs\test.php 中第 8 行中的状态 虽然数字引用有效。 .- - 伦敦 | England ... etc
  • Undefined index error when getting results using the field name in oracle pdo connection
    <?php include ("pdo_mysql_connect.php"); // include ("pdo_oracle_connect.php"); $query="select city ,state from student"; $dataf = $pdoc->query($query); for($x=0;$x<3;$x++) { $resultf = $dataf->fetch(); echo ".- ".$resultf['city'] . " - " . $resultf['state'] . "<br />"; echo $resultf[0] . " | " . $resultf[1] . "<br />"; } ?> using the above code with pdo mysql connection I get the results as expected .- London - England London | England .- Hyderabad - Andhra Pradesh Hyderabad | Andhra Pradesh .- North York - Ontario North York | Ontario but whith the oracle pdo connection there is an Undefined
  • 将 PDO 与 Oracle 数据库连接(Connect PDO with Oracle database)
    问题 我是 Oracle 新手,今天安装了 Oracle 11g Express Edition。 然后我安装了 Java SDK,然后是免费的 Oracle SQL Developer。 我与系统帐户连接并创建了如下定义的用户名和表。 我不完全知道 Oracle 是如何工作的,我认为使用用户名而不是数据库名称。 所以下面是细节。 用户名/连接/数据库= CustomSearch 表 = Reservation_General_2 该表中有一些列和一些数据。 但关键是我无法连接到 Oracle Server。 这是我尝试连接到数据库服务器的方式。 <?php /** * Created by PhpStorm. * User: HaiderHassan * Date: 9/3/14 * Time: 9:52 PM */ header('Access-Control-Allow-Origin: *'); $tns = " (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) "; try { $conn = new PDO("oci:dbname=".$tns,
  • Oracle + Oci8 + php5.6 + Ubuntu 16.04
    I have Ubuntu 16.04 installed, with PHP5.6. I download and unzipped Oracle 12.1.0.1.0, and put it on folder /opt/oracle/instantclient. Export ORACLE_HOME=/opt/oracle/instantclient, making soft links: $ sudo ln -s libocci.so.12.1 libocci.so $ sudo ln -s libclntsh.so.12.1 libclntsh.so After that I add to my system: $echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient After that create a new folder in /opt/oracle/instantclient/src and put in a oci8-2.0.10.tgz version, and tar xzvf this version. Then in the folder I execute $phpize with the next result: Configuring for: PHP Api
  • Export and download query results to Excel file in PHP from oracle
    // Export results require_once('PHPExcel.php'); $query = "SELECT DISTINCT TITLE, PID, TYPE, SUM(DAYCOUNT) AS tot, ROUND(SUM(DAYCOUNT)/( SELECT SUM(DAYCOUNT) FROM REPORT_LIST_VIEW), 4) AS per FROM REPORT_LIST_VIEW WHERE DAYCOUNT > '0' GROUP BY TITLE, PID, TYPE ORDER BY tot DESC"; if ($result = db_query($query)) { // Create a new PHPExcel object $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('List of Cities'); // Loop through the result set $rowNumber = 1; while ($row = $result->fetchAssoc()) { $objPHPExcel->getActiveSheet()->fromArray($row,NULL,'A'.$rowNumber++); } //
  • 甲骨文 + Oci8 + php5.6 + Ubuntu 16.04(Oracle + Oci8 + php5.6 + Ubuntu 16.04)
    问题 我安装了 Ubuntu 16.04 和PHP5.6 。 我下载并解压Oracle 12.1.0.1.0 ,并将其放在文件夹/opt/oracle/instantclient. Export ORACLE_HOME=/opt/oracle/instantclient /opt/oracle/instantclient. Export ORACLE_HOME=/opt/oracle/instantclient ,制作软链接: $ sudo ln -s libocci.so.12.1 libocci.so $ sudo ln -s libclntsh.so.12.1 libclntsh.so 之后,我添加到我的系统: $echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient 之后在/opt/oracle/instantclient/src新建一个文件夹,放入oci8-2.0.10.tgz版本, tar xzvf这个版本。 然后在文件夹中我执行$phpize并得到下一个结果: Configuring for: PHP Api Version: 20151012 Zend Module Api No: 20151012 Zend Extension Api No: 320151012 然后,我执行:
  • 【PHP+Oracle+CLOB】PHP处理Oracle中大文本CLOB数据的问题?
    MySQL中text类型与Oracle中的CLOB对应 <?php class OciModel { public $conn; public $table; public $seqname; public $where;//查询条件,字符串 public $lob;//lob字段名,数组 public $data;//数据数组 //架构函数 public function __construct($table='',$data=''){ //取TP框架的数据库配置 $this->connect('database.username','database.password','database.hostname'.':'.'database.hostport'.'/'.'database.database'); if(!empty($table)) $this->table=$table; if(!empty($table)&&!empty($data)){ $this->checkfield($table,$data); } } //检测字段属性 public function checkfield($table,$data){ $lob = null; if(!empty($table)&&!empty($data)){ $fields=$this->getFields(
  • 无法加载oci8->致命错误:调用未定义函数oci_connect()(can not load oci8 -> Fatal error: Call to undefined function oci_connect())
    问题 我想连接到我的oracle数据库,但是函数oci_connect遇到问题。 我试图取消注释: extension=php_oci8.dll 但是,仍然有一个错误: 致命错误:在第71行的C:\ xampp \ htdocs \ testing \ db.php中调用未定义的函数oci_connect() 但是,当我签入phpinfo()时,oci8仍未加载,并且无法连接到我的oracle数据库。 我已经重新启动了我的Apache。 我该如何解决这个问题? 你能帮助我吗? 谢谢.. 回答1 在使用PHP 5.6和64位操作系统的Ubuntu 16.04中,请尝试以下操作: 安装软件包: sudo apt-get install php5.6-dev build-essential php-pear libaio1 从以下位置下载最新版本的软件包: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html 搜索这些软件包,并使用最新版本: oracle-instantclientXX.X-basic-XX.X.X.X.X-X.x86_64.rpm oracle-instantclientXX.X-devel-XX.X.X.X.X-X.x86_64.rpm 转换包: sudo apt-get
  • 使用 Oracle 和 PHP:在 SQL Developer 中工作,但 PHP 文件结果 ORA-00900:无效语句(Using Oracle and PHP: Works in SQL Developer but PHP file results ORA-00900: Invalid Statement)
    问题 当我从我之前的文章“使用 Oracle 将三个表与 PIVOT 合并为一个”中获取代码并在 SQL Developer 中点击“运行脚本”时,一切正常,但是当我尝试从 PHP 文件执行相同的脚本时,我得到“ORA-00900无效的 SQL 语句” - 错误。 SQL Developers“运行语句”也无法执行代码。 似乎我的代码不在“SQL 语句”语法中? 我用于将 sql 脚本放入变量的 PHP 代码: $sql = "variable x REFCURSOR DECLARE exam_ids VARCHAR2(255); BEGIN SELECT LISTAGG('''' || exam_id || ''' AS \"' || exam_name || '\"',',') WITHIN GROUP( ORDER BY exam_id ASC ) INTO exam_ids FROM exam; OPEN :x FOR 'SELECT * FROM ( SELECT u.user_id, u.user_name, e.exam_id, eu.exam_date FROM users u LEFT JOIN exam_user eu ON u.user_id = eu.user_id LEFT JOIN exam e ON e.exam_id = eu.exam_id
  • 使用 PHP 在 Oracle DB 中插入数据(Insert Data in Oracle DB using PHP)
    问题 使用oci_8在 oracle DB 中插入数据。 插入带有特殊字符或引号的字符串的示例查询 update TABLENAME set COMMENTS = 'As per Mark's email dated 28-Feb-2015 - Bill Gates & Team's effort' where ID = 99; 插入/更新 $query = 'update TABLENAME set COMMENTS = '$_POST[comments]'; $result = customexecute($new_query); public function customexecute($query) { $resutlt = parent::customquery($query); return $resutlt; } public static function customquery($query) { try{ $stmt = oci_parse($conn, $query); oci_execute($stmt,OCI_COMMIT_ON_SUCCESS); oci_commit(db_singleton::getInstance()); oci_free_statement($stmt); }catch (Exception $e) { print_r($e)
  • 使用 PHP 在 Oracle 中将图像上传为 BLOB(Upload images as BLOBs in Oracle using PHP)
    问题 谁能告诉我如何使用 PHP 将图像作为 BLOB 存储在 oracle 数据库中? 一个工作示例会很好。 谢谢你。 回答1 您首先需要从$_FILES #global 数组中获取正在上传的图像: $image = file_get_contents($_FILES['image_field_name']['tmp_name']); 然后在数据库中插入图像试试这个: $sql = "INSERT INTO table (id, image) VALUES(1, empty_blob()) RETURNING image INTO :image"; $result = oci_parse($connection, $sql); $blob = oci_new_descriptor($connection, OCI_D_LOB); oci_bind_by_name($result, ":image", $blob, -1, OCI_B_BLOB); oci_execute($result, OCI_DEFAULT) or die ("Unable to execute query"); if(!$blob->save($image)) { oci_rollback($connection); } else { oci_commit($connection); } oci_free
  • Procedures of Oracle in php with PDO
    I'm having a problem with propel 1.6 and a oracle procedure. I post it under PDO because I'm Propel just for proxying my call to PDO. Basically the procedure get the user name and password, checks that is OK and return the user. For that reason it returns an types.cursorType. The sql start like this. CREATE OR REPLACE PROCEDURE "SP_LOGIN" (R_CURSOR OUT types.cursorType, UserId IN VARCHAR2, Password IN VARCHAR2) my php code is: $con = Propel::getConnection(); $sql = 'BEGIN SP_LOGIN(:CURSOR, :0, :1); END;'; $stmt = $con->prepare($sql); $result_arr; $stmt->bindParam(":CURSOR", $result_arr, PDO
  • 调用未定义的函数oci_connect()(Call to undefined function oci_connect())
    问题 我得到了这个错误。 Fatal error: Call to undefined function oci_connect() $conn = oci_connect('localhost', 'username', 'password') or die(could not connect:'.oci_error) 那就是代码。 这是我得到的错误。 Fatal error: Call to undefined function oci_connect() in C:\xampp\htdocs\org\org\php\myphp.php on line 51 我检查了ext文件夹中的PHP DLL文件。 在这个问题上有人帮助,在此先感谢。 回答1 我只花了整整三天的时间来解决这个问题。 我在Windows 7中使用ORACLE连接,没有问题。 上周,我刚得到一台装有Windows 8的新计算机。安装XAMPP 1.8.2。 此服务器上的每个应用程序PHP / MySQL都可以正常运行。 当我尝试将php应用程序连接到Oracle DB时出现了问题。 调用未定义的函数oci_pconnect() 当我通过更改启动/停止Apache时,在“ PHP启动”上出现一个奇怪的“警告”,并在日志中显示“ PHP警告:PHP启动:在第0行中处于未知状态” 我做了所有工作(未注释php
  • 使用 PHP 限制结果,在数据库查询中获取行时不使用 LIMIT 子句?(Limiting a result using PHP, without using LIMIT clause when fetching rows in database query?)
    问题 我有一个 SQL 表,它包含大约 100 行。 我在 PHP 中使用SELECT * FROM table选择它。 我没有使用偏移量或限制。 我现在的问题是,有没有办法只使用 PHP 来操作返回的结果,只显示表中的 24 行? 我应该尝试什么? 回答1 从数据库获取行的一般格式如下所示: while ($row = mysql_fetch_assoc($rs)) { echo $row['column_name'] . "\n"; } 你会想像这样修改它: $limit = 24; while ($limit-- && $row = mysql_fetch_assoc($rs)) { echo $row['column_name'] . "\n"; } 回答2 好吧,我想现在添加了oracle标签,我将添加 oracle 答案。 $rows = array(row, row, row, ...); // from oracle sql results $first_24_rows = array_slice($rows, 0, 24); 回答3 您可以在 oracle SQL 本身中控制返回的行数。 在 where 子句中放入类似的内容: 和 rownum <= 23