Functions for MySQL With a PHP Variable in the Query

PHP scripts can query MySQL databases as part of powerful applications.

Web applications can connect to and query MySQL databases using server side scripts written in PHP code. Developers writing these scripts can build detailed queries to execute on MySQL databases. These queries must match the database structure correctly to extract useful data. Developers can build PHP variables into their scripts, allowing them to build functions, creating dynamic queries according to unpredictable factors.

Build Query

PHP scripts build MySQL queries as text strings. The following sample code demonstrates:

$query = "SELECT * from client";

This query would select all records in a table named "client" within the database currently connected to. The query could also specify particular records, as follows:

$query = "SELECT * from client WHERE first_name='Mary Smith'";

This would select the value in each row in the table, for all records with the specified value in the first name column. This table could feature in a business database in which client details are stored. In both of these queries, the script supplies data items explicitly. However, the script may need to use a value passed as a variable when the page is fetched in the user's browser.

PHP Variable

PHP scripts model variables for text, numbers and other data items. The following sample code demonstrates creating a variable:

$first_name = "Mary Smith";

This code creates the variable and assigns a value to it using a string literal. However, the script may receive the value in the get, post or session variable. This may happen if the user is logged into a system in which different accounts are being modeled. For example, an administrator for the business, logged into the company's internal system, can request the details of a particular client using a Web form, which then passes these details to another PHP script in the post variable. The script can access this variable as follows:

$first_name = $_POST['fName'];

Variable Query

Once a PHP script has a data item modeled as a variable, it can include this in a MySQL query string. The following sample code demonstrates:

$query = "SELECT * from client WHERE first_name='".$first_name."'";

This query refers to the variable value using its name inside the query string. When PHP executes the query, the variable value will appear in the string. This allows the developer to specify a particular record using a data item that is not known when the script is written. The query will request whatever rows in the table contain the first name value passed from the other script.

Execution

PHP scripts use standard functions for executing MySQL queries and processing their results. The following sample code demonstrates executing the example query:

$result = mysql_query($query);

The script could then iterate through the results as follows:
while($row=mysql_fetch_array($row)) {
//process the row
}

PHP scripts often write the values within a row to the browser in HTML structures.

References

• W3Schools: SQL SELECT Statement
• W3Schools: SQL WHERE Clause
• W3Schools: PHP $_POST Function
• W3Schools: PHP String Variables
• PHP Manual: mysql_query
• PHP Manual: mysql_fetch_array

Resources

• W3Schools: PHP mysql_query() Function
• W3Schools: PHP mysql_fetch_array() Function
• W3Schools: PHP MySQL Introduction
• PHP Manual: MySQL Functions