Inserting Data - Zend

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:

  • The initial parameter is a string value containing the full SQL statement you want to execute. The SQL statement can contain a placeholder values represented as a question mark (?). The ? will be replaced with the escaped values or data-binding values contained in the second parameter of the query() function.
  • The second optional parameter is a mixed type. It can be either a string value or an array containing elements you want to replace the ? with in the SQL statement. Before replacing the placeholders with the values, Zend_Db escapes the values with quotes.

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', '', '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. Its 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 isnt 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:

  • The initial parameter is a string and is the name of the table you want to insert the data into.
  • The second parameter is an array. It must contain key-value pairs, in which the key is the column name in the table, and the value is the data you want stored into the column.

Lets 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 donft 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

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 RDBMSfs 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(). (Youfll learn more in the gUpdating Recordsh section.)

Escaping Values

With the ability to save data into the database, its 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. Letfs 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 Statementsh 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:

  • The initial parameter contains the string with any number of placeholder question marks. It can be a complete SQL statement or a partial statement such as username=?.
  • The second parameter is the data-binding value you want to replace the ? with. If there is more than a single placeholder value to replace, use an array with each element representing the data-binding value.
  • The optional third parameter is a SQL data type.
    • The optional fourth parameter is the number of placeholders that need to be replaced. Additional quote methods are shown in Table, including methods to escape unique table names that use SQL operators such as Limit and Order to identify tables and columns.

Escaping by Using quoteInto()

Escaping by Using quoteInto()

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 initial parameter is a string representing the name of the table.
  • The second parameter is a string and is the name of the column that contains the primary key.

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 userfs 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.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Zend Topics