Jump to content
Fabel

PHP (oop - pdo) prepared statements error

Recommended Posts

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 by Fabel

Share this post


Link to post
Share on other sites

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 by taquitosensei

Share this post


Link to post
Share on other sites

 

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
 
 

Knipsel.PNG

Edited by Fabel

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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;

 

Share this post


Link to post
Share on other sites
		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 by Fabel

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 by Fabel

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
			$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 by Fabel

Share this post


Link to post
Share on other sites

"$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.)

Share this post


Link to post
Share on other sites
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 by Fabel

Share this post


Link to post
Share on other sites

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();
}

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.