Fabel

This is not the safe way of doing it. And if you use this you don't need the $stmt->bindParam, because there is no ':id' to bind a parameter to. (am I explaining that correctly?) Barand gave you a working example of the script. Try to type that exactly as how Barand typed it, it should be working.

did you give $nid a value? And 'nid' stands for 'nurs id'? Maybe you could call it, like patient_id, also nurse_id? But that's personal. Maybe you could var_dump the query and screenshot that with of the error? You could also try to use a query like 'SELECT id FROM ward_allotment WHERE nurse=2' to check if the query itself works.

try WHERE wa.nurse=? or you can go with: WHERE wa.nurse=:nid $stmt->bindParam(":nid", $nid); $stmt->execute(); @Barand already showed you this and he showed you how to make your query beter readable:

Because it's going fairly wrong... And yes I can just do it without the 'RE:' but I wanted to make clear that those titles where 'replies', so that I could show you that my code doesn't work. You suggest I need 2 tables? That's a good idea. Thanks for the insight! With 'reply' I mean a reaction to an email. wether it's your own email or someone else's. I would have to make a new account to show actual replies and I didn't think it would be much of a difference, but if it helps you to understand the output I can do that.

Ugh it's a bad query. I have to accept that I can't figure it out without help. A lot of things in my query are, as you said, going wrong. When user1 sends one email and replies to it 2 times, my query will just grab them all. The count doesn't work too. ---- I substract one (the outer conversation) inline to get the right number: <td><?php echo $row1['replies']-1; ?></td> ---- I don't want users to be able to change the title when they reply to an existing email. I want the title of the last unread message. I don't want to make it even more complex for myself so when they open a conversation/email, I want to set all the incoming unread replies in that email as read. I want to count all the replies in one conversation, not only the unread. I want to make a small email inbox. Thank you for taking the time to try to understand it. I hope I've explained it better now. Fabian

$stmt = $this->db->prepare(' SELECT pm1.title, pm1.timestamp, u.username as sender, (SELECT COUNT(pm2.id) FROM pm as pm1, pm as pm2 WHERE pm1.parent_id=pm2.id) AS replies FROM pm as pm1, pm as pm2, users as u WHERE pm1.receiver_id=:user_id AND pm1.unread=1 AND pm1.sender_id=u.id AND pm1.parent_id=pm2.parent_id AND pm2.parent_id=pm2.id'); This seems to work for now. I hope it actually will keep working 😂 Thanks anyway

Nope I didn't figure it out completely, I still have a problem: with: LEFT JOIN users AS u ON u.id=pm1.sender_id I get this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pm1.sender_id' in 'on clause'' in C:\xampp\htdocs\core\class\pm.php:32 Stack trace: #0 C:\xampp\htdocs\core\class\pm.php(32): PDOStatement->execute() #1 C:\xampp\htdocs\list_pm.php(83): Pm->unread_pm() #2 {main} thrown in C:\xampp\htdocs\core\class\pm.php on line 32 doing it this way won't result in an error but will always give the username of the user who send the original message, not the username of the user who last replied at the original message LEFT JOIN users AS u ON u.id=pm2.sender_id

Damn I think I figured it out: FROM pm as pm1, pm as pm2 LEFT JOIN users AS u ON u.id=pm2.sender_id WHERE pm1.receiver_id=:user_id AND pm1.unread=1 AND pm1.parent_id=pm2.parent_id AND pm2.parent_id=pm2.id');

Hello. My first topic here was about getting the amount of unread messages. I'm now working on showing the unread messages in the inbox. Let's say we have 2 users. User1 sends user2 a message with the title: this is a title. User2 read the message and replied. User1 now has to see: RE: this is a title. This is the code in: class Pm {} public function get_unread_pm() { $stmt = $this->db->prepare(' SELECT pm.title, pm.sender_id, pm.timestamp, (SELECT COUNT(pm2.id) FROM pm as pm1, pm as pm2 WHERE pm1.parent_id=pm2.id) AS replies, u.username as sender FROM pm LEFT JOIN users AS u ON u.id=pm.sender_id WHERE pm.receiver_id=:user_id AND pm.unread=1 AND pm.parent_id=pm.id'); $stmt->bindParam('user_id', $_SESSION['userid']); $stmt->execute(); return $stmt->fetchAll(); } I now get the original message because of the pm.parent=pm.id, but I want to get the message where the parent_id of a reply is the same parent_id as where the parent_id is equal to it's id. I think a less complicated description is: How do I get the last reply instead of the original message? parent_id: When I reply to a message, my id still counts up, the parent_id is the id of the original message. This is what my data-table looks like: The content: If I'm doing this stuff really inefficient, I would be happy to know how to make it better Fabian

Password or text?

I really want to thank you for the information! User Enumeration attack, the SOLID principal, et cetera. It's very valuable to me. I updated my code and it works smoothly. public function regUser($uname,$upass,$upassverify) { $new_password = password_hash($upass, PASSWORD_DEFAULT); if(!password_verify($upassverify, $new_password)) { // passwords are not the same (I thought it would be better to do this after hashing, but maybe it doesn't matter or it's worse. I'm not sure about it) return false; } $stmt1 = $this->db->prepare("SELECT username FROM users WHERE username=:uname"); $stmt1->execute(array(':uname'=>$uname)); try { $stmt2 = $this->db->prepare("INSERT INTO users(username,password) VALUES(:uname, :upass)"); $stmt2->bindparam(":uname", $uname); $stmt2->bindparam(":upass", $new_password); $stmt2->execute(); // succesfully made an account return true; } catch (PDOException $e) { return false; } } my final question for today: should I hash the passwords before comparing them or after?

The errors I want to return will make the $var->regUser true, without succes. If I would do it this way, how then could I trow the different errors? Btw, the unique username solves the resubmission problem as well. Thanks

I now have set 'username' to unique. Thanks! Should I make a differrence between capital letters and none capital letters? Someone makes an account with the name 'fabian' and someone else 'FaBiAn', should I give it an error, or just let it be? I don't know what that means. I read a stackoverflow question with the answer but I still don't know how I'm supposed to use this. If I understand it correctly, you also suggest that my code should look like this: try { $stmt2 = $this->db->prepare("INSERT INTO users(username,password) VALUES(:uname, :upass)"); $stmt2->bindparam(":uname", $uname); $stmt2->bindparam(":upass", $new_password); $stmt2->execute(); } catch { if(){ } else { } } is that true?

Hello, I hope it's ok to ask this question here. I have a registration script, but I'm not sure how to handle it efficiently and I have some questions about it. This is used in the page 'signup.php'. The class is called 'User'. I haven't noticed any errors or bugs. It would be very useful for me to be aware of my mistakes. public function regUser($uname,$upass,$upassverify) { $new_password = password_hash($upass, PASSWORD_DEFAULT); if(!password_verify($upassverify, $new_password)) { // passwords are not the same (I thought it would be better to do this after hashing, but maybe it doesn't matter or it's worse. I'm not sure about it) $info = 'pass_err'; } $stmt1 = $this->db->prepare("SELECT * FROM users WHERE username=:uname"); $stmt1->execute(array(':uname'=>$uname)); if($stmt1->rowCount() > 0) { // this username has already been used $info = 'user_err'; } if (!$info) { $stmt2 = $this->db->prepare("INSERT INTO users(username,password) VALUES(:uname, :upass)"); $stmt2->bindparam(":uname", $uname); $stmt2->bindparam(":upass", $new_password); $stmt2->execute(); // succesfully made an account $info = "success"; } header("Location:/signup.php?status=".$info); exit(); } Am I using the prepared statements as how I should be using them? Is this a safe way of handling my data or do you see vulnerabilities? I'm using PRG to prevent resubmission but I want to show a 'everything is fine' or 'oh no, something went wrong' to the one who is signinup. If I now go to signup.php?status=success, i see 'eveything is fine', without actually signing up, is there a better way to do this or can I somehow prevent everyone being able to see this? As you might have noticed in my last post, my English is not very good, sorry about that. Thanks, Fabian

It works perfect. Thank you!

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

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

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.

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.

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'?

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?

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

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 }