Try Tuts+ Premium, Get Cash Back!
CRUD With Prepared Statements

CRUD With Prepared Statements: New Premium Tutorial

on Jun 3rd 2009 with 50 comments

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

  1. 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.
  2. Prepared statements use fewer resources and thus run faster.
  3. 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

Rollover to read this author's bio or click through to see a full list of posts by this author.

I'm the editor of Nettuts+, and love making the process of building for the web as easy as possible. Also, I'm finishing up my latest book on Laravel Testing! If you want be friends, say hi on Twitter.  

Plus Premium
 
 
Note: Want to add some source code? Type <pre><code> before it and </code></pre> after it. Find out more
  • http://www.thetutorialblog.com Wez

    Awesome tutorial!

  • Dylan

    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.

    • http://www.jeff-way.com Jeffrey Way
      Author

      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.

  • http://www.philohermans.nl Philo

    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!

    • http://www.jeff-way.com Jeffrey Way
      Author

      Yeah – even if you don’t use prepared statements, MySQLI or PDO is absolutely the way to go.

  • Ulrik

    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

  • https://www.getafreelancer.com/users/755993.html Web010

    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

    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!

  • http://labs.dariux.com Dario Gutierrez

    Damm I must buy a + membership.

  • http://www.njedesign.com Norm

    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://www.xtensives.com Hasanga

      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

        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

  • http://nvartolomei.com/ Nicolae Vartolomei

    ;( I’m from Moldova, paypal don’t work in/with Moldova, sad.

  • http://cmstutorials.org krike

    very interesting :D started learning PHP at school and there we started using mysqli :) so i guess I had a good start :)

  • Alexandre HOCHART

    Hey Jeffrey, here is an idea: instead of buying a NETTUTS + membership, couldn’t we be able to pay per view ?
    Thanks.

    • David Riveroll

      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

        Yes, I like the idea too

    • Kevin

      LOVE this idea… I own a business that has me splitting my time between all sorts of design, web design, and printing… I don’t have enough time to take advantage of a monthly membership, but I definitely want to advance my web design knowledge… being able to purchase an article here and there makes much more sense than a subscription for me. Any news on this? is it absolutely NOT an option?

  • Fynn

    That’s it Im getting Plus!

    As soon as my ‘studiefinanciering’ arrives ;)

  • Jermaine Hercules

    I will be signing up very soon.

  • http://www.philohermans.nl Philo

    Extended my PLUS account! :D
    Aweasome Tutorial!

    Thanks again Jeffrey!

  • DP

    Just watched the screencast and thought it was excellent! Keep up the good work!

    Thanks

  • http://www.suciuvlad.com suciu vlad

    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

    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

    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

      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

        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

    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

    Is there anyway I can get the Plus tutorials onto my iPod Touch?

    • http://www.jeff-way.com Jeffrey Way
      Author

      You can download the mp4 and convert it to m4v using Stomp – if you’re on a mac.

      • Jack F

        Thanks for replying Jeffrey but afraid I’m stuck on Windows. Will have a look around for a mp4->m4v convertor then I guess.

  • http://www.77lines.com Tim

    I can’t even begin to explain how excited I am to watch this!

    • http://www.jeff-way.com Jeffrey Way
      Author

      That’s great, Tim. I hope you enjoy it!

  • Stephanie

    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!

  • http://www.fcdbzine.nl Ray

    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!

  • http://shahriat.com Shahriat Hossain

    Nice :)

  • http://www.fcdbzine.nl Ray

    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

    • http://www.fcdbzine.nl 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!

  • Alec

    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.

  • http://www.fcdbzine.nl Ray

    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

    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!

    • http://www.fcdbzine.nl Ray

      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

    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

    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

    And how to bind_result from 2 or 3 tables? Is it possible or is there another method?

    Thanks

  • easy

    may i download this zip file please please :)

  • realtiger

    Hello,

    I have a premium subscription, I have a hard time reading the video tutorial:

    It gets lagging at 2min30, I hear the voice but the image keeps looping.

    Moreover, the system of video is not very practical to me: can’t we download it?
    I can pause but I can’t go back if I want to look again at a specific block of tutorial.

    Is it coming from my configuration?

  • kyle

    Fatal error: Call to a member function bind_result() on a non-object in C:\xampp\htdocs\PhpProject2\read – Copy.php on line 4

    I followed the tutorial and this is the error i am getting with the read.php file.
    I am using xampp and that’s the error i have been getting and no I know has the knowledge to fix it.
    pls help

  • Yahia

    Hay , is there a faster way than Prepared Statements ?

  • Mittul chauhan

    PDO and mysqli prepared statements are best … many thanks from india.