John Davidson

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