Even now, I constantly see developers using the ancient “mysql_connect” methods when querying their databases. How come, when there are so many better solutions? In this week’s 40-minute video tutorial, we’ll learn how to create, read, update, and delete records using MySQL improved and prepared statements. Join Today!
After this 40-minute video tutorial, you'll be a "prepared statement" master capable of creating, reading, updating, and deleting records quickly and, most importantly, securely! The source code is neatly divided into subsets so that you may easily copy and paste the code into your own projects.
Why Use Prepared Statements
- The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When a query is prepared, the database will analyze, compile and optimize it's plan for executing the query.
- Prepared statements use fewer resources and thus run faster.
- The parameters to prepared statements don't need to be quoted; the driver handles it for you. If your application exclusively uses prepared statements, you can be sure that no SQL injection will occur. (However, if you're still building up other parts of the query based on untrusted input, you're still at risk).
...in other words... faster, smarter, and safer!
Learn how in this week's PLUS video tutorial!
Related Posts
Check out some more great tutorials and articles that you might like
Plus Members
Source Files, Bonus Tutorials and
More for $9 a month for all TUTS+
sites in one subscription.
User Comments
( ADD YOURS )Wez June 3rd
Awesome tutorial!
( )Dylan June 3rd
Haven’t watched the tutorial, but whenever I hear someone touting prepared statements, I feel obliged to mention that they’re only faster/more efficient when you’re executing a single query multiple times.
If, for example, a user is updating their account on your website, they’ll only be doing one update query – using a prepared statement for that would be slower/less efficient.
This might all be covered in the tutorial. If so, forget I mentioned anything.
( )Jeffrey Way June 3rd
Yep. We go over the speed issues in the tutorial. Personally, I’m okay taking a slight performance hit in those instances, in exchange for the sql injection protection.
( )Philo June 3rd
Always used mysql_connect, because it was “the way how to do it” a while back ago when I started with PHP. So I can’t wait to watch it!
It’s time to renew my NETTUTS+ account!
( )Will watch it tomorrow! Thanks for the tutorial Jeffrey!
Jeffrey Way June 3rd
Yeah – even if you don’t use prepared statements, MySQLI or PDO is absolutely the way to go.
( )Ulrik June 3rd
Great tutorial Jeff! Im almost finished watching it. Now im not complaining, im very satisfied with it, but personally i wouldnt mind it being 5 minutes longer if needed. Often you seem concerned about the length and start rushing a bit
But in the end you still show what you wanted to in the first place.. Anyways, all the best from here
( )Web010 June 3rd
I think this is the last drop. I’m getting a + membership
Do you accept moneybookers payments or just paypal?
I live in Serbia (Europe) and paypal doesn’t support my country.
( )Paul Davis June 3rd
I’ve been looking tor a good tutorial on adding, editing and deleting text to and from a database. If this is like any other of your screencasts, i’ll pick it up quickly.
I will finally buy a + membership tomorrow!
( )Dario Gutierrez June 3rd
Damm I must buy a + membership.
( )Norm June 3rd
It seems most designers visiting this site are php/mysql oriented, but maybe you guys at netTuts could look for someone to supply some asp.net/sql server tutorials for us windows folks. (I’m a paying subscriber and have enjoyed)
Thanks!
( )Hasanga June 3rd
http://net.tutsplus.com/about/
“Nettuts+ is a site aimed at web developers and designers offering tutorials and articles on technologies, skills and techniques to improve how you design and build websites. We cover HTML, CSS, Javascript, CMS’s, PHP and Ruby on Rails.”
( )Norm June 4th
Understood, but you do have a few asp.net tutorials on the site now, for example – Build a Shopping Cart in ASP.NET, and How to Search a Website Using ASP.NET 3.5.
Do you plan to add tutorials on asp.net in the future, or should I not look forward to that?
Jeffrey Way had mentioned some time ago that he would be adding more asp.net tutorials.
Thanks
Nicolae Vartolomei June 3rd
;( I’m from Moldova, paypal don’t work in/with Moldova, sad.
( )krike June 3rd
very interesting
started learning PHP at school and there we started using mysqli 
so i guess I had a good start 
( )Alexandre HOCHART June 4th
Hey Jeffrey, here is an idea: instead of buying a NETTUTS + membership, couldn’t we be able to pay per view ?
( )Thanks.
David Riveroll June 5th
Yes!, that’s a great idea. I think the membership is worth it, but what if we don’t have the time to take full advantage of it, but would like to take a + tutorial once in a while
( )Jakot June 17th
Yes, I like the idea too
Fynn June 4th
That’s it Im getting Plus!
As soon as my ’studiefinanciering’ arrives
( )Jermaine Hercules June 4th
I will be signing up very soon.
( )Philo June 4th
Extended my PLUS account!
Aweasome Tutorial!
Thanks again Jeffrey!
( )DP June 4th
Just watched the screencast and thought it was excellent! Keep up the good work!
Thanks
( )suciu vlad June 4th
Thanks, Jeffrey. Just watched your video. I Think i got used with mysql_connect that why i was using it. I’ll give a try to mysqli in the near future too.
( )Jack F June 4th
YES YES YES! I’ve been waiting for this one for ages! It’s worth $9 just for this one tutorial to be honest! Watching it shortly, thanks JW!
( )Scott June 4th
Hey Jeff – Thank a lot. I started trying to use the mysqli, but I’m having trouble getting a prepared query inside or another prepared query. I keep getting an error where it wanted me to close the statement first. Any ideas?
( )Web010 June 4th
you need to close the mysqli after you finish the querying i think. I’m not much familiar with mysqli so i’m not sure.
( )Scott June 4th
I tried saw a post on another site that said to make a new mysqli connection, but that doesn’t seem efficient at all, and I don’t think closing the mysqli will work either because then the loop won’t work at all. Thanks for the suggestion though.
hari June 4th
Great tutorial!!!
I am a +member and would love to see more tuts for the beginners.
I like your market place too. What to do with a cool landscape psd for background? I bought one and i can’t find the way to stretch it or maybe use it in a different way.
I see many questions for contact forms.
Some good modern backgrounds tuts in nettuts+, working contact forms…
I do enjoy being a member and will more once I finish photoshop class at the local college.
( )Jack F June 4th
Is there anyway I can get the Plus tutorials onto my iPod Touch?
( )Jeffrey Way June 4th
You can download the mp4 and convert it to m4v using Stomp – if you’re on a mac.
( )Jack F June 5th
Thanks for replying Jeffrey but afraid I’m stuck on Windows. Will have a look around for a mp4->m4v convertor then I guess.
Tim June 4th
I can’t even begin to explain how excited I am to watch this!
( )Jeffrey Way June 4th
That’s great, Tim. I hope you enjoy it!
( )Stephanie June 4th
This was an awesome tutorial. I hope to see more like this in the future!
( )I am really happy that I got the plus membership!
Ray June 4th
Hi Jeff,
I commited myself to learn some php, and you’ve been a good sensei, and i just became a plus-member after watching your diving into php series!
Thanks for another nice tutorial! In my opinion i’d like to have seen somewhat more on the updating values part and maybe combining all your code into one admin function (eg a table which encompasses all functions -> edit/delete/add).
The fading looks nice, but thats step two if you ask me
Thanks again and looking forward for more of your tutorials!
( )Shahriat Hossain June 6th
Nice
( )Ray June 7th
IS there any way to return the values stored in MySql in reversed order?
I have made a guestbook using the learnings from this tutorial, but i have not yet found out how to show te ‘posts’ in reversed order? (last entry shows op top followed by previous entries in decending order).
Thanks a lot for your help!
Ray
( )Ray June 7th
I just found out how to do this, i’ll share it with you guys:
$stmt = $mysql->prepare(’SELECT id,name,club,contents,datetimecreated FROM guestbook ORDER BY datetimecreated DESC’);
I added a field ‘TIMESTAMP’ in MySql and ordered it on this field, ensuring it shows the last post first. I recon ordering it on the ID field will have the same effect.
Regards!
( )Alec June 7th
Allright, so… where can I file a complaint against tutsplus?
I was just about to get started with learning about MySQLi or PDO, so obviously I just had to check this out. Now you made me buy a netplus membership!
But seriously, great free content and I’m sure the plus content will be worth it.
( )*Starting the screencast video.
Ray June 8th
Does anybody have a link to a good tutorial which handles MySql extraction which shows a set amount of results followed by links to the other results?
E.g.
10 results (posts)
Results (links): 11-20 21-30 31-40 etc.
( )Wade June 12th
Hey Jeff,
Thanks for the tutorial, Im a bit stuck now that I am all hooked on doing it the ‘new way’. 2 questions:
1 – how do we retrieve the number of rows like COUNT and;
2 – can we utilise mysqli / oop to do pagination?
Perhaps this could be your next tutorial as most dbs these days have records over 30+ minimum.
Anyway thanks for all the great tutorials so far they have really helped out.
Thanks!
( )Ray June 12th
Hi Jeff,
Please dive into pagination using your prepared statements. I have tried to implemet this using other tuts i found around the web. But i think these were simply not safe or use out of date techniques…
Thanks again for your help so far!
Ray
( )Brian July 12th
Jeff, or whoever can help.
When you call execute on bind_result, its throwing an error:
“Call to a member function bind_result() on a non-object.”
bind_param works fine though.
I even tried wrapping it in an “if” statement but couldn’t get that to work either.
Anyone?
Code:
( )$stmt = $mysql->prepare(’SELECT stuff, stuff2 FROM table’);
$stmt->execute();
$stmt->bind_result($variable, $variable);
Jules Manson August 6th
I have a better idea. This tutorial should be on hbo or showtime! Great tutorial Jeffrey. I always learn something off your videos.
period.
( )Jusko August 6th
And how to bind_result from 2 or 3 tables? Is it possible or is there another method?
Thanks
( )easy September 24th
may i download this zip file please please
( )