[go: up one dir, main page]
Scribd
Upload
55 views3 pages

2nd Answer

1) The document describes a PHP application that allows users to execute SQL queries against a MySQL database from a web interface. 2) The application consists of two PHP pages: one for selecting a database and entering a query, and another for displaying the query results or errors. 3) It connects to the MySQL database, gets the list of databases, executes the user's query, and displays any results in an HTML table or error messages.

Uploaded by

Rajasri Janakiraman
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
55 views3 pages

2nd Answer

1) The document describes a PHP application that allows users to execute SQL queries against a MySQL database from a web interface. 2) The application consists of two PHP pages: one for selecting a database and entering a query, and another for displaying the query results or errors. 3) It connects to the MySQL database, gets the list of databases, executes the user's query, and displays any results in an HTML table or error messages.

Uploaded by

Rajasri Janakiraman
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

Structured Query Language

The Structured Query Language (SQL) is the standard programming language for accessing and manipulating information from a relational database. SQL is an ANSI and ISO standard, and is supported by almost all the relational databases. In the next section, we will present a small PHP application, which will allow you to execute SQL statements against a MySQL database.

TEAM FLY PRESENTS

A PHP SQL Code Tester


The application consists of two short PHP pages, query.php and mysql_test.php. The first of these pages consists mostly of HTML and allows the user to select one of the databases from the local server and to input a SQL query to execute against that database: The second page presents the results of the query, or an error message if the query failed. The following screenshot shows the results of querying a test database with information about some Wrox books:

TEAM FLY PRESENTS


The code behind these pages contains PHP functions specific to the MySQL database, which we will look at a bit later on in this chapter. However, most of it should be reasonably easy to follow. The first page contains a <SELECT> element, which we populate with the names of the available databases. This is the only section of PHP code in the page. It also contains a <TEXTAREA> element where the user can input the SQL query, and a Submit button:
<HTML> <HEAD> <TITLE> PHP SQL Code Tester </TITLE> </HEAD> <BODY> <!-- query.php --> <?php $host="localhost"; $user="php"; $password="php"; ?> <FORM ACTION="mysql_test.php" METHOD=POST> Please select the database for the query:<BR><BR> <SELECT NAME=database SIZE=1 > <?php mysql_connect($host, $user, $password);

TEAM FLY PRESENTS


$db_table = mysql_list_dbs(); for ($i = 0; $i < mysql_num_rows($db_table); $i++) { echo("<OPTION>" . mysql_tablename($db_table, $i)); } ?> </SELECT><BR><HR> Please input the SQL query to be executed:<BR><BR> <TEXTAREA NAME="query" COLS=50 ROWS=10></TEXTAREA> <BR><BR> <INPUT TYPE=SUBMIT VALUE="Execute query!"> </FORM> </BODY> </HTML>

The PHP script in this page occurs within the HTML <SELECT> element. It connects to the local MySQL database server with the username php and password php. We then call the mysql_list_dbs() function, which returns a reference to a resultset containing the names of the available databases. We iterate through this resultset, and for each entry print to the browser the string "<OPTION>" followed by the database name. This creates an <OPTION> element within the <SELECT> element for each database. When the Submit button is pressed, the chosen database will be passed to the next page, mysql_test.php, and be available through a variable $database. The text of the SQL query entered by the user will also be available, through the $query variable. The next page displays an HTML table containing the results of the query; if no rows are returned by the query, either a success message or an error message will be displayed.
<HTML> <HEAD> <TITLE> PHP SQL Code Tester </TITLE> <BODY> <!-- mysql_test.php --> <?php $user="php"; $host="localhost"; $password="php"; mysql_connect($host,$user,$password); mysql_select_db($database);

$result = stripSlashes($query) ; $result = mysql_query($query); ?> Results of query <B><?php echo($query); ?></B><HR> <?php if ($result == 0): echo("<B>Error " . mysql_errno() . ": " . mysql_error() . "</B>"); elseif (mysql_num_rows($result) == 0): echo("<B>Query executed successfully!</B>"); else:

TEAM FLY PRESENTS


?> <TABLE BORDER=1> <THEAD> <TR> <?php for ($i = 0; $i < mysql_num_rows($result); $i++) { echo("<TH>" . mysql_field_name($result,$i) . "</TH>"); } ?> </TR> </THEAD> <TBODY> <?php for ($i = 0; $i < mysql_num_rows($result); $i++) { echo("<TR>"); $row_array = mysql_fetch_row($result); for ($j = 0; $j < mysql_num_fields($result); $j++) { echo("<TD>" . $row_array[$j] . "</TD>"); } echo("</TR>"); } ?> </TBODY> </TABLE> <?php endif ?> <HR><BR> <FORM ACTION=query.php METHOD=POST> <INPUT TYPE=SUBMIT VALUE="New query"> </FORM> </BODY> </HTML>

We connect to the database server in exactly the same way as on the previous page, again using the username "php". We then specify the active database as that referenced by the $database variable passed over from query.php. Our next step, before we execute the query, is to remove any escape characters from the text of the query. We aren't going to type escape any characters when we type in the query, so why do we need to do this? Consider the SQL query:
SELECT * FROM books WHERE title="Professional PHP"

When this query is typed into the textarea, the quote marks will automatically be escaped if magic_quotes_gpc configuration directive is set true in the configuration file (php3.ini in case of PHP 3.0, php.ini in case of PHP 4.0), so the variable $query will actually contain the string:
SELECT * FROM books WHERE title=\"Professional PHP\"

To avoid this problem, we use the PHP function StripSlashes(), which removes the offending

TEAM FLY PRESENTS


slashes. Thus un-escaped, we can execute the query against the active database with the mysql_query() function. The return value is stored in the variable $result. This may be one of two things: If a resultset is returned from the query, $result will contain a reference to that resultset. If the query fails, $result will be false (0). If the query fails or the number of rows returned is zero then we simply display an error or a success message. If the number of rows returned by the query is non-zero, then we build an HTML table to display the results. The header of the table consists simply of the field name for each field in the resultset: we use mysql_num_fields() to determine the number of fields in the resultset, and iterate through the resultset, writing each field name to the browser enclosed in <TH>...</TH> tags. The body of the table is built in similar fashion. The difference here is that we must have two for loops: one for each row in the resultset, and another for each field. In the outer loop, we simply write an opening <TR> tag to the browser, call the mysql_fetch_row() function to store the data for the row in an array, iterate through this array, and then print the closing </TR> tag. This ensures that each iteration of the inner loop occurs in a separate row in the HTML table. Each call to mysql_fetch_row() moves the row pointer to the next row in the resultset, so the outer row enumerates through each row in turn. This function stores the row as an array, with each

field in the row represented by an element in the array. So our inner loop iterates through each element in this array, and prints it to the browser within <TD>...</TD> tags to create each cell of the row in our HTML table. Finally, the page also contains a form with a submit button to return the user to the previous page, so a new query can be made.

You might also like