PHP (oop - pdo) prepared statements error

[Fabel]

Hello there

 

I'm really struggeling with my prepared statement. I want to get the number of unread PMs to show at the user's homepage. Can someone help me with some explanation about my code? I don't really know what I'm doing yet. I've read a lot of code about prepared statements, but I still can't figure it out. 

public function count_unread_pm() {
        if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1")) {

            /* Bind parameters, s - string, b - blob, i - int, etc */
			$stmt->bindParam(':unread', $id, PDO::PARAM_INT); 
            $stmt -> execute();

            var_dump($stmt); // no output 

            /* Bind results */
            $stmt -> bind_result($test); // Error: call to undefined method 

            /* Fetch the value */
            $stmt -> fetch();
            $numberofrows = $stmt->num_rows;

            /* Close statement */
            $stmt -> close();
        }
        var_dump($numberofrows); // no output 

    }

Edited September 16, 2019 by Fabel

[taquitosensei]

What does the unread table look like. You only need to bind when you're putting user submitted data in your query. You don't have to do that with unread=1. Also unread shouldn't ever equal an id, because it's probably a boolean. Your query should probably look more like this 

if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and id_field=:id")) {
	$stmt->bindParam(':id', $id, PDO::PARAM_INT); 
}

 

Edited September 16, 2019 by taquitosensei

[benanamen]

Get rid of all the space in your method calls.

[Fabel]

 

20 minutes ago, taquitosensei said:

What does the unread table look like. You only need to bind when you're putting user submitted data in your query. You don't have to do that with unread=1. Also unread shouldn't ever equal an id, because it's probably a boolean. Your query should probably look more like this 

if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and id_field=:id")) {
	$stmt->bindParam(':id', $id, PDO::PARAM_INT); 
}

 

What do you mean with 'unread shouldn't ever be equal an id, because its probably a boolean' ?

In the picture is shown how I designed my table. I'm a beginner, so let me know if if you know a better way to design my directory and table

I get the same error when I change my code:

Fatal error: Call to undefined method PDOStatement::bind_result() in C:\xampp\htdocs\core\class\user.php on line 108

EDIT:

object(PDOStatement)#5 (1) { ["queryString"]=> string(47) "SELECT unread FROM pm WHERE unread=1 and id=:id" }

this is the output when I vardump the $stmt after bindparam
 

 

Edited September 16, 2019 by Fabel

[taquitosensei]

a boolean is 1 or 0, true or false. Usually a column labelled as "unread" would be a boolean telling you whether it had been read or not. If it's supposed to hold the user's id, you should rename it. But after looking at your table here is what the query should look like. 

$stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and receiver_id=:id")

This would get all of the results that were sent to your user that have not been read. You don't need the bind_result line. I believe that's a mysqli function and not needed with pdo. 

[Fabel]

Thank you for your help. 

This the code I now have:

	public function count_unread_pm() {
		if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and receiver_id=:id")) {

			$stmt->bindParam(':id', $id, PDO::PARAM_INT); 
			$stmt->execute();
			
			var_dump($stmt);


			/* Fetch the value */
			$stmt->fetch();
			$numberofrows = $stmt->num_rows;

			/* Close statement */
			$stmt->close();
		}
		var_dump($numberofrows);
	}

This is the output:

object(PDOStatement)#5 (1) { ["queryString"]=> string(47) "SELECT unread FROM pm WHERE unread=1 and id=:id" }
Notice: Undefined property: PDOStatement::$num_rows in C:\xampp\htdocs\core\class\user.php on line 111

Fatal error: Call to undefined method PDOStatement::close() in C:\xampp\htdocs\core\class\user.php on line 114

The bind->result was in an explanation of the code. Is there an efficient way to count the unread messages? 

[gw1500se]

You need to RTFM. 'fetch' returns a result and you are not assigning it. That result object is what you check for rows.

/* Fetch the value */
			$result=$stmt->fetch();
			$numberofrows = $result->num_rows;

 

[Fabel]

		if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and receiver_id=:id")) {

			$stmt->bindParam(':id', $id, PDO::PARAM_INT); 
			$stmt->execute();
			
			$result=$stmt->fetch();	
			var_dump($result); // 1

			$numberofrows = $result->num_rows;

		}
		var_dump($numberofrows); // 2

 

Notice: Trying to get property of non-object in C:\xampp\htdocs\core\class\user.php on line 107

var_dump 1: Bool(false)

var_dump 2: NULL

 

Why does 'receiver_id' has to be equal to ':id'?

Edited September 16, 2019 by Fabel

[gw1500se]

6 minutes ago, Fabel said:

if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1 and receiver_id=:id")) {

Why are you doing this in an if block?

[Fabel]

5 minutes ago, gw1500se said:

Why are you doing this in an if block?

After I tried some ways I saw a stackoverflow comment, the answer  worked was with the 'if' block. I though, if I'm going to change parts without understanding the whole code, I might not be sure what is going wrong, but sadly enough I wasn't sure anyway. I removed it from the code but nothing changed.

Edited September 16, 2019 by Fabel

[gw1500se]

You need to add error checking but in the right places. Like after the 'execute'. In any case you need to be more specific about what you are trying to query.

[Fabel]

19 minutes ago, Fabel said:

Why does 'receiver_id' has to be equal to ':id'?

Could someone explain this to me? I read the manual but I think I missed something and the words which are used are sometimes too complicated for me to completely understand. 

[gw1500se]

That was a suggestion. If you want to query unread for receiver_id that is what it does. If you want to query any unread then leave that off. As I  asked, you need to be more specific about what you are trying to query.

[Fabel]

			$stmt = $this->db->prepare("SELECT COUNT(*) FROM pm WHERE unread=1 and receiver_id=:id");

			$stmt->bindParam(':id', $id, PDO::PARAM_INT); 
			$stmt->execute();
			
			var_dump($stmt); 
			
			$result=$stmt->fetch();	
			echo '<br>'; var_dump($result); 
			
			$numberofrows = $result->num_rows;
			var_dump($numberofrows);

I've add COUNT(*) and now the var_dump after $results gives: array(2) { ["COUNT(*)"]=> string(1) "0" [0]=> string(1) "0" } and I have 2 rows in my database, both 'unread: 1'

the first var_dump shows this:object(PDOStatement)#5 (1) { ["queryString"]=> string(58) "SELECT COUNT(*) FROM pm WHERE unread=1 and receiver_id=:id" }
I still get this: Notice: Trying to get property of non-object in C:\xampp\htdocs\core\class\user.php on line 110

1 minute ago, gw1500se said:

you need to be more specific about what you are trying to query.

For example: when I log into my homepage, the table contains 2 unread messages where my user_id equals receiver_id. I want my homepage to show '2 unread messages' or something like that. Therefor I need this object, but I can't fix what is going wrong cause of my lack of knowlegde. So my next step, and the reason I'm asking this question here, is understanding this script. 

 

could you explain to me what is happening here: $stmt->bindParam(':id', $id, PDO::PARAM_INT); how does receiver_id=:id stop the query from looking all rows? 

If you know a useful, well explained, website or video about pdo I am happy to read it. 

Edited September 16, 2019 by Fabel

[Barand]

"$result->num_rows" is not PDO.

The PDO equivalent is $stmt->rowCount();

You cannot mix mysqli calls with PDO calls. Manual - PDO

(If you use SELECT COUNT(*) then only one row containing the count will be returned.)

[Fabel]

16 minutes ago, Barand said:

"$result->num_rows" is not PDO.

The PDO equivalent is $stmt->rowCount();

You cannot mix mysqli calls with PDO calls. Manual - PDO

(If you use SELECT COUNT(*) then only one row containing the count will be returned.)

Thank you, after I searched for rowCount I found this: 

    $sql = 'SELECT id FROM pm WHERE unread=1 AND receiver_id=2';
    $stmt = $this->db->prepare($sql);
    $stmt->execute();
   
	$stmt->fetch();	
	$result = $stmt->rowCount();
	var_dump($result);

This seems to work fine. Thanks for the help

Edited September 16, 2019 by Fabel

[Barand]

Try

function unread_count( $id )                                // pass id of receiver to the function
{
    $stmt = $this->db->prepare("SELECT COUNT(*) as total
                                FROM pm
                                WHERE unread = 1
                                    AND receiver_id = :id
                               ");
    $stmt->execute( [ 'id' => $id ] );
    return $stmt->fetchColumn();
}

 

[Fabel]

9 minutes ago, Barand said:

Try

 

It works perfect. Thank you!