What’s the use of a database if you can’t save data into it? As a developer, you want to save data that will continue to exist even if the user decides to use it at a future date or leaves the site.
The Zend_Db component opens up functionality that allows you to insert data into a table as easily as creating a simple array, but also allows you to create SQL statements to have complete control over what is executed. You’ll look at each one of these approaches.
In the first approach, you create the complete SQL statement; in the second approach, you supply all the necessary information to save, along with the table name you want to save the data into.
Using Plain Old SQL (POSQL)
The initial way of saving content into the database is the tried-and-true method of creating the full INSERT statement and then using the data-access layer supplied by PDO to execute the statement.To execute the INSERT statements, you use the query() method.
The query() method accepts two parameters:
Let’s create a test action to insert a couple of rows into the accounts table using a complete INSERT statement along with the query functionality. Open the Account Controller .php file and add a new action: testInsertAction().
Listing creates a Zend_Db_Adapter_Pdo_Mysql object by creating a database object using the model Db_Db.php you created in the beginning of this chapter. You create three INSERT SQL statements, each of which inserts a new account into the accounts table. Each INSERT statement contains the username, e-mail, password, status, and created date information.
Listing Using Full SQL INSERT Statements: testInsertAction()
Each statement is constructed the same way except for the third statement, which excludes the values in the statement. Instead you can use the placeholder flag ?. Zend_Db will replace these markers with the proper data-binding value supplied in the second parameter when you query the database.
Using the query() method to run the statements, pass in the variables containing $statement[1.3]. The final query call uses the optional second parameter. It contains an array with each element representing the value for each of the placeholder markers. When the query creates the statement, it places each of the elements into the proper position in the statement. The first element will replace the first marker, the second element in the array will replace the second marker in the statement, and so on. The final statement when executed will look like this:INSERT INTO Accounts (username, email, password, status, created_date) VALUES ('test_3', 'email@example.com', 'password', 'active', NOW())
Finally, close the connection by calling the closeConnection() method on the Zend_ Db_ Adapter_ Pdo_ Mysql object.
Open the browser and run the new action by visiting the URL http://localhost/account/test-insert. If all went well, you should see no errors and should see only the Completed Inserting string. Check the database table and make sure that you see the three new accounts.
Inserting Data Without SQL
You now have three records in the database, but you might be wondering how this is any different from not using Zend Framework. Its not; I just wanted to show you that Zend Framework allows developers who are savvy enough to create optimal SQL statements to continue using and executing them.
An easier way to insert data into the database tables is one that does not require a single line of SQL. No, this isnt a joke. Using the insert() method provided by the Zend_Db_Adapter_Abstract object allows you to bypass the traditional creation of SQL in the PHP code and just worry about the creation of a key-value array.
The insert() function accepts two parameters:
Lets update the code created in Listing to use the insert() method. Open the Account Controller. php file and create a new action, testInsert Method Action(), as shown in Listing.
Listing Using the insert() Method: testInsertMethodAction()
Referencing Listing, create an instance of the Zend_Db_Adapter_Pdo_Mysql class by instantiating the database model created earlier. Unlike Listing, you donft write any INSERT statements. Instead, you create three key-value arrays, each containing keys representing the columns in the accounts database table and the values representing the data you want to save in each column. You store the data into the $userData1, $userData2, and $userData3 variables.
After the data to save is initialized, use insert(), passing in the table name accounts as its first parameter and the array containing the data as its second parameter. Finally, close the connection; if everything went well, you should see the text Completed Inserting when visiting the URL.
Without the SQL statements, the code is much cleaner and lets you focus on the business logic and not worry whether the statement is optimal.
There is one thing to notice in the arrays created in Listing. Notice the way in which you saved the dates using the literal string 0000-00-00. If you tried to use the NOW() database expression, PHP would think it was calling a PHP function somewhere in the controller code. You need a way to tell PHP that the database expression is a database function, not a PHP function.
Database expressions are used extensively and even required when you begin creating complex SQL statements. Typical expressions include NOW(), COUNT(), LOWER(), and SUB(), just to name a few. You can review the RDBMSfs documentation for a complete list of expressions you can use.
When it comes to Zend_Db, these expressions pose a problem. If you use a Zend _Db _Adaptor and try to pass in the literal string NOW() as an example, the PHP script will fail before it reaches the insert() method with the error message Call to undefined function NOW(). At this point, NOW() in the code is seen as just another PHP function, not a database expression. You need a way to tell PHP to ignore the database expression call and allow the database to handle the function. The Zend_Db_Expr class lets you do just that.
Listing demonstrates the functionality of the Zend_Db_Expr class. Apart from that, the code inserts only one user. You use the insert() method and pass in two parameters: the name of the table, accounts, and a key-value pair array. The important difference is how you treat the NOW() database expression. You create a new instance of the Zend_Db_Expr class and using its constructor you pass in the expression as a string: "NOW()".
Listing Zend_Db_Expr Usage: testExpressionAction
Pull up the browser and enter the URL http://localhost/account/test-expression to run the action. When the action finishes, open the database and take a look at the records currently saved in the accounts table. You should see a new user with an accurate date in the created_date column for the account.
Zend_Db_Expr contains only two methods: its constructor and the __toString() method. The constructor accepts a single parameter as a string. The string is a database expression such as NOW(), SUM(), or COUNT(), and can be used during the calls to insert() and update(). (Youfll learn more in the gUpdating Recordsh section.)
With the ability to save data into the database, its only logical to become paranoid about what the user is saving into the tables. Can you trust users to save data into the database with your best interest at heart? You must be cautious that the user does not try to use any number of SQL injection techniques when storing data in the tables.
Brief Background: SQL Injection
SQL injection, a vulnerability that affects many RDBMSs, permits the user to inject a SQL statement into a predefined SQL statement that will execute in the database. Letfs take a quick look at a small test case.
Suppose that the user decided to sign up for the application and entered the following into the username field without any filtering or cleanup:user'DELETE * FROM Accounts
The single quote after user would halt the predefined INSERT statement and execute the DELETE statement injected into the code by the user. This will cause the accounts table to remove all the records currently stored in it. This vulnerability can be extremely dangerous to the application, so Zend Framework provides a method to guard you from this exploit. Escaping User Data
To counter the attack, Zend Framework has supplied you with two methods to escape single quotes. Using quote() and quoteInto(), you can add slashes into the user-supplied data. Any data the user decides to pass into the database containing a single quote will be escaped by placing a backslash in front of the single quote.
The quote() method accepts two parameters. The initial value can be a string, an array with each element containing the value you want to escape, a Zend_Db_Expr object, or a Zend_Db_Select object (covered in the gObject Oriented Select Statementsh section later in the chapter).
By default, the method returns a single quoted value. If you passed the "this is a test" parameter, the returned string would be '"this is a test"'. The value is returned in this fashion because of the way some RDBMSs require data to be passed into the INSERT or UPDATE statement. Each of the data values is required to be surrounded by single quotes. This is seen in the INSERT statements created in Listing.
There are times when the database table does not allow values to save as strings_for example, when saving INTEGER values. In such cases you use the second parameter to specify the SQL data type to use. By default, the second parameter is set to null, but you can overwrite it by using the following values:
Using the example shown in Listing you’ll display the full cycle of a string containing a ' by seeing it before it’s filtered and after the string is passed through quote(). Open the AccountController.php file and create a new action, testQuoteAction(), as shown in Listing.
Listing Quoting Strings: testQuoteAction()
You instantiate the $db object and call the quote() method on the $username data. After these calls, you echo each of the returned values to see the resulting string. Pull up the action in the browser so you can see how quote() treats the values using the URL.
Before the string is passed through quote(), the username is set as testing ' user. The username at this point is not saved, but you need to pass it through quote(). After you use quote(), the username is returned as 'testing ' user'. The username containing the backslash is now ready and safe to save into the database.
You can also use the filtering techniques to filter out any unwanted characters or data when saving the data to the database.
Escaping by Using quoteInto()
The second method that allows you to quote values is quoteInto(). The method is primarily used when using the ? placeholder.
The quoteInto() method accepts four parameters and returns a SQL safe string:
Last Inserted ID
Database tables use primary keys, which often contain an autoincremental value, typically a numerical value to uniquely identify a record in the table. Using the primary key you can associate other information to the record, so it’s important that you have an easy way of retrieving the primary key.
Zend Framework provides you with two methods that provide such effortless functionality: the lastInsertId() and lastSequenceId() methods that will retrieve the last-generated ID for a table (depending on the RDBMS). To use the methods, the primary key for the database table must be set to autoincrement and must be an int type.
The lastInsertId() method accepts two parameters:
The method lastInsertId is used only if the RDBMS follows the standard sequence-naming format. An example of the sequence-naming format is this: if you supply lastInsertId() with the parameters, artists, and id,you’ll have a sequence called artists_id_seq within the RDBMS.
If you have an RDBMS that supports sequences, yet does not conform to the format supported by default, you can use lastSequenceId().This method allows the use of a specific sequence name to be specified by passing in a single string parameter to the method.
Unfortunately, MySQL and MsSQL do not support sequences, so they ignore the parameters in both lastInsertId and lastSequenceId, and return the last inserted ID for the specific database query.
Let’s add another account into the account table and use lastInsertId() to fetch the account’s unique ID by creating a new action, the test Last Insert Action, in the Account Controller .php file.
Using the code shown in Listing and updating the user information, add the extra lines of code shown in bold to retrieve the ID of the account that was created.
In Listing, you insert the new user into the table and immediately call lastInsertId(). Calling the method returns 9, the autoincrement value for the new record. The call is available only immediately after an insert and cannot be used with other instances of a database connection.
Listing Retrieving the ID for the New Record
You’re now ready to apply the techniques covered so far into the application, enhancing the sign-up form and creating the add artist page.
LoudBite Sign-up Page
Open up the AccountController.php file and look at successAction(). The action takes the user-supplied data, cleans it up, and then does nothing. You need to fill in that void now. Right after the user’s data is cleaned, you need to add in the functionality to save the data. Using the techniques you learned, let’s create the insert functionality.
The updated successAction() is shown in Listing. The action is triggered after the user submits the form to add a new account and is the action that enters a new user into the system.
Listing. AccountController.php: SuccessAction
Reviewing the action, begin by ensuring that the form has no errors, and the form values meet the required formats. If there are no errors, initialize the $username, $email, and $password variables using the Zend_Form object's getValue(). You then create an instance of the Zend_Db_Adapter_Pdo_Mysql object using the database model. After you have the database object, initialize the data you will save into the database using the $userData variable. You will then pass in accounts and the $userData variable into the insert() method. I also recommend that you wrap the logic in try-catch just in case there are any issues with the insert. If there are no errors, return the unique user ID using lastInsertId() and eventually send out the verification e-mail.
Open the sign-up form in the browser http://localhost/account/new, fill out the form, and click Create My Account. If there are no database errors, you are now one of the first real users in the application.
LoudBite Add Artist
Now open the ArtistController.php file. You'll update saveArtistAction() to actually save the user's artist. The new saveArtistAction() method should take the userfs artist information, ensure that the entered data is valid, and save the data into the database for the specific user. For this example, because the login page has yet to be created, you need to create a static account ID and set it to the ID for the account you finished adding using the updated sign-up form. It should be 10. Using lastInsertId(), you'll link the artist to the account specified by the static value.
You’ going to focus on the saveArtistAction() method. Update the method as shown in Listing.
Listing ArtistController.php: saveartistAction()
Listing contains updates for the saveArtistAction() method, which is executed after the user fills out the artist information and submits the form. Reviewing the code, the action verifies whether the form values are valid; initializes and sets the variables $artistName, $genre, $rating, and $isFav; and initializes a Zend_Db Adapter_Pdo_Mysql object using the database model. You then create the array that contains the data to save into the artists table and insert the data into the table using the insert() method. If there are no errors, continue down the code by calling lastInsertId() and associate the artist to the user specified by the $userId using the returned ID. Finally, you create a new array, $accountArtistData, which holds the usered properties for the artist, the rating, the user'fs ID, the ID of the artist, and the flag if the user considers the artist to be a favorite.
Pull up the browser and load the http://localhost/artist/new page. Fill out the form, click Add Artist, and watch the thank you page come up.
You're done with inserting data. Now you'll focus your attention on fetching and displaying the data the users saved in the database.
Zend Related Interview Questions
|CSS3 Interview Questions||HTML Interview Questions|
|PHP Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Drupal Interview Questions|
|Magento Interview Questions||MVC Framework Interview Questions|
|CSS Interview Questions||WordPress Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.