PHP and SQL Server - Selecting Data
When performing an operation on a SQL Server database, such as selecting, inserting, updating and deleting data, the first thing that needs to be done is to connect to the database, as previously described.
In terms of selecting data, the SQL 'select' statement needs to be used. This takes the following format.
SELECT column_name FROM table_name
Where multiple columns need to be selected they can be separated by commas.
SELECT column_name1, column_name2, column_name3 FROM table_name
If all the columns in a table are required, then the comma separated list can be replaced by a single '*'.
SELECT * FROM table_name
The following table of data, called 'person', will be used in the example below for selecting data.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
Note that by default, dates in a SQL Server database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day).
The example below will select all records, in 'lastname', 'firstname' and 'dob' order, from the table 'person', where the 'dob' is between 1 January 1980 and 31 December 1989, and if any records are returned displays them in a table.
<?php // Connect to the database. require_once('database-connect.php'); // Query parameters. $dobLower = "1980-01-01"; $dobUpper = "1989-12-31"; try { // Prepare the query. $results = $connect->prepare("SELECT id, firstname, lastname, title, dob FROM person WHERE dob BETWEEN ? AND ? ORDER BY lastname, firstname, dob"); // Bind the parameters. $results->bindParam(1, $dobLower); $results->bindParam(2, $dobUpper); // Execute the query. $results->execute(); } catch(Exception $e) { // If query fails, display an error and exit. echo "Error retrieving person information."; exit; } // Assign the results to a variable. $people = $results->fetchAll(PDO::FETCH_ASSOC); // Check to see if any records have been returned. if (!$people) { // Display a message if no records exist for the criteria. echo "<p>There are no people that match the search criteria.</p>"; } else { // Display the results in a table. echo "<table>"; echo "<tr> <td>id</td> <td>firstname</td> <td>lastname</td> <td>title</td> <td>dob</td> </tr>"; foreach ($people as $person) { echo "<tr> <td>" . $person["id"] . "</td> <td>" . $person["firstname"] . "</td> <td>" . $person["lastname"] . "</td> <td>" . $person["title"] . "</td> <td>" . $person["dob"] . "</td> </tr>"; } echo "</table>"; } ?>
The above example uses the 'foreach' loop, which is a variation on the 'for' loop, to go through the records that have been returned and display them as rows in a table. The output can be seen below. A '.' is used to connect two parts of a string together when outputting the information onto the screen using the 'echo' statement.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
4 | Alan | White | Mr | 1989-03-20 |