17: Query A Database Using OOP PHP | Prepared Statements Using PDO | Object Oriented PHP Tutorial

In this OOP PHP tutorial, I will show you how to query a database using OOP PHP. We will be going over selecting, inserting, and updating database content using PDO and prepared statements. Prepared statements are important as part of a more secure database connection.
➤ GET ACCESS TO MY LESSON MATERIAL HERE!
First of all, thank you for all the support you have given me!
I am really glad to have such an awesome community on my channel. It motivates me to continue creating and uploading content! So thank you!
I am now using Patreon to share improved and updated lesson material, and for a small fee you can access all the material. I have worked hard, and done my best to help you understand what I teach.
I hope you will find it helpful :)
Material for this lesson: / lesson-material-42361704

[@gabrielfono844]

after teaching myself coding full time , I recently got hired as software engineer in seattle waghinston. I remember started tutorials with your html and css. now , they require me to learn php oop, design pattern in two weeks before getting started. everything makes sense for me since I really understand OOP in javascript. this is why it is so crucial to know one language well. thanks Dani krossing.

[@andresalvarez7303]

I think the most valuable thing about your tutorials is that you say what you're doing but also WHY you're doing something. It's that information that helps bridge gaps in learning and has helped me have a lot of answered questions about the code. I always find myself asking WHY immediately after learning how to do something. Thanks for making every video feel like more than just a set of rigorous instructions without reason behind them.🙌

[@MMH94MMH]

Its 2022 and this series still very helpful, Thank you so much Daniel this OOP-Series helped me a lot, a appreciate your effort.

[@lawrencemichael5571]

20 minutes of brilliance. Some guys are lucky to sit in class in real life with this man as their professor.

[@xyzxyz2734]

My professor linked most of your videos - so great job! He is quite picky, when it comes to exact education/explanations. So thats like a TÜV-Approved for your content. And you did your job great! Nice help for my fisrt php project. Thank you so much.

[@techbeauty1015]

There are two channels which are my favorite . That are mmtuts and all type tutorials. Well Done brother.

[@geoclaps]

Very nice, thank you! I extended this this by combining it with an earlier tut you made with the calculator. But instead of calculating I let a user type their first name, last name and dob and when they submit it, it creates a user inside the table. Not really that special but your tuts made that possible, once again thanks a lot!

[@mh5854]

I got full marks in college in a web programming class. thanks.

[@astijusarimavicius1948]

i wish i had such easy classes

[@NicolasLagiosOfficial]

Thank you Dani, you helped me a lot. I had to watch your series first in order to understand OOP PHP in order to start learning Laravel. Thank you very much 😄

[@ambotlang1810]

I like the way you explain even no programming knowledge don't lost the topic.

[@AbJadzera]

Absolute LEGEND!!! Much love and respect!

[@sugamkarki697]

I am very picky about the channels I subscribe to and my good sir, you are one of them.

[@Snakebite420]

cant wait til the next episode! Thanks for being an awesome teacher!

[@stopit405]

Dani, I was frustrated with this subject. I was trying to make sense of the clases and the examples provided by the professor. After spending 35 minutes watching your videos i fanally got it. Thank you man.

[@renypacheco4812]

Thanks for this video,, we are learning web development in school.. This helps me a lot... More power

[@chyjohn6502]

Thanks a lot Dani for these really helpful videos. I just want to say something about 12:55-13:35, I might be wrong, I don't know, you obviously know more than a lot of us, but I think FETCH works just fine with or without prepared statements, you just have to use WHILE LOOP to spit out multiple records, whereas FETCHALL fetches everything at once removing the need to use the WHILE LOOP.

[@ma.reynalinjoypahayahay3302]

Thank you for creating this video! I finally solve my 4 hours error in php

[@TheLauchiz]

You really have a knack for teaching. Thank you for these amazing videos, truly appreciate it.

[@edgar1906]

Heyyy I love these tutorials! You should do a full-stack course with angular or react for front-end using php for the back-end. That would be the perfect combination! :D

[@pbznt]

Vanilla JS and custom css is way better than any front-end framework and libraries. Just like we are doing now. We don't use Laravel. We use pure PHP. Always go for the pure alternative, if you're not in a context of work where you are limited time to accumplish a task. ;)

[@adedayoomotoso9340]

You are the best, simple and unique.

[@Star-zf8su]

Thanks very much 😊😀....I was eagerly waiting for the lesson..... I hope I'll get another lesson very much soon.....😁😁

[@sivap8984]

Can you make the tutorial for laravel framework in php?

[@Born2DoubleUp]

I'm here! 😎 Great video, just posting a comment to show some support! Keep up the good work. 🤙

[@gonzaloalbornoz3220]

Thank you very much, greetings from Argentina

[@abdessamadelidrissi5884]

thank you very much teacher, i like your style of teaching

[@omerkarebek8809]

Thanks, Dani. You made my day!

[@rashid.m-li]

Waiting for the next Episode...Thank You ;) Class

[@nassimamoufakkir7519]

too much lovve from MOROCCO !!! thank u soooooooo much

[@LateBenji]

Må indrømme at dine videoer hjælpe meget når man er igang med at tage en Webudvikler uddannelse :)

[@yungifez]

Dani would be like Wow good to see someone my kind Rest of audience: what does this mean

[@khemiljariwala]

Just love the way you teach sir!, i find your videos very taughtful, but can you make more videos on topics like ReactJs, AngularJs, etc, where you can explain every concept of those language topic by topic.

[@JamesAutoDude]

Freaking best tutorial I've ever seen with awesome explanations

[@jambodjembe]

People are so lazy: There should be way more like as these tutorials are excellent. Many Thanks

[@Jam....]

Subscribed, excellent tutorials.

[@imane5971]

you saved my life man

[@stuknowlton477]

Awesome Thank you!

[@JIGA1up1]

Perfect!! I was waiting for the next episode, when Will this course finish?

[@danielsantiago11]

Thank you!

[@hayatzee8830]

Thank you Dani!

[@ivansavchenco5636]

So ok... Then it is going to be new hat every episode. Nice:)

[@osmanalp4110]

Thank you very much 😀

[@Amanitian]

Thanks thanks thanks!!!

[@alwaha4283]

tnx brother ....

[@okaforchinyerematthew1937]

Hello Dani Thanks a lot for this awesome tutorial, your teaching style is one in a million. Dani for some reason i don't know i try using while loop to convert the data return from database into array it failed, but i changed it to foreach loop and it worked, then i come upon stackoverflow there was an answer that you cannot use while loop with fetchAll() function in PDO rather foreach loop is right. But in your example you use while loop and it worked for you.

[@Dani_Krossing]

I can explain that. 🙂 In my example I am fetching the database results one at a time using "fetch", and not using "fetchAll". This means that I can use a while loop since I am only fetching one at a time. What you are trying to do however, is to grab ALL the data at once using "fetchAll", not one at a time, and insert into an array. Therefore a while loop would only run once and not do anything, since you get ALL the data the first time. That's why a "foreach" loop is working, since you are creating a multidimentional array that way. However just to mention it, it is also still possible to create an array with the results like you are trying to do, but with a "while" loop instead of "foreach". You just need to create an empty array before the "while" loop, and then inside the while loop just insert the data into the empty array during each run. 😊

[@jvoynar5826]

I had a similar problem. I changed the '' to "", and it displayed all items in that column. Otherwise it only displayed the first. Using mariaDB on macOS terminal to interact w/ my database/table. Anyway, thank you Dani !

[@kfchotwings]

Hey mmuts nice tutorial! But when are you going to make a router tut? looking forward to that

[@pbdaemon]

Thanks.

[@aguswidi9316]

Mr. Daniel, I have learned a lot from you especially oop php. Could you make another video teaching oop php with mvc, database, and using transaction, commit, rollback feature. Thank's

[@VitalijMik]

Thanks for the tutorial but please bare in mind that this code is just for the Tutorial Purpose, this is not industry standard, you should not extend from the database connection, rather injecting the connection into your class over constructor, usually youre using a dependecy container such as PHP-DI for this. Also you should not prepare the statement everytime you call getUsersStmt, prepare it once in constructor or something and then just call execute with the new parameters. And also do not use PHPMyADmin on live system since it has too many security issues while noone is dating it up. But anyways thanks for this tutorial which helps the PHP Language a lot

[@mahsapourshahmari8458]

that is what I call a mega tutorial. would you mind making one for handling JSON and inserting it into the database in oop way, please?

[@Greenitcom]

thank you i understand is oop php

[@vytautaskuklys3829]

Awesome video! How to handle exceptions though?

[@ten8217]

you are amazing.

[@vivek5147]

Thanz broo

[@poetha5100]

before i don't understand now i can understand easly bro Thank a lot Super over talk bor

[@pawecyrklaf4729]

Hi :) Why did you use extends instead of injecting the Dbh instance into the constructor of the Test class?

[@pseudounknow5559]

Znalazlesz odpowiedz czy nie ? Bo ja nie rozumiem

[@pawecyrklaf4729]

@@pseudounknow5559 Nie, ale dla mnie to ewidentny błąd, bo jego klasa Test w żaden sposób nie rozszerza możliwości klasy Dbh, a od tego jest extends, jedynie używa z niej połączenia. W tym wypadku powinien wstrzyknąć Dbh do klasy Test. Od 8 lat pracuję jako php dev i nigdy nie spotkałem się z tym, aby klasę od DB rozszerzać po to, żeby używać z niej połączenia.

[@arshiatolou1958]

What's that? i don't get it?

[@nathanielohiare5717]

Hey thanks for your tutorials, because of you am able to understand traits better. Please do you have a playlist on KZbin for your PHP tutorials, I checked your channel but couldn't find any PHP related playlist

[@Dani_Krossing]

I have a playlist on my KZbin page :) It's in the section where I have all my courses

[@godsonchiemena5870]

Please a make a video on '5 star rating and review system, comment and reply' . Thanks

[@user-kl7fe3rz5c]

thank you for good video everytime, and I would like to ask why you can extends Dbh class without requiring dbh.class.php??

[@Amanitian]

there is includes\class-autoload.inc.php file solves it =)

[@Zoen12345]

insaine!!

[@alexandernoelceleste1552]

Hi! can you give example on how to populate a table? using the methods in this video. thanks

[@nielsvansteen9087]

being a high school student in computer science (last year), i learned a lot from your videos. In school we have only seen procedural programming (OOP will come in college) and i decided i wanted to learn OOP because I am currently doing an internship for school with a big project and procedural PHP isn't working all that well on a big scale. The point of this comment being that although i learned a lot from your videos, i would like more in depth tutorials, i mean these are good but just the basics. For example namespaces were only covered briefly. But what i really mean with in depth is for example ajax with OOP, ... Don't get me wrong I LOVE your tutorials and i don't want to criticize but i am in need of of the most in depth tutorials, if you guys know any feel free to let me know

[@princeedward9160]

hi Dani ive got a question of the prepared statements why didnt we bind parameters like we do with precedural php and then execute $stmt later? of do we bind if they are coming from the form?

[@chyjohn6502]

I assume you might have found an answer to this and I'm not very good with PHP theories, but then I'll still try to answer you and then hope my answer makes any sense. Inserting the values directly as an array inside execute() is very valid according to PHP docs and it makes things easier when using the class across multiple projects. Whenever you instantiate an Object and pass 'where clause' and 'the value array' as the parameters, no matter the project, no matter the quantity of the 'where parameters / where values', your code will still run correctly without having to go back to the class file to start editing / worrying about the number of 'bindParams' you are supposed to have.

[@carllawrencecacho9455]

I wish that I saw your tutorials sooner :(

[@AnnaNelsonAnimeFan]

Nice :)

[@ademottoman962]

hi can you live stream some times because that would be awsome :)

[@bouabadahamid]

kzbin.info/www/bejne/b6bde6FunJmgoq8

[@walidnaceri9665]

@@bouabadahamid nta w mahrez ta3k hhhhh nas tat3alem w nta ab9a m3a mahrez

[@IlluSionCH]

Hi, First Thanks alot for your Tutos. They are very helpful. General Question, as you talk about prepared statements: In this examples you didnt use the *bind_param? This is for purpose, as you assume not getting external Data? or does the execute([]) take this procedure? Thank you or someone else who will explain me :)

[@IlluSionCH]

Ok got it, its PDO Handling

[@emirano]

Hi, great tutorial and I really loved it but am having an issue with the fact that you stated that you get the property of class by using the 'this' keyword but with the 'stmt' property you actually called it like a variable that is '$stmt'. So am kinda confused. Thanks alot for the tutorial

[@raymondmcveety9375]

I'm curious about the query() method you call on $this->connect()->query(), is this a built in method or did you write it in the class you're extending. I didn't see it in the last video. Also curious about fetch(). Are they built in for pdo?

[@pratiknavlani1188]

coming to the query part, you have to pass in the query you have written. And for fetch()....it's like you have to define the attribute first(which is done in the parent class in this video) and then use fetch method in order to fetch the data. Hope I helped you! Happy to help. Pratik Navlani

[@samduss4193]

@@pratiknavlani1188 $this ->connect()->query() ??? does it mean that the object return from $this-connect() as a method called query ?!

[@arshiatolou1958]

Those are built in oop generally, he didn't create any methods as 'query' , or 'fetch' in the class.

[@pbznt]

Dani, thank you for those videos. I'll soon subscribe to your patreon to discover the hidden content 😎 Meanwhile, could you tell me how is it that, for example, at 1:25, you extends a class that is in another file, and the file is not included in the document, nor the autoloader? Should not we include the autoloader in every file that requires references to other documents?Thanks!

[@pbznt]

I simply don't understand how the spl_autoload_register function from autoloader is called.

[@RiversDelay]

Hello Daniel, can you explain the differences between real escape string and prepared statements?

[@Dani_Krossing]

www.eversql.com/5-common-mistakes-php-developers-make-when-writing-sql/ :)

[@stephenkruger37]

Would you be able to do a class AddUser{} where you have data input from your HTML input fields and then posting it to your table and then display info in table on front end?

[@tovis_bratsburg]

Is it more or less the same with using Stored Procedures and Views?

[@BK-it8ht]

I'm stuck... Fatal error: Uncaught Error: Call to a member function fetch() on bool in /var/www/html/classes/test.class.php:8 Stack trace: #0 /var/www/html/index.php(16): Test->getUsers() #1 {main} thrown in /var/www/html/classes/test.class.php on line 8

[@arturskovrigo3915]

This most probably means that the query is returning false, but I have not found the reason why is this happening

[@nguyentruongminh7387]

Did you get the bug? I got the exact same and I'm stuck. Can't find a way through

[@mikoko2192]

Ensure you check everything concerning the database check your SQL statement if it is working properly and also your database connection code check for spelling errors...I found out mine was a spelling error at setting up the dsn I spelt (dbName=' . $this->dbName;) instead of (dbname=' . $this->dbName;)

[@Rafael-es5dp]

How are we gonna use LIKE in that kind of format of prepared statement? around 12:00 of the video stmt

[@arunkumar1414]

Sir, Gud Evening.!. I am trying to insert answer to my database answer which consists of q_no (AUTO _INCREMENT), qno(VARCHAR) and answer(VARCHAR) . Data is coming from radio button and i want answer of question 1 should insert into qno(varchar) 1 and so on.. but i am unable to perform that ..can u help me out if u have free time ?

[@ShubhamMishra-uw9yi]

Hello sir. Can i get material used in this lesson

[@poetha5100]

I was subscrib && watch your a lot of videos but don't understand any php || any php mysql i already understand over voice an English language 😃😄😁😆😅😂🤣

[@victor7883]

Shouldn’t we close the connection? How it works then? Thank you

[@randyhopkins9895]

Howdy Dani, trying to move more to OOP, it seem the class-autoload does not function in my setup. Is there something that needs to be changed in maybe the php.ini file? It is kind of weird because your PDO OOP login systems works fine. Just asking for a little help here and Thank you!

[@Jacksons_are_jackson]

the next episodes explains it

[@AhmadZaki95]

when i trying to run test.class.php i getting an error class Dbh not found why and i make autoloader page

[@monsurrahmanriaz5157]

I got this error, " Fatal error: Uncaught Error: Call to a member function fetch() on bool in C:\xampp\htdocs\OOP_PHP\db_connection\Danial\classes\test.php:11 Stack trace: #0 C:\xampp\htdocs\OOP_PHP\db_connection\Danial\index.php(8): Test->getUsers() #1 {main} thrown in C:\xampp\htdocs\OOP_PHP\db_connection\Danial\classes\test.php on line 11 ." can you tell me why?

[@subratmca]

How to return a success or failure message with prepare statement? also how to check for existing data and return a message if already data present in DB?

[@scottm8701]

what about closing the connection, commit, rollback?

[@sohaibrahman6240]

Please Please! Create a tutorial on Laravel Framework in PHP.

[@dagmartin]

I have one question to the setUsersStmt, if I want to have the firstname optional how can I add NULL to the DB

[@aleksandartrisic8443]

Access your database, go to your table -> structure -> action -> edit firstname and check null. SO firstname can be null.

[@noorahmed5115]

Please tell me how you bring your body in front of the video? I also use OBS studio, but I don't know how to to do it. I can place my video in a corner but that is complete square. How you trim all other background except your body?

[@Dani_Krossing]

Green screen :)

[@raylee141414]

Hi Daniel, i am not familiar with PDO. i remember u use mysql to handle database in php. Is there any reasons that u don't use mysql here?

[@Dani_Krossing]

Hi there, I think you mean MySQLi and not MySQL :) most people have the assumption that PDO connections are better than MySQLi, so therefore I decided to go with PDO :) you can just go ahead and use MySQLi if you wanna go with that instead.

[@raylee141414]

@@Dani_Krossing thanks dude. =D

[@vsmclassifiedads4046]

i got this error Fatal error: Uncaught Error: Call to a member function fetch() on boolean in C:\xampp\htdocs\education\mvc_model\classes\test.class.php:7 Stack trace: #0 C:\xampp\htdocs\education\mvc_model\index.php(95): Test->getUsers() #1 {main} thrown in C:\xampp\htdocs\education\mvc_model\classes\test.class.php on line 7

[@albatros280]

How many episodes are you planning?

[@Benjitob]

Hey Daniel, i am a little bit confused about prepared statments. First i learned to make prepared statements in msyqli, and in that way i should bind the parameters, but you dosent do it in your toturial, but is it still safe to do it in your way?

[@Dani_Krossing]

This tutorial just shows how to do basic queries, so no this isn't secure since it isn't done using prepared statements. You should ALWAYS use prepared statements, and I just never got far enough with making this course to the point where I show how to do it. 🙂 But if you wanna use prepared statements, then it is done like this inside the method instead: // HERE WE START THE METHOD protected function getUser($userId) { // HERE WE CREATE THE QUERY $stmt = $this->connect()->prepare('SELECT * FROM users WHERE users_id = ?;'); // HERE WE ASSIGN THE PLACEHOLDERS AND EXECUTE THEM if (!$stmt->execute(array($userId))) { $stmt = null; header("location: index.php?error=stmtfailed"); exit(); } // HERE WE GRAB THE RETURNED DATA $data = $stmt->fetchAll(PDO::FETCH_ASSOC); // HERE WE CHECK IF DATA WAS ACTUALLY RETURNED if (count($data) == 0) { $stmt = null; header("location: index.php?posts=none"); exit(); } // IF WE HAD DATA, THEN WE RETURN IT return $data; }

[@Benjitob]

Hey Dani Many thanks for you answer So in pdo, You assign the pladeholders in the execute function? So if i do like you wrote, my query is safe?

[@Dani_Krossing]

I mean there are many security measurements in PHP, such as assigning user roles to prevent any accidental "bad queries", hashing, secure connections, sanitizing data, etc... But yes, using prepared statements is how you should submit data to a database, or get data from a database.

[@Benjitob]

@@Dani_KrossingSorry about all my questions, but i saw a touturial about prepared statement on youtube. and in that tutorial they made it like this $conn->prepare("INSERT INTO MyGuests (email) VALUES (:Mail)") $stmt->bindParam(":Mail", $input); $input = $userId $stmt->execute(); i think that the ":" is a type of placeholders. Do you think this way also works, and is secure? Many thanks for you big help, and thank you for heping a danish guy how wants to program

[@dlosurucu]

Wrong is while($row=$smtp->fetch() True is Do it $users=$stmt->fetchAll();

[@soumikbhattacharya8938]

Hey, thank you so much for this great tutorial. Watched all oop videos and everything was great but in this lecture i got an error, public function getUsersStmt($name, $ph) { $sql = "SELECT * FROM tableName WHERE name = ? AND mobile = ?"; $stmt = $this->connect()->prepare($sql); $stmt->execute([$name, $ph]); $res = $stmt->fetchAll(); foreach ($res as $key) { echo $key['name']; echo $key['mobile']; } } but this piece of code return nothing. It's return an empty page with no errors. Please help

[@coooperrr]

change it to foreach ($res = $key)

[@paulnoris7774]

how can I get lastInsertedId using PDO ?

[@lserranoit]

Please use PSR12

[@jonathanorellana7270]

HELP I keep getting the error: connect()->query($sql); while ($row = $stmt->fetch()){ echo $row['firstname']; } } } ?> Fatal error: Uncaught Error: Class 'Email' not found in C:\Users\

[@qwertzuiop1978]

Well it's hard to tell what's wrong when you only pasted one small part of your code. But judging by the error message, it seems you have created a new object that's an instance of class "Email" while not having created the class itself. Like this: - $your_new_object = new Email();

[@mrwinnin1306]

how to do delete and update method please help me i am stuck

[@emanuelefalanga1152]

Sorry, when I try to get the rows from the database the query fails, but I have done everything as you explained. Here is my code: public function getUser(){ $sql = "SELECT * FROM users"; $stmt = $this->connect()->query($sql); if (!$stmt) echo "error"; else{ while($row = $stmt->fetch()){ echo $row['username']; } } } Can you please help me, I'm becoming crazy!

[@Dani_Krossing]

You are missing an opening bracket at the if statement.

[@emanuelefalanga1152]

@@Dani_Krossing nothing changes, on the web I found that is possible to not put the brakets with only one instruction under the if statement

[@VTULab]

Please 🥺 do you have course on udemy?

[@nipunsachinda]

Fatal error: Uncaught Error: Call to a member function execute() on bool in C:\Xampp new\htdocs\L16\classes\test.class.php:18 Stack trace: #0 C:\Xampp new\htdocs\L16\index.php(21): Test->getUserStmt('nipun', 'sachinda') #1 {main} thrown in C:\Xampp new\htdocs\L16\classes\test.class.php on line 18 why come this error

[@stephenkruger37]

How would you code a table to show your output data?

[@Dani_Krossing]

You just create a HTML table and then create a PHP foreach loop, that loops a with the data in it. 🙂

[@stephenkruger37]

@@Dani_Krossing Was able to create one thank you

[@youcefg9760]

9:49 Prepared statement how it works