Tue Jun 19

Introduction to Databases

9 – 12h Teaching

Teacher:

Run some simple SQL in multible databases, and generate CREATE statements from CSV data:

Install MySQL with PHP on Windows:

Commercial and open-source RDBMS, by popularity, to give an idea of what can be useful in the job market:

PowerCoders - Database - Day 2

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

12 – 13.30h Lunch

13.30 – 17h

Afternoon exercise #1 on MAMP (see slides)
1. connect with phpMyAdmin to the 'test' database
2. Create the CITIES and COUNTRIES tables
3. You can navigate in the browser to see what is possible from phpMyAdmin

If you encounter the Warning in ./libraries/sql.lib.php#615 count() error, it's a bug. Need to move a parenthesis:

line 615 of C:\MAMP\bin\phpMyAdmin\libraries\sql.lib.php should be:

     ( count($analyzed_sql_results['select_expr'] ) == 1 )

Afternoon exercise #2 (just if you have time and want to go further)
1. copy the PHP code provided in the slides into C:\MAMP\htdocs\index.php
2. browse http://localhost/index.php to see the result

Here is the full code so that you can copy/paste into C:\MAMP\htdocs\index.php

 are the HTML comments

/* */ are the PHP comments

Within an HTML page, the PHP code is between <?php and ?> tags

<html>

<head>

 <title>Testing MySQL</title>

</head>

<body>

<!--

 Start some PHP code

-->

<?php

/*

 The mysqli_init() function initializes MySQLi and returns an object to use with

 the mysqli_real_connect() function.

*/

$conn = mysqli_init();

if (!$conn) {  die("mysqli_init failed"); }

/*

 The mysqli_real_connect() function opens a new connection to the MySQL server.

     connection         Specifies the MySQL connection to use

     host               Specifies a host name or an IP address

     username           Specifies the MySQL username

     password           Specifies the MySQL password

     dbname             Specifies the default database to be used

     port               Specifies the port number to attempt to connect to the MySQL server

*/

$user = 'root'; $password = 'root';

$db = 'test';$host = 'localhost';$port = 3306;

if (!$success = mysqli_real_connect( $conn, $host, $user, $password, $db, $port)){

 die("<p>&#128544; Connect Error: " . mysqli_connect_error());

echo "<p>&#128526; Connected to database <b>$db</b> as user <b>$user</b>.";

/*

 Run a SQL query in the connection, return the result

*/

$sql = "SELECT Country_Code, Country_Name FROM countries";

$result = $conn->query($sql);

/*

 End the PHP code

*/

?>

<!--

 Some HTML to start a table

-->

<p>

Here are the countries:

 <table border=1>

 <tr><th>Country code</th><th>Country name</th></tr>

<!--

 Start some PHP code

-->

<?php

/*

 If there are some rows returned by the SQL statement loop on them and display a HTML row with the result values

*/

if ($result->num_rows > 0) {

  while($row = $result->fetch_assoc()) {

 echo "<tr><td>" . $row["Country_Code"] . "</td><td> " . $row["Country_Name"] . "</tr>";

/*

 End the PHP code

*/

?>

<!--

 End the HTML table

-->

 </table>

<!--

 Close the connection in the PHP code

-->

<?php

mysqli_close($conn);

?>

</body>

</html>

Report abuse