John Davidson

php - SQL_CALC_FOUND_ROWS Returns 0 in SELECT INSERT Query - Doctrine

0 comments
Message:


Below are my table data


TABLE Name: table_1
id value created_date
------ ------ ---------------------

TABLE Name: table_2
id value created_date
------ ------ ---------------------
1 A 2021-12-17 17:22:55
2 B 2021-12-17 17:22:56
3 C 2021-12-17 17:22:57
4 D 2021-12-17 17:22:58

When I execute the below query in SQL Tool, I am getting FOUND_ROWS() value.


INSERT INTO table_1 (`value`)
SELECT SQL_CALC_FOUND_ROWS `value` FROM table_2;
SELECT FOUND_ROWS();


When I tried to execute in PHP Doctrine statements. It is not working!


$statement = $entityManager->getConnection()->prepare("INSERT INTO table_1 (`value`)
SELECT SQL_CALC_FOUND_ROWS `value` FROM table_2 LIMIT 1000");
$statement->execute();

$statementFoundRow = $entityManager->getConnection()->prepare("SELECT FOUND_ROWS()");
$statementFoundRow->execute();
echo $foundRow = $statementFoundRow->fetchColumn(0);exit;

Output: 0


But I am getting values when I am not using the INSERT Query.


$statement = $entityManager->getConnection()->prepare("SELECT SQL_CALC_FOUND_ROWS `value` FROM table_2 LIMIT 1000");
$statement->execute();

$statementFoundRow = $entityManager->getConnection()->prepare("SELECT FOUND_ROWS()");
$statementFoundRow->execute();
echo $foundRow = $statementFoundRow->fetchColumn(0);exit;

Output: 4


Can someone help me with 0-row issue?


TABLE DATA for reference:


CREATE TABLE `table_1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) DEFAULT NULL,
`created_date` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `table_2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) DEFAULT NULL,
`created_date` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `table_2`(`id`,`value`,`created_date`) VALUES
(1,'A','2021-12-17 17:18:15'),
(2,'B','2021-12-17 17:18:16'),
(3,'C','2021-12-17 17:18:17'),
(4,'D','2021-12-17 17:18:19');


My Answer:
> createNativeQuery("INSERT INTO table_1 (`value`) SELECT SQL_CALC_FOUND_ROWS `value` FROM table_2", $rsm);
$statement->execute();

$foundRows = $entityManager->getConnection()->fetchColumn("SELECT FOUND_ROWS()");

echo $foundRows; // This will output 0

The reason you are getting 0 as the result of FOUND_ROWS() in PHP Doctrine is because the SQL_CALC_FOUND_ROWS function is not supported in INSERT queries in Doctrine. This function is typically used in SELECT queries to get the total number of rows that would have been returned without a LIMIT clause.

If you need to get the number of rows affected by the INSERT query, you can use the rowCount() method after executing the query:

php
$rowCount = $statement->rowCount();
echo $rowCount; // This will output the number of rows affected by the INSERT query


Alternatively, if you need to get the total number of rows in table_2, you can execute a separate SELECT query to get the count:

php
$count = $entityManager->getConnection()->fetchColumn("SELECT COUNT(*) FROM table_2");
echo $count; // This will output the total number of rows in table_2

Rate this post

3 of 5 based on 2255 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us