How to Create a Search Feature with PHP and MySQL [con't]
Check to See if the Criteria Has Been Met
At this point, it's wise to understand how the initial search functionality will work. When a visitor enters a first or last name and then presses the submit button, the form will post back to itself and append a query string of "go" on the end. At this point, we'll check the address bar for a query string of go and if it exists, we'll perform additional programming logic to make applicable staff members to appear. Before we perform database operations or information is displayed back to the visitor, we need to verify three things: (1) Has the form been submitted, (2) Does the query string contain a value of go, and (3) Does the search criteria entered only contain a capital or lower case letter? If none of these is true, then no operations are needed. First, let's add a PHP code block right below the closing </form> tag:
First, we start a PHP code block:
Followed by a closing PHP code block:
We use the built-in function isset, which is a Boolean check and pass in the super global array $_POST value of submit. A Boolean value in programming is a true/false value. Therefore, if the function returns true, then the form has been submitted and we need to do additional programming logic. If the function returns false, we display an error message. If you haven't saved your file yet, now would be a good time to do so (search_submit.php).
Next, we need to check whether the query string has a value of go, which is achieved by using this code:
We simply nest another conditional logic statement inside our original, except this time, we use the super global array name of $_GET, with a parameter of "go" to check our address bar for this value. Save your file (search_go.php).
Finally, we need to ensure that visitors are only allowed to enter a capital or lower case letter as the first character in our search field. Why this is done is explained in the SQL injection section at the end of the article. We also need a way to collect the search criteria entered by the visitor. The best way to check visitor input, and really the only way, is through a regular expression, which is shown below:
Again, as before, we nest another conditional logic statement inside our original two. This time, we use a regular expression to check our input. We use the built-in preg_match function along with two parameters, which is the pattern to match, in our case, at least one capital or lowercase letter, followed by the name of our form field we want to check against, in our case, name. Lastly, in order to collect the search criteria entered by the visitor, we create a variable called name by using the dollar sign ($) in PHP and assign it the POST value of name from our form, which is used in conjunction with our SQL query, seen later. At this point, even though our form won't return a result set, we've ensured that: (1) The form has been submitted, (2) The query string has a value of go, and (3) The visitor has entered a capital or lowercase letter as the first character before any database or SQL operations are allowed to continue. Save your file (search_expression.php).
Connect, Select, Query and Return Result Set from Our Database Table
In order to return a result set from our database table, we need to first connect to our database server. We use the following code:
As you'll notice, we create a variable called db, and assign it to My SQL's built-in mysql_connect function which takes three parameters: your database server, which if you're developing locally, would simply be "localhost," followed by your user name and password. After this, we call a built-in function in PHP, die, which will stop further execution of the code if it cannot connect to the database, and lastly, append error information by calling My SQL's built-in function, mysql_error which will hopefully give us some insight as to why we're not able to connect. Save your file (search_connectdb.php).
Next, we select which database to use by using this code:
We create a variable called mydb and assign to My SQL's built-in function, mysql_select_db and pass in the name of our database we created earlier. Next, we query our database table using SQL, along with our name variable, which contains the search criteria entered by our visitor:
When querying our database table, we first create a variable called sql, and assign it to a literal string containing our SQL query. In our case, we use the keyword SELECT to grab the id, first and last name columns from our contacts table. Then we use the keyword WHERE, along with our first and last name columns to narrow our search field. Using the LIKE keyword, we pass in the percentage sign (%), which is a wildcard character that returns zero or more characters and our name variable from the search field. As a result, the LIKE keyword (in conjunction with our wildcard character) will find any name that matches in our database table to that entered by our visitor. In other words, the SQL query performed could be translated as follows: "Select first and last names from our contacts table where either the first or last name matches that entered by our visitor." If you haven't saved your file yet, now would be a good time to do so (search_query.php).
Next, we need to store the results of our SQL query in a variable and run it against the mysql_query function as illustrated below:
As you can see, we create a variable called result, and assign it to the mysql_query function, passing in our query variable. Now our query is stored in the result variable. In order to display our result set in PHP, we'll create a loop and then parse out the first and last name using an unordered list as shown below:
First, we create a while loop, and inside we create a variable called row and assign it to the mysql_fetch_array function, which takes one parameter, our result variable containing our SQL query. Inside the while loop, we assign each column from the row variable to a new variable with an identical name. Then, we display the values inside an unordered list. Two things worth noting are: (1) Inside the while loop, you don't have to create and assign a variable to the row array, you could parse out the values directly from the row array, (2) You'll notice in the anchor tag that we pass in the name of our file along with the id or primary key. The reason for this is mainly because in most search applications, you don't display everything initially. Since we're only displaying first and last name, by appending the ID on the end of our anchor tag, we can then use the ID for an additional query that will display further information regarding our staff member. Save your file and test your form at this point (search_display.php).
Find a programming school near you