Jack Jack - 1 month ago 5
MySQL Question

MYSQL Query & PHP - Query returning nil to IOS App

I am calling a function from my IOS app which calls the function in PHP file which then should return the data retrieved, but it is return nil since I added a new part to my query.

My new query is working in the MYSQL tester service on my DB, but strangely is returning nil in my app.

Here is how I have it all set up in my PHP file:

function fetchAssocStatement($stmt)
{
if($stmt->num_rows>0)
{
$result = array();
$md = $stmt->result_metadata();
$params = array();
while($field = $md->fetch_field()) {
$params[] = &$result[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
if($stmt->fetch())
return $result;
}

return null;
}

// Select all posts + user information made by user with relevant $id
public function selectPosts($id) {

// declare array to store selected information
$returnArray = array();

/*
// sql JOIN
$sql = "SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
Posts.imageHeight,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage
FROM Posts JOIN USERS ON
Posts.id = $id AND USERS.id = $id ORDER by date DESC
LIMIT 0, 5";
*/
$sql = "SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
Posts.imageHeight,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage,
coalesce(A.LikeCNT,0)
FROM Posts INNER JOIN USERS ON
Posts.id = $id AND USERS.id = $id
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost
FROM Activity A
WHERE type = ""
GROUP BY A.UUIDPOST) A
on A.UUIDPost = Posts.uuid
ORDER BY date DESC
LIMIT 0, 5";


if($stmt = $this->connection->prepare($sql))
{
$stmt->execute();
$stmt->store_result();

while($row = $this->fetchAssocStatement($stmt))
{
$returnArray[] = $row;
}

//$stmt->close();
}

return $returnArray;


}


As you'll be able to see I have a commented out query, which was my previous query for getting the posts(which worked great). But now that I am trying to get the "like count" for all the images, in my app it is return nothing...

If anyone can see one, please let me know!

Thanks in advance!!

Answer

You need to trap for errors. I think the SQL being executed is throwing an error and you're not trapping it; which is why no records are being returned. The type = "" seems to be an error in my mind. Either you want type is null or type = '' (empty set) but not type = "" or maybe you want type = 'somevalue' or no value for type at all. (eliminate the where clause entirely) I'd start there (eliminate where clause) and if you get results then you know the problem is with the where clause

Comments