I am attempting to perform a SQL call that fetches a row from one table and counts the records from another table. However, the call only returns results from the first SELECT. Code is as follows:
$sql = "SELECT row_1 FROM table_1 WHERE user_id = :user_id UNION SELECT COUNT(row_2) as new_var FROM table_2 WHERE user_id = :user_id"; $query = $this->db->prepare($sql); $params = array('user_id' => $var); $query->execute($params); $result = $query->fetch();
The result should be:
array( 'row_1' => val_1, 'new_var' => val_2 )
The solution to the problem is undoubtedly simple, yet eludes me! Thanks in advance!
It would appear UNION was inappropriate in this case. Revamping the SQL call to use JOIN solved the issue:
SELECT table_1.row_1, COUNT(table_2.row_2) AS new_var FROM table_1 LEFT JOIN table_2 ON table_1.user_id = table_2.user_id AND table_1.user_id = :user_id
Thanks for all your help!