SQL Prepared Statements Tutorial

Learn how to use prepared sql statements in prodigyview.

1. Prepared Statements

2. Overview Objectives Learn the basics of creating, updating, searching, and deleting data with prepared statements. Requirements A database connection A basic understanding of how to write queries Estimated Time 8 minutes

3. Follow Along With Code Example Download a copy of the example code at www.prodigyview.com/source . Install the system in an environment you feel comfortable testing in. Proceed to examples /database/ PeparedStatements.php

4. Prepared Statements Defined If you are new to prepared statements, you are probably wondering what they are. The simple explanation they can described as is cached sql statement that can be reused multiple times. Prepared statements have advantages to them. No SQL Injection: The driver handles sanitizing values passed. Variables in a prepared statement will not cause SQL injection. Faster Queries: Because the sql statements are saved, they do not have to analyzed/compiled/optimized every time, making execution faster in certain situations. They are not the magic bullet.

5. Prepared Insert Let’s begin by first inserting data into the database. We need to have a table to insert into and data to insert. 1. Data to be inserted, column – value format 2. Name of the table to insert data into 3. Name of the column that is auto incremented

6. Prepared Insert Explain In our previous example, we are inserting a query and returning the auto generated id. The method used for that looks and operates like below: $ table_name : The table the query is going into $ returnField : The field that is going to be returned. Used for Postgresql . $ returnTable : The table that contains the field that the auto-generated will be returned from. Used for MS SQL . $data: Data to be inserted into the table using column => value format $formats: Optional. By default is an empty row, but is associated with the values passed. So if the value at $data[0] is a string, the $format[0] should be ‘s’ for string .

7. Prepared Select Now with a prepared select, there is not a complex method that handles placing the placeholders for us yet . For now, the place holders have to be placed manually. 1. Write the select statement 2. Set the Place Holder 3. Set the data to be updated 4. Execute the update query

8. Prepared Select Explained The placeholder’s method puts a placeholder that corresponds with a variable. The place holder also increments with every variable in the query. This means that if $data had 2 more variables, then those variables should match a variable in the query and they should be: getPreparedPlacholder (2) for the second variable and getPreparedPlacholder ( 3 ) for the third.

9. Prepared Update So we are going to need to update records . We need the table to be updated, the data that is going updated, and what values to look for when updating the date. 1. Set the data to be updated 2. Update where these values are found 3. Set the table to update 4. Data to be updated 5. Where to update

10. Prepared Update Explained The prepared update requires two list. Data List The data list contains the values you are updating. They should be in the array in a column => value format. The array key is the column name to update, and the value is the value to go in that column. Where List The where list defines the portion of the where clause in the sql statement. These values determine what row(s) will be updated.

11. Prepared Delete And the final prepared statement is the prepared delete . I think by now you get the idea of the $table and the $ wherelist .

12. Review Insert data into a table and return the generated id with PVDatabase :: preparedReturnLastInsert (); method Search for data with PVDatabase :: preparedSelect (); method Update data with PVDatabase :: preparedUpdate (); method Delete data with PVDatabase :: preparedDelete (); method

13. API Reference For a better understanding of the database, visit the api by clicking on the link below. PVDatabase www.prodigyview.com More Tutorials For more tutorials, please visit: http:// www.prodigyview.com /tutorials