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 Premium video tutorial!
Enjoyed this Post?
Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!
By Jeffrey Way
Premium Members
Source Files, Bonus Tutorials & More for all relevant Tuts+ sites in one subscription. Join Now
Related Posts
Premium Membership
Source Files, Bonus Tutorials & More for all relevant Tuts+ sites in one subscription
Tom Elders - More
Web Basix
Just getting started? Start with our basix tutorials on the fundamentals. View All Basix Tutorials
Other Plus Sites ...
aetuts+ audiotuts+ vectortuts+ tuts+ phototuts+ cgtuts+ nettuts+ psdtuts+ activetuts+Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
Awesome tutorial!
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.
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.
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!
Yeah – even if you don’t use prepared statements, MySQLI or PDO is absolutely the way to go.
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
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.
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!
Damm I must buy a + membership.
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!
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.”
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
;( I’m from Moldova, paypal don’t work in/with Moldova, sad.
very interesting
started learning PHP at school and there we started using mysqli 
so i guess I had a good start 
Hey Jeffrey, here is an idea: instead of buying a NETTUTS + membership, couldn’t we be able to pay per view ?
Thanks.
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
Yes, I like the idea too
That’s it Im getting Plus!
As soon as my ’studiefinanciering’ arrives
I will be signing up very soon.
Extended my PLUS account!
Aweasome Tutorial!
Thanks again Jeffrey!
Just watched the screencast and thought it was excellent! Keep up the good work!
Thanks
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.
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!
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?
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.
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.
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.
Is there anyway I can get the Plus tutorials onto my iPod Touch?
You can download the mp4 and convert it to m4v using Stomp – if you’re on a mac.
Thanks for replying Jeffrey but afraid I’m stuck on Windows. Will have a look around for a mp4->m4v convertor then I guess.
I can’t even begin to explain how excited I am to watch this!
That’s great, Tim. I hope you enjoy it!
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!
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!
Nice
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
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!
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.
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.
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!
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
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);
I have a better idea. This tutorial should be on hbo or showtime! Great tutorial Jeffrey. I always learn something off your videos.
period.
And how to bind_result from 2 or 3 tables? Is it possible or is there another method?
Thanks
may i download this zip file please please