Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

CRUD Operations with PHP

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Adding Projects

5:57 with Alena Holligan

When accepting user data, it's important to use prepared statements when interacting with the database, to prevent SQL injection. We'll use a prepared statement to INSERT data into our database.

Course

Modifying Data with SQL: Adding a Row to a Table with Auto Incrementing

Additional Resources

Getting the last insert id: return $results->lastInsertId();

Prepared statements can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster. The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

NOTE: Another Take on Auto Incrementing: UUID instead of auto incrementing

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    Most tables in a relational database have a primary key 0:00
    that uniquely identifies each record in that table. 0:03
    This allows us to join tables together using the key of a specific record. 0:07
    By using auto incrementing fields, we allow the database to generate the next 0:12
    key for us much the same way as adding a new element to an array 0:17
    automatically increments the largest previously used integer key. 0:22
    When adding a record to the database, 0:27
    we allow the database to automatically add the key. 0:28
    When updating a record in the database, we specify the key to update. 0:32
    Now that we are accepting user data, 0:37
    it's important to start using prepared statements. 0:40
    A prepared statement can be thought of as a kind of template for 0:43
    a SQL statement that can be customized using variable parameters. 0:47
    Prepared statements offer two major benefits. 0:51
    First, the query only needs to be parsed or prepared once. 0:55
    But can be executed multiple times with the same or different parameters. 0:59
    This means that a prepared statement has fewer resources and thus runs faster. 1:04
    Second, the one we're really concerned about here. 1:09
    A prepared statement properly escapes the variables so 1:13
    that no SQL injection will occur. 1:17
    For more information on prepared statements and SQL injections, 1:20
    please check the teacher's notes associated with this video. 1:23
    Now let's get started. 1:27
    Back in the functions.php file we'll create a new function. 1:28
    We'll name this add project. 1:36
    This function will accept two arguments, title and category. 1:43
    Next, we include the connection.php file. 1:47
    Let's start writing our SQL statement. 1:58
    INSERT INTO projects, 2:01
    title and category. 2:06
    We're then going to pass the values with placeholders instead of 2:14
    the actual value names. 2:18
    Then we'll be able to bind the values to the statement. 2:20
    But first, we need to prepare the SQL statement. 2:23
    When running a method on a PDO object, we want to place that method call 2:27
    within a try catch block to catch any errors we might have in our SQL code. 2:31
    Let's start with try and then within curly braces, we'll add our prepare statement. 2:36
    results = db prepare. 2:43
    And then we'll pass the SQL. 2:51
    Now we can bind the value of the first question mark to the variable title. 2:53
    Results bindValue. 2:57
    The first place holder will be title. 3:03
    And then we can explicitly defined the data type for that parameter. 3:08
    In this case PDO PARAM string. 3:11
    We can duplicate this line and 3:17
    bind the second placeholder to the variable category. 3:19
    Category is also a string. 3:27
    After binding the values, we can execute the query. 3:30
    Now we're ready for our catch block. 3:40
    Once again we add catch Exception e. 3:43
    And then we're going to echo Error, 3:52
    getMessage. 3:59
    Then return false. 4:07
    If we make it past the try catch block, we want to return true. 4:08
    Now that our function is set up, we can go back to project.php and start using it. 4:19
    Remove these echo statements. 4:25
    And call the add_project function. 4:28
    We'll pass the title and the category. 4:34
    This will return true, if the statement is successful, and 4:38
    false if it's unsuccessful. 4:42
    So, let's add a conditional. 4:44
    If we can add the project, then we want to redirect to the project list page. 4:47
    Use the exit command to make sure that the execution of the current script is 5:01
    stopped. 5:05
    Else, if adding the project was not successful, 5:08
    we want to set the error message equal to could not add project. 5:14
    We're ready to test this out in the browser. 5:26
    Fill out the project form again, We have an error in our SQL statement. 5:29
    So let's go check it out. 5:38
    Instead of VALUE, this should be VALUES. 5:41
    Let's refresh and submit the form again. 5:47
    This time our new project is added and we're redirected to the project list. 5:52

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up