SQL query doesn't get the rows successfully

Question

I have tried to retrieve data from database using the SQL query but it doesn't get the rows successfully

it tells me the rows are empty in PHP but when I query the database directly with the query it works. I don't know what I'm not doing.

// read all message records
function readAllByTopicId($from_record_num, $records_per_page, $topic_id){
  // query to read all message records, with limit clause for pagination
  **$query = "SELECT * FROM " . $this->table_name . " ps WHERE ps.published=1 and ps.id IN (SELECT pt.post_id FROM post_topic pt WHERE pt.topic_id=? GROUP BY pt.post_id HAVING COUNT(1)=1) ORDER BY id DESC LIMIT ?, ?";**
  // prepare query statement
  $stmt = $this->conn->prepare( $query );
  // bind limit clause variables
  $stmt->bindParam(1, $from_record_num, PDO::PARAM_INT);
  $stmt->bindParam(2, $records_per_page, PDO::PARAM_INT);
  $stmt->bindParam(3, $topic_id, PDO::PARAM_INT);
  // execute query
  $stmt->execute();
  return $stmt;
}

score 0 · Answer 1 · answered Jul 13 '20 at 03:01

Try debug the statement to ensure you have the data you expected in the query...

$stmt->debugDumpParams();

Otherwise maybe a join?

$query = "
SELECT * 
FROM 
`" . $this->table_name . "` AS ps INNER JOIN
 ( SELECT pt.post_id FROM post_topic pt WHERE pt.topic_id=?       
     GROUP BY pt.post_id HAVING COUNT(1)=1) AS pt
 ON ps.id = pt.post_id
WHERE ps.published=1 and ps.id IN 
ORDER BY id DESC 
LIMIT ?, ?";

SQL query doesn't get the rows successfully

1 Answers1