I am trying to retrieve the data from database table where column approved_date is not null. For database access, I am using MySQL object-oriented prepared statement. The query which I have written already is giving empty results while the same query is getting the correct result when I tried in PhpMyAdmin.
I tried to run the same query which I wrote for the prepared statement as raw query in PhpMyAdmin. There it worked perfectly but comes to the code, it is not working.
Below is the code I used for the Prepared statement.
public function get_approved_problems()
{
$data = array();
$db = DB::open();
$day = date('d');
$month = date('m');
$sql = "SELECT id, user, title, description, address, publish, created_at
FROM problems
WHERE approved_date IS NOT NULL AND publish=1
ORDER BY id DESC";
$query = $db->stmt_init();
if($query = $db->prepare($sql))
{
// $query->bind_param('ii', $month, $day);
$query->execute();
$query->store_result();
$query->bind_result($id, $user, $title, $description, $address, $publish, $created);
$numrows = $query->num_rows;
if($numrows > 0)
{
while($query->fetch())
{
$data[] = array(
"id" => $id,
"user" => $user,
"title" => $title,
"description" => $description,
"address" => $address,
"publish" => $publish,
"created_at" => $created
);
}
}
$query->close();
}
return $data;
}
The above code is returning the empty result. While the below RAW query is working fine on PhpMyAdmin.
SELECT id, user, title, description, address, publish, created_at FROM problems WHERE approved_date IS NOT NULL AND publish=1 ORDER BY id DESC
The code doesn't show any error messages even its not working as I expected.
Try this:
SELECT id, user, title, description, address, publish, created_at
FROM problems
WHERE publish=1 AND approved_date IS NOT NULL
ORDER BY id DESC
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments