1

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
bhaswanth
  • 76
  • 1
  • 8
  • 2
    The problem is most likely with your PHP code. As a side note, you don't necessarily even need to use a prepared statement here, as the SQL query appears to be fixed. – Tim Biegeleisen Oct 30 '19 at 05:24
  • 1
    @TimBiegeleisen It looks like this may be a simplification of the real application, notice the commented-out call to `bind_param()`. – Barmar Oct 30 '19 at 05:25
  • 1
    Make sure you enable mysqli error reporting: https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments – Barmar Oct 30 '19 at 05:28
  • 1
    Couple of things here... 1) `stmt_init()` is redundant if you just call `prepare()` later and overwrite `$query`. 2) You probably don't need `store_result()`. 3) Why even bother with `num_rows`? – Phil Oct 30 '19 at 05:29
  • Having done `$query = $db->stmt_init();` this line `if($query = $db->prepare($sql))` should be `if($query->prepare($sql))` – Nick Oct 30 '19 at 05:31
  • The statement of question is wrong. The NOT NULL SQL statement has nothing to do with prepared statements. It means your problem is elsewhere and this question, therefore, is off topic. – Your Common Sense Oct 30 '19 at 05:39

1 Answers1

-2

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
Raheel Khan
  • 32
  • 4