Thu Jun 21

Introduction to SQL

9 – 12h Teaching

Teacher:

Franck

Goals

I know how to query data with SELECT statements and modify with INSERT, UPDATE, DELETE
I have joined tables and used set operations (UNION, MINUS, SELECT)
I learned about transactions and ACID properties

Useful online resources:

SQL tutorial and example

https://www.w3schools.com/sql

PDO Tutorial for MySQL Developers

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

Slides (course and exercises)

PowerCoders - Database - Day 3

open speakers-note (S) to copy/paste the code

12 – 13.30h Lunch

13.30 – 17h

Sylvain

Introduction to SQL Exercise

create a PHP page 'routes.php' to list routes between 2 airports
1. an HTML form to enter source and destination airport
2. connection to the flightdb2 database
3. A SELECT query on routes with a filter on src_ap and dst_ap
4. an HTML table to dynamically display the result
5. try to improve it as much as you can: for example display the destination airport name by joining ROUTES with AIRPORTS, maybe add a second query to get the number of routes with a query like:

SELECT COUNT(*) from ROUTES WHERE ... GROUP BY ...

Solution

Here is the full code of C:\MAMP\htdocs\routes.php and look at the result at http://localhost/routes.php.

<html>

<head>

 <title>Testing MySQL from PHP</title>

</head>

<body>

<!--

 Some PHP code to test if src_arp and dst_arp are passed in the URL with the GET method

 If not, then set it to % which is the SQL wildcard

-->

<?php

  if ( ! isset ( $_GET["form_src_arp"] ) ) { $_GET["form_src_arp"]="%" ; }

  if ( ! isset ( $_GET["form_dst_arp"] ) ) { $_GET["form_dst_arp"]="%" ; }

?>

<!--

 An HTML form to enter source airport and destination airport with search pattern

 default to the value passed in the URL (or % to list all)

-->

<form action="routes.php" method="get">

  Source Airport code:      <input type="text" size="20" name="form_src_arp" value="<?php echo $_GET["form_src_arp"] ?>"> <br>

  Destination Airport code: <input type="text" size="20" name="form_dst_arp" value="<?php echo $_GET["form_dst_arp"] ?>"> <br/>

  <input type="submit" value="Search">

 </form>

<!--

 Some PHP code connect to the database, run the query, and fetch the result into the $routes array

-->

<?php

  $connection=new PDO('mysql:host=localhost;dbname=flightdb2','root','root');

  $query=$connection->prepare('SELECT airline,src_ap,dst_ap,equipment FROM routes where src_ap like :pattern_src_arp and dst_ap like :pattern_dst_arp order by airline,src_ap,dst_ap');

  $query->execute( [ ':pattern_src_arp'=> $_GET["form_src_arp"] , ':pattern_dst_arp'=>$_GET["form_dst_arp"] ] );

  $routes=$query->fetchAll();

?>

<!--

 We start the HTML table and fetch at display the result row by row

-->

<p>

 Result:

 <table border=1>

 <tr><th>Airline</th><th>Source Airport</th><th>Destination Airport</th><th>Equipment</th></tr>

<?php

  foreach( $routes as $route ) {

   echo "<tr><td>" . $route['airline']. "</td><td>" . $route['src_ap']. "</td><td>" . $route['dst_ap']. "</td><td>" . $route['equipment']. "</td></tr>";

?>

</body>

</html>

If you want to go further (not required for next course)

Take the SQL Quizz: https://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL

You can also watch the 'Database for Developers' videos (by Oracle developer advocate Chris Saxon):

Database for Developers videos

Tables, Columns and Data Types, Data Modeling
Select and Where, Joins, Aggregates and Group By
Insert and Commit, Update and Transactions
Delete and Truncate, Null, Subqueries
Order By & Top-N, Analytic Functions, Pivot & Unpivot

Report abuse