CART 351 – INTRO TO SQL-LITE

images used in this lecture

Introduction::

  • SQL stands for Structured Query Language is a standard language for accessing and manipulating databases.
    According to ANSI (American National Standards Institute), SQL is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres,…
  • SQL is a declarative programming language designed for creating and querying relational database management systems. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. SQL is relatively simple language, but it’s also very powerful.
  • SQL can insert data into database tables. SQL can modify data in existing database tables. SQL can delete data from SQL database tables. Finally SQL can modify the database structure itself – create/modify/delete tables and other database objects.
  • SQL uses set of commands to manipulate the data in relational databases. For example SQL INSERT is used to insert data in database tables. SQL SELECT command is used to retrieve data from one or more database tables. SQL UPDATE is used to modify existing database records.

BREAK DOWN OF this class

First: download the images zip folder using the link above (same images folder as from the PHP lectures). We will continue with the gallery website – that we started while learning PHP. Let’s recall the purpose of the project we started last week: to create an online gallery of graffiti art from different geographic locations… Last week – we went over how to take input from the user, post the data to PHP as well as sending a response back from PHP (server) to the browser (client). Today we will focus on how to STORE data given by a user. So how do we do this? We will create tables to hold the data and will load these tables with the desired information.
Then we can query (answer questions) about the data that users input by retrieving the data from the tables. As we would like to have a simple web interface, we will use PHP to access and manipulate these tables. Therefore, in this lecture the following operations will be demonstrated:

  • Create a database
  • Create a table
  • Load data into the table
  • Delete data from the table
  • Retrieve data from the table in various ways

SETUP AND CREATE A DATABASE WITH SQL LITE 3

We will be using SQL lite version 3 since mysql is not available from the cda: we will refer to oreilly’s website for the definition of sql lite: what is sqlite

PHP provides two SQLite extensions by default since version 5.0. The latest SQLite extension is known as sqlite3 extension that is included in PHP 5.3+.

The sqlite3 extension provides an interface for accessing SQLite 3. The sqlite3 includes class interfaces to the SQL commands. In addition, it allows you to create SQL functions and aggregate using PHP.

PHP introduced the PDO (PHP DATA OBJECTS) interfaces since version 5.1. The PDO is the latest PHP solution that provides a unified database access interface. Note that PDO is just an abstract layer that allows you to use a common library to access any databases. In the context of SQLite, it needs sqlite3 extension to access SQLite database.

The PDO_SQLITE extension provides the PDO driver for the SQLite 3 library. It supports standard PDO interfaces, and also custom methods for creating SQL functions and aggregates using PHP. This is the only option with regards to creating/accessing/modifying and querying a database on the CDA servers. Therefore, this class will only cover the details with rgeards to the PDO_SQLITE extension. If you were to use a different server – you would possibly have other options…

First, create a folder called db outside outside of your project directory – this is where you will store your database. It is important to note that the db should NOT be part of the web directory.
So: if you were to have your website run on the hybrid cda servers you would want to ensure that the database & directory is stored one level above your public html folder. I would therefore highly reccomend that you mirror this structure when setting up your project / and for this class:;

  • Create a folder called CART_351_SQL_CLASS
  • Within this folder make two folders: public_html + db
  • Within the public_html folder: make a new folder: CART351_Gallery_Ex
  • Create an images folder inside the CART351_Gallery_Ex folder and copy the images included with this workshop into that folder…
  • You will put all your php files + html+js+css+images into this folder (you can make folders for js/css…) AND you will run the PHP server from within this folder (in atom run php server here)…

Now: we also need to ensure that everyone has the SQLLite extenion set up correctly … make a php page within the CART351_Gallery_Ex folder called index.php . Then:

1
2
3
<?php
echo phpinfo();
?>

Start your php server (index.php should open) – and verify that the PDO Driver for SQLite 3.x is installed (search on the page)… and sqlite3 … If not then you will not be able to do this workshop πŸ™

Next, we will now create our database using php, there are alternative methods i.e. using the terminal, python, ruby, c, java. However, as we are creating a web application, it makes sense to use a language like PHP to communicate with the data base.
Create a new php page in the project directory and save it as openDB.php . The script in this page will either create a new database if it does not exist at that path OR it will open the database at the specified path.
In order to establish a database connection to an SQLite database, we need to create a new instance of the PDO class and pass a connection string to the constructor of the PDO object:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
try {
    /******************************************
    * Create databases and  open connections*
    ******************************************/
 
    // Create (connect to) SQLite database in file
    $file_db = new PDO('sqlite:../../db/graffitiGallery.db');
  // Set errormode to exceptions
    $file_db->setAttribute(PDO::ATTR_ERRMODE,
                            PDO::ERRMODE_EXCEPTION);
    echo("opened or connected to the database graffitiGallery <br>");
   }
catch(PDOException $e) {
    // Print PDOException message
    echo $e->getMessage();
  }
  ?>

Explanation:::
1. Within the try clause – we attempt to instantiate an instance PDO object assign it to the variable $file_db.
2. If for any reason this instruction is NOT successful then an error will be automatically “thrown”.
3. The catch clause will catch this error and will print out a relevant message …
4. We set that any error reporting is done via exceptions (this is a choice we make).
5. Notice that when PHP connects to an SQLite database, if it does not exist, PHP will create a new SQLite database file.

Next: we will want to create another php file within the same directory: let’s call it: setUp_DB.php .
Ok – so this page will only be used once (for creating the inital database)…
As we have learn the require () – lets now use it here …. as to avoid repeating code:

1
2
3
<?php
 require('openDB.php');
?>

So if you run the page in the browser – you should see that a new file called graffitiGallery.db has been created in the db folder.

CREATE A TABLE IN THE DATABASE

Creating the database is the easy part, but at this point the database is empty.
The harder part is deciding what the structure of the database should be: the design of the database is very important. We start with asking the following questions:
1: What are and how many tables do we need?
2: What are the columns that should be in each of them?
We know that we want a table that contains a record (an entry) for each of the graffiti art pieces. This can be called the artCollection table, and it should contain, as a bare minimum, the title of each art piece. Because the title by itself is not very interesting, the table should contain other information:
For example, if the same artist produces different art pieces we may want to list all the art pieces created by a specific artist. Maybe we also want to keep some basic descriptive information such as geographic location,creation date, a description and a visual document (i.e. an image).
Please Note::: it is up to you as the developer to decide what fields you want in the database – depending on the content, context and usage (i.e. what kind of queries do you want to implement).

The next step is then to create a table. We use the sql CREATE TABLE statement to specify the layout of the table:
Note:: this code should ONLY be executed if the opening/creation of the database was successful – so put the code after the require statement.

1
2
3
4
5
6
7
8
9
10
try{
$theQuery = "CREATE TABLE artCollection (pieceID INTEGER PRIMARY KEY NOT NULL, artist TEXT, title TEXT,geoLoc TEXT, creationDate TEXT,descript ,image TEXT)";
$file_db ->exec($theQuery);
echo ("Table artCollection created successfully<br>");
$file_db = null;
}
catch(PDOException $e) {
    // Print PDOException message
    echo $e->getMessage();
  }

1. CREATE TABLE -> a command to create a table
2. artCollection -> the name of the table
3. Within parentheses we have the columns for the table: column names are artist, title, geoLoc, creationDate, descript, and image.
4. For each column, we specify the data type: TEXT is a good choice for the artist, title, geoLoc, creationDate, description columns because the column values vary in length and could contain both characters and numbers (as literal strings).
As there is no DATE type in SQL LITE we just set the creationData to TEXT.
For the image column we also specify TEXT, as we will store the path to the image (not the image itself).
Finally – we have a column called pieceID. When designing a database it is necessary to make sure that at any time we can always uniquely identify an entry. In our current design, none of the other columns uniquely identify an entry, and therefore we use an automatically incrementing id. Every time we insert a new entry into the database table, the id will be placed in the first column. This unique value is known as a Primary Key.
A table need only be created once – and this table will be stored in the db file graffitiGallery.

Notes:::
1. The variable $theQuery holds the SQL statement to create a table.
2. The method exec() (part of the SQLLite3 class) takes one argument: the SQL statement and executes that statement against the database.
3. The exec() statement is invoked through the database instance ($file_db).
Run the php page in the browser and make sure that you see the message “Table artCollection created successfully.”

Now that we have a database with a table in it – we can now insert entries into the table. The first example demonstrates inserting hardcoded entries into the database. In the second example we will use an html form to insert additional graffiti art pieces into the database.

Database Insertion:

Create a new php file and save it as insertIntoGallery.php.
Once again – before we can insert anything, we need to open the database. Include the require() statement in your file:

1
2
3
<?php
require('openDB.php');
?>

To insert data into a data base: one option is to do add one at the time using the INSERT INTO SQL command, which includes the following:
1: the name of the table we are inserting into
2: the names of the columns that we are inserting into
3: the values for each of the fields (in order).
Add the following after opening the database:

1
    $insertStatement =  "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Martha', 'Elephants','1998-03-04','Montreal','Description for the arts','images/alternative-paris-tours.jpg')";

Next, we will run the $file_db->exec() command to actually insert the data into the database:

1
2
3
4
5
6
7
8
9
10
11
 try
{
  $file_db->exec($insertStatement);
  echo ("INSERTION OF ENTRY into artCollection Table successful");
 // Close file db connection
  $file_db = null;
 }
  catch(PDOException $e) {
     // Print PDOException message
      echo $e->getMessage();
     }

Ok – if you now run the page – you should see a success message being echoed to the browser.
In the following example we define an array which holds seven different sql formatted statements for inserting a distinct entry with its required information.
(We do not need to specify the pieceID column as this value will be automatically added).
Subsequently, we run the $file_db->exec() method seven times, by using a standard for loop.
Append the php file with the following code and comment out the previous code otherwise you will re-insert the same entry into the database. Run this file in your browser.

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
require('openDB.php');
$queryArray = array(
  	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Sarah', 'Hippos','2002-06-12','Montreal','Description for the arts','images/Artists-Lane-2.jpg')",
  	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Harold', 'Untitled', '2012-10-21','New York','Description for the arts','images/gorod-stena-grafiti-4927.jpg')",
   	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Stephen', 'Scotland','1999-07-18','Edinborough','Description for the arts','images/graffiti-artist-scotland.jpg')",
   	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Martha', 'Tigers','2017-08-21','Paris','Description for the arts','images/maxresdefault.jpg')",
  	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Sarah', 'WIndow','2005-06-13','Toronto','Description for the arts','images/windows.jpg')",
  	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Sarah', 'Untitled', '2003-03-19','Halifax','Description for the arts','images/work-50.jpg')",
   	   "INSERT INTO artCollection (artist, title, creationDate, geoLoc, descript, image) VALUES ('Stephen', 'Zoo','2000-05-06','London','Description for the arts','images/multi.jpg')"
     );
 
     try {
       // go through each entry in the array and execute the INSERT query statement....
     for($i =0; $i< count($queryArray); $i++)
     {
        $file_db->exec($queryArray[$i]);
 
     }
     // if we reach this point then all the data has been inserted successfully.
    echo ("INSERTION OF ENTRY into artCollection Table successful <br>");
     }
 
     catch(PDOException $e) {
        // Print PDOException message
         echo $e->getMessage();
       }
 
?>

Database Retrieval

First: Create a new php file and save it as viewResults.php
Next: create a css folder -> in the CART351_Gallery_Ex folder.
And then: create a file called galleryStyle.css and add the following css: (NOTE: you can definetly make up your own):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* css for the initial form*/
label{display:inline-block; width:30%;font-size:1vw; font-weight: bold; align: center; margin-top:1%;}
textarea{vertical-align: top;width:50%;margin-left:2%;}
input{margin-left:2%;width:50%;}
fieldset{background-color:#38B4FC;width:50%;color:#555;font-family:arial;margin:0 auto;margin-top:2%;}
#buttonS{margin:0;width:auto;font-size:1vw;}
.sub{background:#8AD3FD;padding:2%;}
h3{text-align: center; width:50%;font-family:arial;color:#555;margin:0 auto; margin-top:5%;}
 
 
/* css for displaying the output*/
#back{display:flex;flex-wrap: wrap;width:100%;padding-left:5%;}
.outer{background-color:#0099ff;width:25%;margin:2%;}
.content{background-color:#006bb3;width:100%;}
.content p{color:white;padding:2%;background:#004d80;font-family:arial;}
img{margin:2%;width:96%;}

Next: We will be dynamically generating our HTML markup mostly in php: so first add the following to generate the required HTML markup:
Also – note how we open and close the php tags between the start and end of the HTML body – why because in between we will retrieve and output the content from the database…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!DOCTYPE html>
<head>
<title> Output from the Grafitti Gallery Database </title>
<link rel='stylesheet' type='text/css' href='css/galleryStyle.css'>
</head>
<body>
<?php
// get the contents from the db and output. ..
try {
 
 
 
}
catch(PDOException $e) {
    // Print PDOException message
    echo $e->getMessage();
  }
 
?>
</body>
</html>

So far: we have a a generic html template … In order to retrieve content from the database, we need to once again open the database. So – just after the opening php tag – add the same require() statement …

1
require('openDB.php');

Now that the data base is open we need to write the SQL statement to retrieve the data from the database: the $sql_select variable will contain the sql statement which uses the SELECT SQL statement. The general form of the statement is:
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

  • what_to_select indicates what you want to see. This can be a list of columns, or the (wildcard):* to indicate all columns.
  • which_table indicates the table from which we want to retrieve data.
  • The WHERE clause is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.

The simplest form of the SELECT statement is to retrieve ALL the rows and subsequent fields from the table.
We achieve this by using the wildcard to select ALL the fields within a row and no WHERE clause.
The query() method is used in order to return the result set.
With this result set, we have access to the retrieved data that meet the conditions of the query.
Append the php file with the following code (within the try clause).

1
2
3
4
$sql_select='SELECT * FROM artCollection';
// the result set
$result = $file_db->query($sql_select);
if (!$result) die("Cannot execute query.");

Now that we have the result set, we need to access each row from the result set. This is accomplished by using the PDO fetch method.
The result set contains a “pointer” which points to next entry in the result set. When the fetch() is called, the CURRENT entry from the result set represented as an array in returned. After the entry is fetched, the result set will move the “pointer” to the next entry in the result set.
There are three possible array types (set as flags in the function call) that this row is returned as:
1: FETCH_ASSOC: returns an associative array
2: FETCH_NUM: returns a numerical array (used previously)
3: FETCH_BOTH: returns both array types with associative indexes and numerical indexes.
The php $row variable will hold this array. Append the php file with the following code.

1
2
3
// fetch first row ONLY...
  $row = $result->fetch(PDO::FETCH_ASSOC);
  var_dump($row);

Now we have the $row variable which contains the first row from the result set represented as an associative array.
At this point you can save and view the page – in order to ensure that the page has no errors and that the first result “row” is output to the browser…
We still need to do a little more work to print out the entire result set.
Comment out the code to retrieve and view the first row – why?
Because we want to reset the result set pointer to the beginning
(Note: every time you fetch – the pointer goes forward … (RECALL:: fread() from a file — well its similar…):

1
2
3
// fetch first row ONLY...
  //$row = $result->fetch(PDO::FETCH_ASSOC);
 // var_dump($row);

We then use a while loop to fetch each row from the result set using fetch(). The while loop will automatically terminate when all rows have been read.
Within the while loop we go through each field within the row (using a foreach loop ), display each one and then go onto the next row. Now add the following code::

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
echo "<h3> Query Results:::</h3>";
echo"<div id='back'>";
// get a row...
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
   echo "<div class ='outer'>";
   echo "<div class ='content'>";
   // go through each column in this row
   // retrieve key entry pairs
   foreach ($row as $key=>$entry)
   {
     //if the column name is not 'image'
      if($key!="image")
      {
        // echo the key and entry
          echo "<p>".$key." :: ".$entry."</p>";
      }
   }
 
  // put image in last
    echo "</div>";
    // access by key
    $imagePath = $row["image"];
    echo "<img src = $imagePath \>";
    echo "</div>";
}//end while
echo"</div>";

Now if you run the viewResults.php in your browser – you should see all the results displayed.

Updating entries in a database:

The form of the SELECT statement we have just used is useful if you want to view your entire table, for example, after you’ve just loaded it with your initial data set…
Another very useful operation is UPDATING entries in the database.
For example, after viewing the initial results -> we determine that the title of the first entry should NOT be “Elephants”, rather the artist has determined that the title is actually “Untitled”.
So how to fix this? – We need to use an UPDATE SQL statement.
Create a new php file and save it as updateDatabase.php.
Once again we need to open the database – add the require() …

1
2
3
<?php
require('openDB.php');
?>

To update a specific entry into the data base, we use an UPDATE statement – and we need to specify the following:
1: the name of the table we are updating
2: the name of the column that we want to change using the “SET” keyword
3: assign a new value to the column name
4. a condition using the “WHERE” clause in order to identify the entry to be changes.
In the following example we SET the “title” column to be ‘Untitled’, and we identify the entry by the pieceID.
The SQL UPDATE statement is assigned to the $sql_update variable.
Subsequently, we execute the exec() command and do the appropriate error checking. Append the php file (within the try clause) with the following code.
Run this file in your browser. If you see the message “UPDATE OF table called artCollection successful”, means that database has been successfully populated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
try
{
  $sql_update="UPDATE artCollection SET title = 'Untitled' WHERE pieceID =1";
   // again we do error checking when we try to execute our SQL statements on the db
    $file_db ->exec($sql_update);
 
   // if we reach this point then all the data has been updated successfully.
    echo ("UPDATE OF table called artCollection successful");
 
 }
  catch(PDOException $e) {
     // Print PDOException message
      echo $e->getMessage();
    }
?>

Check that the title of the first entry has changed by loading the viewResults.php page.

Selecting particular Rows from the database:

As shown before, it is quite easy to view the entire table: just omit the “WHERE” clause from the SQL SELECT statement.
However, it is more typical that we do not want to view the entire table – rather we’re usually more interested in answering a particular question, in which case we need to specify some constraints on the information we want to retrieve. Let’s look at some selection queries in terms of questions about the grafitti pieces that they answer:

  • If for instance we only want to verify that the 1st entry has been updated correctly without viewing the entire table we can use the following SQL statement:

    1
    
     "SELECT * FROM artCollection WHERE pieceID = 1";

    1: Open viewResults.php page and under the $sql_select statement, make a new variable called $selectEntryOne and assign the above statement to this variable.
    2: modify the $result = $file_db->query($sql_select); statement to be $result = $file_db->query($selectEntryOne);
    3: Save and load the page.

  • We can specify conditions on any column, not just on the pieceID. For example, if we wanted to know which pieces were created during or after 2002, test the creationDate column using the following SQL statement:

    1
    
    "SELECT * FROM artCollection WHERE creationDate >=Date('2002-01-01')";

    Again assign this statement to a new variable, modify the query() method and reload the page.

  • We can also combine conditions, for example, to access pieces created after 2002 AND whose artist has the name “Sarah”:
    1
    
    "SELECT * FROM artCollection WHERE creationDate >=Date('2002-01-01') AND artist = 'Sarah'";

    Again assign this statement to a new variable, modify the query() method and reload the page.

  • The preceding query uses the ‘AND’ logical operator. There is also an ‘OR’ operator: i.e. if we want to select all pieces whose geolocation either Montreal or London:

    1
    
    "SELECT * FROM artCollection WHERE geoLoc = 'Montreal' OR geoLoc = 'London'";

    Again assign this statement to a new variable, modify the query() method and reload the page.

  • We can use both AND and OR in one SELECT statement, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped – in this example we look for either pieces created after 2003 and whose artist name is Sara OR pieces created before 2000 whose artist name is Stephen.

    1
    
    "SELECT * FROM artCollection WHERE (creationDate >=Date('2003-01-01') AND artist = 'Sarah')OR(creationDate <=Date('2000-01-01') AND artist = 'Stephen')";

    Again assign this statement to a new variable, modify the query() method and reload the page.

Selecting particular Columns from the database:

If you do not want to see all of the fields for each row in the result set, then just name the columns in which you are interested, separated by comma, instead of using the *.

  • If we want to know when ALL the pieces were created, we can just select the pieceId, title and creationDate columns for display:

    1
    
      $sql_selectA = "SELECT pieceID, title, creationDate FROM artCollection";

    Again assign this statement to a new variable and modify the query() method.
    Also, since we are NOT displaying the image as part of the result, comment out the 2 echo commands for image display in the while loop and reload the page.

  • To find out who the artists of each piece are, we can write the following statement:

    1
    
    "SELECT artist FROM artCollection";

    Again assign this statement to a new variable, modify the query() method and reload the page. Notice that this query simply retrieves the artist column from each entry, and some of them appear more than once.

  • To minimize the output, retrieve each unique output entry just once by adding the keyword DISTINCT:

    1
    
    "SELECT DISTINCT artist FROM artCollection";
  • You can use a WHERE clause to combine row selection with column selection. For example, to get creation dates for only the artists “Harold”, or “Sarah” we can use the following:

    1
    
    "SELECT creationDate, artist FROM artCollection WHERE artist = 'Harold' OR artist = 'Sarah'";

    Again assign this statement to a new variable, modify the query() and reload the page.

  • If you want to display the results in a particular order – then we just append our query statement (after the WHERE clause) with the ORDER BY clause.
    If for example we wanted to order the results by the creationDate field, then we would write the following query:

    1
    
    "SELECT creationDate, artist FROM artCollection WHERE artist = 'Harold' OR artist = 'Sarah' ORDER BY creationDate";

    Again assign this statement to a new variable, modify the query() and reload the page.

Counting Rows:

Databases are also used to answer the question, “How often does a certain type of data occur in a table?”
For example, we might want to know how many pieces we have, or how many pieces each artist has etc…
The function COUNT() is an aggregate function that returns the number of items from a particular group.

  • In is simpliest form is COUNT(*): which returns the number of rows in a table, including the rows including NULL and duplicates.

    1
    
     "SELECT COUNT(*) FROM artCollection";

    If you run this query – you will see that we still get a result set back from the query -> so we need to unpack the result just as any other query result (will be one row with one col).

  • We can use COUNT and combine with the WHERE clause i.e. if we wanted the number of artists who have pieces created after 2000:

    1
    
    "SELECT COUNT(*) FROM artCollection WHERE creationDate >=Date('2000-01-01')";
  • Lets get a little bit more complicated i.e. what if we wanted to count how many entries a given artist has?
    We can use the following SQL statement:

    1
    
    "SELECT artist, COUNT(*) FROM artCollection GROUP BY artist";

    This query is perhaps a little more complex to understand: to break it down:

    1. The GROUP BY clause gets resolved first: all pieces created by one artist are grouped together. GROUP BY returns one record for each group ONLY.
    2. Then the COUNT clause will count the number of pieces in each group.
    3. The output will be the number of pieces for each artist (one artist is a distinct group).

  • Another example using The GROUP BY clause: i.e if we wanted to count pieces with a distinct geoLocation we could group the pieces by geoLocation:

    1
    
    "SELECT geoLoc, COUNT(*) FROM artCollection GROUP BY geoLoc";
  • We can also have more complex groupings i.e. if we wanted to group the entries by artist and geoLocation:

    1
    
    "SELECT artist, geoLoc, COUNT(*) FROM artCollection GROUP BY artist,geoLoc";
  • Finally – you need not retrieve the entire table when you use COUNT().
    For example:- if we just want to group and count the number of pieces created by Sarah or Harold: we could use the following statement:

    1
    
    "SELECT artist, COUNT(*) FROM artCollection WHERE artist ='Sarah' OR artist ='Harold' GROUP BY artist";

** PLEASE note that what has been shown here is only a start to what is possible with SQL -> there is A LOT more, but that is beyond the scope of this class…

Deleting entries from a database:

The SQLite DELETE statement is used to delete existing entries from the database table.
You can use the WHERE clause with DELETE statement in order to delete specific rows, otherwise all the entries would be deleted.
We will create a new page for this example – in order for you to have seperate pages for each distinct operation.
Note: in reality you may have one script that does a mixture of things – but do PLEASE use require()
Create a new page called deleteEntries.php and add the follwing template code (opening the db , the try/catch clause…):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
require('openDB.php');
?>
<?php
try
{
 
 
 }
  catch(PDOException $e) {
     // Print PDOException message
      echo $e->getMessage();
    }
?>

Next:Let’s say we want to delete all entries for the artist “Martha” – therefore our WHERE clause will be to define the condition to select only pieces by the artist Martha i.e WHERE artist = ‘Martha'”.

1
2
3
    $sql_delete="DELETE FROM artCollection WHERE artist = 'Martha'";
    $file_db ->exec($sql_delete);
    echo ("DELETION OF entry in artCollection successful");

The command to execute the query – is as before: the exec() method …
Now: if you run the deleteEntries.php page, you should see the new message – and then if you reload the viewResults.php – you should now have NO results for Martha: use the query to verify:

1
 $sql_select='SELECT * FROM artCollection';

Adding Columns to a database:

We can also add new columns to a table by using the ALTER TABLE SQL statement. (Note that this statement will also allow us to change the name of the table – but we will not demonstrate this here…
Lets add a new column to our table: a random number.
So how do we implement this?
Create a new page called alterTable.php and add the follwing template code (opening the db , the try/catch clause…):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
require('openDB.php');
?>
<?php
try
{
 
 
 }
  catch(PDOException $e) {
     // Print PDOException message
      echo $e->getMessage();
    }
?>

Then – within the try() clause lets add a random number …

1
2
3
4
5
6
   $ran_num = rand(5,100);
   //echo($ran_num);
   // add a new column + default value where we use the php variable
   $sql_alter = "ALTER TABLE artCollection ADD COLUMN ran_num INTEGER NOT NULL DEFAULT '$ran_num'";
   $file_db ->exec($sql_alter);
   echo ("ALTERATION OF entry in artCollection successful");

The command to execute the query – is as before: the exec() method …

Now if you run the alterTable.php page: you should see the new message – and then if you reload the viewResults.php – you should now have a new field for each entry…
NOTE: that newly added column is filled with by default with NULL values. Therefore, in the SQL statement we specify a default value.
Also – you can only add one column at the time per SQL ALTER TABLE statement.

Database Insertion II:

As a final step we will use the php page that we created a few weeks ago to insert data and extend it to include the functionality for inserting new entries into our artCollection database. Create a new file called: InsertGalleryAJAX.php page.
If you do not have the file from a couple of weeks ago – then just copy and paste the following (it is the same) and save.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
<?php
//check if there has been something posted to the server to be processed
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
// need to process
 $artist = $_POST['a_name'];
 $title = $_POST['a_title'];
 $loc = $_POST['a_geo_loc'];
 $description = $_POST['a_descript'];
 $creationDate = $_POST['a_date'];
 if($_FILES)
  {
    //echo "file name: ".$_FILES['filename']['name'] . "<br />";
    //echo "path to file uploaded: ".$_FILES['filename']['tmp_name']. "<br />";
   $fname = $_FILES['filename']['name'];
   move_uploaded_file($_FILES['filename']['tmp_name'], "images/".$fname);
    //echo "done";
    //package the data and echo back...
    /* make  a new generic php object (note:: php also supports objects -
   but we are NOT doing that in this class - you can if you want ;)  )*/
    $myPackagedData=new stdClass();
    $myPackagedData->artist = $artist ;
    $myPackagedData->title = $title ;
    $myPackagedData->location = $loc ;
    $myPackagedData->description = $description ;
    $myPackagedData->creation_Date = $creationDate ;
    $myPackagedData->fileName = $fname ;
     /* Now we want to JSON encode these values as a JSON string ..
     to send them to $.ajax success  call back function... */
    $myJSONObj = json_encode($myPackagedData);
    echo $myJSONObj;
    exit;
 
  }//FILES
}//POST
?>
 
<html>
<head>
<title>Sample Insert into Gallery Form USING JQUERY AND AJAX </title>
<!-- get JQUERY -->
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<!--set some style properties::: -->
<link rel="stylesheet" type="text/css" href="css/galleryStyle.css">
</head>
<body>
  <!-- NEW for the result -->
<div id = "result"></div>
 
<div class= "formContainer">
<!--form done using more current tags... -->
<form id="insertGallery" action="" enctype ="multipart/form-data">
<!-- group the related elements in a form -->
<h3> SUBMIT AN ART WORK :::</h3>
<fieldset>
<p><label>Artist:</label><input type="text" size="24" maxlength = "40" name = "a_name" required></p>
<p><label>Title:</label><input type = "text" size="24" maxlength = "40"  name = "a_title" required></p>
<p><label>Geographic Location:</label><input type = "text" size="24" maxlength = "40" name = "a_geo_loc" required></p>
<p><label>Creation Date (DD-MM-YYYY):</label><input type="date" name="a_date" required></p>
<p><label>Description:</label><textarea type = "text" rows="4" cols="50" name = "a_descript" required></textarea></p>
<p><label>Upload Image:</label> <input type ="file" name = 'filename' size=10 required/></p>
<p class = "sub"><input type = "submit" name = "submit" value = "submit my info" id ="buttonS" /></p>
 </fieldset>
</form>
</div>
<script>
$(document).ready (function(){
    $("#insertGallery").submit(function(event) {
       //stop submit the form, we will post it manually. PREVENT THE DEFAULT behaviour ...
      event.preventDefault();
     console.log("button clicked");
     let form = $('#insertGallery')[0];
     let data = new FormData(form);
 
     $.ajax({
            type: "POST",
            enctype: 'multipart/form-data',
            url: "insertGalleryAJAX.php",
            data: data,
            processData: false,//prevents from converting into a query string
            contentType: false,
            cache: false,
            timeout: 600000,
            success: function (response) {
            //reponse is a STRING (not a JavaScript object -> so we need to convert)
            console.log("we had success!");
            console.log(response);
            //use the JSON .parse function to convert the JSON string into a Javascript object
            let parsedJSON = JSON.parse(response);
            console.log(parsedJSON);
            displayResponse(parsedJSON);
            //reset the form
            $('#insertGallery')[0].reset();
           },
           error:function(){
          console.log("error occurred");
 
        }
      });
   });
 
   // validate and process form here
    function displayResponse(theResult){
      let container = $('<div>').addClass("outer");
      let title = $('<h3>');
      $(title).text("Results from user");
      $(title).appendTo(container);
      let contentContainer = $('<div>').addClass("content");
      for (let property in theResult) {
        console.log(property);
        if(property ==="fileName"){
          let img = $("<img>");
          $(img).attr('src','images/'+theResult[property]);
 
          $(img).appendTo(contentContainer);
        }
        else{
          let para = $('<p>');
          $(para).text(property+"::" +theResult[property]);
            $(para).appendTo(contentContainer);
        }
 
      }
      $(contentContainer).appendTo(container);
      $(container).appendTo("#result");
    }
});
</script>
</body>
</html>

We will use the database that we just made and then –
Instead of just echoing the data back to JQUERY -> we will store it in our database…

  1. Remove the following:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    
        $myPackagedData=new stdClass();
        $myPackagedData->artist = $artist ;
        $myPackagedData->title = $title ;
        $myPackagedData->location = $loc ;
        $myPackagedData->description = $description ;
        $myPackagedData->creation_Date = $creationDate ;
        $myPackagedData->fileName = $fname ;
         // Now we want to JSON encode these values to send them to $.ajax success.
        $myJSONObj = json_encode($myPackagedData);
        echo $myJSONObj;
  2. Now – we input our data into the database:
    Specifically: we need to first open the database and assign a reference to it. So add in the require():

    1
    
    require('openDB.php');
  3. Now: we want to INSERT data from the posted form using the INSERT INTO command (like before). But instead of using literal values in this query we pass the variables. So within the try clause add:
    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
    
     try{
       /*The data from the text box is potentially unsafe; 'tainted'. Use the quote() - puts quotes around things..
          It escapes a string for use as a query parameter.
          This is common practice to avoid malicious sql injection attacks.
          PDO::quote() places quotes around the input string (if required)
          and escapes special characters within the input string, using a quoting style appropriate to the underlying driver. */
          $artist_es =$file_db->quote($artist);
          $title_es = $file_db->quote($title);
          $loc_es =$file_db->quote($loc);
          $description_es =$file_db->quote($description);
          $creationDate_es =$file_db->quote($creationDate);
          // the file name with correct path
          $imageWithPath= "images/".$fname;
          $rnNum = rand(5,100);
     
          $queryInsert ="INSERT INTO artCollection(artist, title, creationDate, geoLoc, descript, image,ran_num)VALUES ($artist_es,$title_es,$loc_es,$description_es,$creationDate_es,'$imageWithPath','$rnNum')";
          $file_db->exec($queryInsert);
          $file_db = null;
          echo("done");
          exit;
        }
        catch(PDOException $e) {
            // Print PDOException message
            echo $e->getMessage();
          }
  4. Lastly: modify the Ajax function that it does not try to parse any json data – as we have only echoed a “done” message…

You should recognize that this is almost the same code as for the first set of inserts that we did – but this time we use variables which hold the desired values. Run the InsertGalleryAJAX.php page, fill in some values, then if all has processed everything correctly, a success message will be sent back to the JQUERY AJAX callback function. Verify that data has been added to the database by running the viewResults.php page.

Database Retrieval with AJAX:

Ok – as a last task lets get some data from the database and display by using JQUERY and AJAX… Make a new php page called retrieveGalleryAJAX.php.
Here is the file in its entirety:
ALSO – ensure to comment out the echo statement in openDB.php -> else the result to JQUERY will be malformed.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
<?php
//check if there has been something posted to the server to be processed
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
 
  require('openDB.php');
   try {
      // need to process
      // could have different types of queries
      $criteria = $_POST['a_crit'];
      if($criteria == "ALL")
      {
      $querySelect='SELECT * FROM artCollection';
      $result = $file_db->query($querySelect);
      if (!$result) die("Cannot execute query.");
      }
    // get a row...
    // MAKE AN ARRAY::
    $res = array();
    $i=0;
    while($row = $result->fetch(PDO::FETCH_ASSOC))
    {
      // note the result from SQL is ALREADy ASSOCIATIVE
      $res[$i] = $row;
      $i++;
    }//end while
    // endcode the resulting array as JSON
    $myJSONObj = json_encode($res);
    echo $myJSONObj;
 
  } //end try
     catch(PDOException $e) {
       // Print PDOException message
       echo $e->getMessage();
 
     }
      exit;
}//POST
?>
 
<!DOCTYPE html>
<html>
<head>
<title>Sample Retrieval USING JQUERY AND AJAX </title>
<!-- get JQUERY -->
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<!--set some style properties::: -->
<link rel="stylesheet" type="text/css" href="css/galleryStyle.css">
</head>
<body>
<div class= "formContainer">
<!--form done using more current tags... -->
<form id="retrieveFromGallery" action="">
<!-- group the related elements in a form -->
<h3> RETRIEVE STUFF :::</h3>
<fieldset>
<p><label>Criteria:</label><input type = "text" size="10" maxlength = "15"  name = "a_crit" value = "ALL" required></p>
<p class = "sub"><input type = "submit" name = "submit" value = "get Results" id ="buttonS" /></p>
 </fieldset>
</form>
</div>
<!-- NEW for the result -->
<div id = "back"></div>
<script>
$(document).ready (function(){
    $("#retrieveFromGallery").submit(function(event) {
       //stop submit the form, we will post it manually. PREVENT THE DEFAULT behaviour ...
    event.preventDefault();
     console.log("button clicked");
     let form = $('#retrieveFromGallery')[0];
     let data = new FormData(form);
     $.ajax({
            type: "POST",
            enctype: 'text/plain',
            url: "retrieveGalleryAJAX.php",
            data: data,
            processData: false,//prevents from converting into a query string
            contentType: false,
            cache: false,
            timeout: 600000,
            success: function (response) {
            console.log(response);
            //use the JSON .parse function to convert the JSON string into a Javascript object
            let parsedJSON = JSON.parse(response);
            console.log(parsedJSON);
            displayResponse(parsedJSON);
           },
           error:function(){
          console.log("error occurred");
        }
      });
   });
 
   // validate and process form here
    function displayResponse(theResult){
      // theResult is AN ARRAY of objects ...
      for(let i=0; i< theResult.length; i++)
      {
      // get the next object
      let currentObject = theResult[i];
      let container = $('<div>').addClass("outer");
      let contentContainer = $('<div>').addClass("content");
      // go through each property in the current object ....
      for (let property in currentObject) {
        if(property ==="image"){
          let img = $("<img>");
          $(img).attr('src',currentObject[property]);
 
          $(img).appendTo(contentContainer);
        }
        else{
          let para = $('<p>');
          $(para).text(property+"::" +currentObject[property]);
            $(para).appendTo(contentContainer);
        }
 
      }
      $(contentContainer).appendTo(container);
      $(container).appendTo("#back");
    }
  }
 
});
</script>
</body>
</html>
  • We create an instance of the DB as before in php
  • There is a different HTML form, whereby by default ‘ALL’ is the criteria
  • When the button is pressed – as before the button callback is triggered, and within is the .$ajax()
  • We are still POSTING data – but this time with only one value (the criteria) – what we will allow the user to potentially search by
  • The data is POSTED to the php page – the criteria is assigned, and we build an SQL query to retrieve the data from the database
  • The result set is retrieved as arrays of key-> value pairs (associative) -> really good for sending JSON back ….
  • Each row is subsequently stored in a php array, the php array is then encoded as a JSON string and echoed back
  • The result is picked up the success callback function, parsed into a JavaScript object and passed to the function which will handle the displaying of the results.

Hopefully – this part was already a bit familiar πŸ™‚ – and note that the examples we did today – you can modify and reuse in your final projects – they are intended to be base scripts for you to use.