friday’s class zihan will be out-of-town sidd will give the lecture – php and sql server:...
TRANSCRIPT
![Page 1: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/1.jpg)
Friday’s class
• Zihan will be out-of-town• Sidd will give the lecture
– PHP and SQL Server: Queries
![Page 2: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/2.jpg)
IST210 2
PHP and SQL Server: Connection
![Page 3: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/3.jpg)
IST210 3
Typical web application interaction
(php, jsp…)
database drivers
![Page 4: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/4.jpg)
IST210 4
When you query a database…
Step2. Select the database
Step1. Connect to the MS SQL serverServer Name: upsqlAuthentication: SQL Server Authentication
Step3. Input a query and execute it to get result
Step4. When you are done, close the application
![Page 5: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/5.jpg)
IST210 5
Let PHP do it for you…
Basic Steps for PHP Database Access:1. Connect to the MS SQL server and access the
database– connect
2. Perform SQL operations– Query– Get the results and update on the webpage– Most of the work are in this step
3. Disconnect from the server– close
![Page 6: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/6.jpg)
IST210 6
Example
• http://my.up.ist.psu.edu/zuz22/query.php
![Page 7: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/7.jpg)
IST210 7
Try it yourself
1. Download query.php from the course website and save it to your IST webspace
2. Open query.php using Notepad++ and modify the database information (important!)
3. Visit the PHP page and query the database:– http://my.up.ist.psu.edu/YourPSUID/query.php
![Page 8: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/8.jpg)
IST210 8
Modify the Database Information
Input your own information
![Page 9: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/9.jpg)
IST210 9
Connect to the Server
Attention: you may not have PROJECT table in your database! Check out what tables you have!
Go to MS SQL Management Studio Go to your database (your PSUID)Go to TablesIf you do not have any table in your database, download the scripts “SQL-Create-Tables.sql” and “SQL-Insert-Data.sql” from course website (week4-2) to create tables
Visit http://my.up.ist.psu.edu/YourPSUID/query.php
![Page 10: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/10.jpg)
IST210 10
HTML Form and PHPGet the table name from HTML form
In PHP, fetch the table name from $_POST
![Page 11: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/11.jpg)
IST210 11
Step 1. Open a DB Connection
• Open a connection to a database server– MS SQL Server:
• $connection = sqlsrv_connect( $hostName, $connectionInfo )
• More spec: http://php.net/manual/en/function.sqlsrv-connect.php
• The sqlsrv_connect function returns a resource handle $connection if it connects to the database successfully
![Page 12: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/12.jpg)
IST210 12
$hostname
‘UID’‘PWD’
Same parameters when you try to log in SQL server using MS Management Studio:
‘Database’
![Page 13: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/13.jpg)
IST210 13
Step 2-1. Use SQL to Query your DB
• Run a query with PHP – MS SQL Server:
• $query_result = sqlsrv_query($connection, $query);– It returns a result handle $query_result – We need the result handle ($query_result) to
fetch result data
![Page 14: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/14.jpg)
IST210 14
If input table is “project”, the generated query is “SELECT * FROM project”
Execute the SQL command “SELECT * FROM project”
Same as you execute SQL in Management studio:
![Page 15: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/15.jpg)
IST210 15
Step 2-2. Fetch query results
• Fetch the fields in the results – MS SQL Server:
• $fieldMetadata = sqlsrv_field_metadata($query_result);• $fieldname= $fieldMetadata['Name'];
– More spec:• http://php.net/manual/en/function.sqlsrv-field-metadata.php
• Get the data from each row – MS SQL Server:
• $line = sqlsrv_fetch_array($query_result, SQLSRV_FETCH_ASSOC)• The row ($line) is returned as an array.
– More spec:• http://php.net/manual/en/function.sqlsrv-fetch-array.php
![Page 16: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/16.jpg)
IST210 16Result in management studio Result shown on webpage (in table)
Put the result in the table
Fetching all the fields and print the field names
The header is the field
Fetching each row of the result
Fetching each column of that row$line is an array, $cell is an element in the arrayRefer to previous lectures for array, loop, and table
![Page 17: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/17.jpg)
IST210 17
Step 2-other. Other queries for your DB
• You can also insert/update/delete records– MS SQL Example
$query=“INSERT INTO USERS (UserID, UserName) VALUES(1, \‘Leon\’)” $query_result = sqlsrv_query($connection, $query)
• You can even create/delete/alter db objects– MS SQL Example
$query=“DROP TABLE USERS” $query_result = sqlsrv_query($connection, $query)
![Page 18: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/18.jpg)
IST210 18
Step 3. Close a DB connection
• You should close a DB connection to release resources– MS SQL Server:
• sqlsrv_close($connection);
![Page 19: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/19.jpg)
IST210 19
Error Handling
• All php DB functions return NULL (or false) if they fail– The database server is not running– Insufficient privileges to access the data source– Invalid username and/or password
• Several functions are helpful in graceful failure– die(string) - halts and displays the string– sqlsrv_errors() - returns text of error
![Page 20: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/20.jpg)
IST210 20
Error Handling examplesMethod One:
if (!($connection = sqlsrv_connect( $hostName, $connectionInfo )))
die("ERROR: connecting database server failed ");
Method two:
$connection = sqlsrv_connect( $hostName, $connectionInfo ) or die("ERROR: connecting database server failed");
![Page 21: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/21.jpg)
IST210 21
Other PHP MSSQL Classes
• http://php.net/manual/en/book.sqlsrv.php– Functions: allowing plugging-in variables
![Page 22: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/22.jpg)
Lab Exercise• Modify query.php, allowing to specify attributes
– If no input for attributes, show the table– If some input for attributes, show the selected columns– Example: http://my.up.ist.psu.edu/zuz22/query-attr.php
![Page 23: Friday’s class Zihan will be out-of-town Sidd will give the lecture – PHP and SQL Server: Queries](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d6e5503460f94a4fcfb/html5/thumbnails/23.jpg)
IST210 23
Hints
You need to modify these two parts
Now, you need to query given attributes not the whole table. What query you should generate?Also, when there is no input for attributes, you need to show all the results. So think about using if…else… statement
You need to have a new input for attribute names