MySQLi – Select Data

The SELECT command allows us to select information from our database and do the following:

– send the data back to the requesting web page.
– display the data directly from the PHP script (we will look at this option).

1
2
3
4
5
6
7
8
9
10
11
12
13
 
$sql = "SELECT * FROM Messages";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Title: " . $row["title"]. " - Message text: " . $row["messagetext"]. " " . $row["email"]. "<br>";
        // we can access each field through the $row variable.
    }
} else {
    echo "0 results";
}

The above code selects all data from the ‘Messages’ table. However, we can set criteria on the data we want to select.

When using the echo function, you can use any HTML to style your output.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
$sql = "SELECT title, messagtext FROM Messages WHERE email = 'smith@smith.com'";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Title: " . $row["title"]. " - Message Text: " . $row["messagetext"]. "<br>";
        // we can access each field through the $row variable.
        // the '.' allows us to concatenate the fields.
    }
} else {
    echo "0 results";
}

MySQLi – Delete data

As the title suggests, we will learn how to delete a single or multiple rows (records) from a table within our database.

I will no longer be including the connection code in the examples. You should realise that you must connect to the database in every new PHP script.

1
2
3
4
5
6
7
8
9
 
$sql = "DELETE FROM Messages 
WHERE email = 'smith@smith.com'";
 
if ($conn->query($sql) === TRUE) {
    echo "Message has been deleted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

The above script will delete the message stored in the table that has the email ‘smith@smith.com’ associated with it. Please note that if there is more than one record associated with the email address ‘smith@smith.com’ then they will all be deleted. Best using a unique identifier.

You can delete everything from the table by using the following code:

1
2
3
4
5
6
7
8
 
$sql = "DELETE * FROM Messages";
 
if ($conn->query($sql) === TRUE) {
    echo "All messages have been deleted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

The ‘*’ denotes everything and can also be used to SELECT everything from the table.

MySQLi – Create Table and Insert Data

Now that we have created a database, we will attempt to create a table then populate it with data.

Below is the php code that will create a table. There are four fields, one of them being ID. Every time a record is added to the table, ID will incremented by one automatically to provide each record with a unique id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
 
$sql = "CREATE TABLE Messages (
ID int NOT NULL AUTO_INCREMENT,
title VARCHAR(50),
messagetext VARCHAR(10),
email VARCHAR(100),
PRIMARY KEY (ID))";
 
 
if ($conn->query($sql) === TRUE) {
    echo "Table has been created";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
 
$conn->close();
?>

The php and mysqli code below allows us to insert data into our ‘Messages’ table. The table will have been created using phpMyAdmin. If no table exists an error message will be displayed.

Insert data into a database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$sql = "INSERT INTO Messages (title, messagetext, email)
VALUES ('Intro Message', 'A message', 'smith@smith.com')";
 
if ($conn->query($sql) === TRUE) {
    echo "A new message has been posted";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
 
$conn->close();
?>

Your task is to insert 5 new records into the ‘Messages’ table. Please refer to the following website to see how insert multiple records at the same time: Multiple Inserts

MySQLi – Connecting to a Database

In order to manipulate the contents of your database dynamically we will use PHP and SQL.

PHP – is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

SQL – is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

Connecting to a Database – Your teacher will provide you with localhost, username and password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
$conn->close();
?>

The above piece of code allows us to connect to the named database. You must always remember to close the connection to database at the end of every script.

We will now try and create a table then insert data into the database using the INSERT INTO statement.

HTML and CSS – Twitter Bootstrap (Tasks)

Create a page that matches the following criteria:

– Contains a navbar (bootstrap navbar)
– Two rows.
– Each row contains three columns.
– Each column can contain any component.

Use the starter template available in prepwork.

Task 2

You have been using the preset bootstrap style sheet. You are now going to create your own css rules that will change the appearance of your website.

You are going to change the following elements:

– Headings
– Links

There is a exemplar to help you get started.

HTML and CSS – Twitter Bootstrap

The Bootstrap framework allows us to create responsive websites or web apps. Furthermore, Bootstrap comes with many components and widgets, and we are provided with sample code at getbootstrap.com.

Below is a starter template:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<html>
  <head>
    <title>Bootstrap - DHS</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- This line tells the web page to resize according to the size of the device -->
    <!-- Bootstrap -->
   <link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css" rel="stylesheet" 
media="screen">
     <!-- loads the style sheet for bootstrap -->
  </head>
 
  <body>
    <h1>Hello, world!</h1>
 
   <script src="http:http://code.jquery.com/jquery-1.11.3.min.js"></script>
   <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>
    <!-- loads the javascript file that is used by many of the bootstrap components -->
  </body>
</html>

Bootstrap uses a grid layout to organise the appearance of the web page or web app. You can view diiferent examples of using the grid layout at getbootstrap

Have a look at an example below:

1
2
3
4
5
6
7
<div class="container">
  	<div class="row">
                 <div class="col-md-4">
                 <!-- place your components here or any html code -->
                 </div>
        </div>
<div>

The grid system works by providing twelve columns per row. For example, if you want three equal columns you would use the class ‘col-md-4’ three times. Furthermore, you can split the columns into unequal amounts such as ‘col-md-8’, col-md-2 and col-md-2.

Playing around with the different column sizes and positions is recommended.

Codecademy – jquery

We are going to cover the jquery module from codecademy. This will provide you with a basic understanding on how to use jquery and integrate it into your webapp.

Complete:

Quick guide to terminology

  • An element is HTML tag such as <p>, <h1> or <ul>.
  • Each element can have an id e.g. <p id=”firstParagraph”>. This allows jquery to target a specific element rather that just all <p> elements.
  • When an event is triggered the following jquery code could be executed:

         $(“firstParagraph”).html(“This is Higher ComputingScience”);
         $(“secondParagraph”).append(“This is the new content.”);

Introduction – Javascript

Javascript – jquery

Javascript allows the developer to add interactive and dynamic elements to a website or web app. Javascript is one of the most popular scripting languages used for the web. We are going to use jquery, which provides a simple way to integrate javascript into our web app. Also, jquery provides us with functions that allow us to alter the contents of any HTML code.