In previous posts I’ve covered a general overview of how SQL and PHP work together and the benefits of creating dynamic content. We have discussed content management systems and how they can simplify your life as a webmaster. Now we’ll start to look at how to actually construct a content management system, starting with connecting PHP to MySQL. 

First, we need to establish a connection using the builtin PHP function, “mysqli_connect”. The old PHP functions for MySQL communication have been outdated by the new “mysqli” family of functions. The standard functions haven’t  been deprecated, so you can still use them, but the added functionality of the “mysqli” group is very useful to have. 

Let’s set up an example where we connect to a MySQL database and pull out the member names, just like in the other examples. For clarity’s sake I’ll use the standard SQL functions instead of regular expressions. 

<?php
 //declare a global variable for database interaction (can be called by any function)
global $mysqli;  
//connect to server and select database; you may need it
$mysqli = mysqli_connect("localhost", "username", "password", “memberDB”);  
//if connection fails, stop script execution
if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
}   
//get member information
$get_data_sql = "SELECT lastname, firstname, email FROM members WHERE lastname LIKE “F%”;
  
//get the data or exit if there is an error
$get_data_res = mysqli_query($mysqli, $get_data_sql) or die(mysqli_error($mysqli));
while ($member_info = mysqli_fetch_array($get_data_res)) {
            $lastN = $member_info['lastname'];
            $firstN = $member_info['firstname'];
            $email = $member_info['email'];
echo “Last Name: “.$lastN.”First Name: “.$firstN.”Email: “.$email;
 }
//close connection to MySQL
mysqli_close($mysqli);
?>

When using this code, you will need to fill in “username” and “password” with the actual values you have set up for your database. It is bad practice to use the root account for this, as any script would have administrative access. Instead, set up another account that only has SELECT, INSERT, and UPDATE privileges. 

In the (while) loop we are pulling the data out of an array we construct from the record that MySQL returns based on our query. We then echo or print to screen the information so we can verify the script is working. Notice the break (
) tags inside the echo line – we are embedding HTML tags in PHP scripts. The other parts of the notation enable us to string together strings and variables. When the final print out is made, we will have something like the following:

Last Name: Falwell
First Name: James
Email: jfalwell@gmail.com

Last Name: Farney
First Name: Sarah Marie
Email: sarah_baby@msn.net

….

Last Name: Flaherty
First Name: Timothy
Email: tflaherty@salvationarmy.org

Pretty neat huh? We can use similar code to insert data into the database. The main code that we will reuse is the connection code, so perhaps we should make it modular and stick it into its own function. Next time I’ll show you how to make a login console that you can access from everywhere on your site and connect to different databases or using different logins! We’ll also discuss the php include() directive and how to make your page code modular to save space and leverage the power of the PHP scripting language.

Labels: CMS, command syntax, Content Management, Lesson, MySQL, PHP, SQL

Last time we defined the meaning of a database and picked apart a few examples queries from the MySQL camp. To recap, we looked at CREATE TABLE, INSERT, UPDATE, ALTER TABLE, and SELECT statements. Just to refresh your memory, they use the following syntax (abbreviated listing): 

SELECT a, list, of, stuff  FROM tablename

ALTER TABLE tablename ADD name datatype other_parameters

UPDATE tablename SET colname = somevalue WHERE somecolumn = ‘some_value’

INSERT INTO tablename (list, of, colnames) VALUES (‘list’, ‘of’, ‘values’)

CREATE TABLE (colname_1 datatype, colname_2 datatype, …colname_N datatype) 

These are just a few of the basic commands or queries that are typically executed on a daily basis. Create Table is a little rarer in an established application since we are focusing on getting data into and out of an existing database. Applications that must dynamically create a database every run time are a niche product and do not represent typical behavior.

SQL Search Engine
Let’s say you want to make a SQL search engine that pulls data out from our members database. Suppose we want to generate a listing of all of the members, sorted alphabetically. We could construct the following query: 

mysql> SELECT * FROM members ORDER BY lastname ASC;

This will pull all of the data about each member and return a list that is sorted from A to Z. If we want the data returned from Z to A we would specify DESC (descending) after ORDER BY instead of ASC (ascending). At a minimum SQL will let you sort by an integer column, alphabetical (VARCHAR or TEXT), decimal or floating point, and date columns. Since the SQL engine doesn’t know the lexical ordering of raw binary data, BLOB columns aren’t typically sorted. Even TEXT columns, since they have an “unlimited” length, will only sort up to X amount of characters (this value can be changed at the cost of system efficiency). 

Grouping the Data
Well, that was fine and dandy, but I have over 25,000 members and that’s just too large of a list. Perhaps we could break it down a little more, let’s say by letter. We want to be able to create an interface like that below, where we can click on a letter and get all of the last names that begin with that letter, sorted alphabetically to return the following results:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 

Name                                      Email

Falwell, James                         jfalwell@gmail.com

Farney, Sarah Marie                sarah_baby@msn.net

Fardwood, Alexandra             cutsiepie99@hotmail.com

Fickleton, Martin                    martin_fickleton@cnn.com

Fiduciary, Dixon                     fdixon@gatech.edu

Fixington, Thomas                  thomas-the-tank@gmail.com

Flaherty, Timothy                   tflaherty@salvationarmy.org

We would construct the query:

mysql> SELECT lastname, firstname, email FROM members WHERE lastname LIKE “F%”;

 The syntax used here is a call to a SQL function “LIKE” that can take parameters such as the “F” and “%”. The % is a wildcard that matches any number of characters. There are different wildcards that can match single characters, or a specified number of characters. 

Perhaps you want a more advanced engine than can find parts of words (substrings) inside a given column. We can modify the above example by adding a “%” before the letter. 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname LIKE “%F%”; 

Now we will get any and all members that have an “F” somewhere in their last name. The wildcards just say “find an f” even if there are letters to the left or right. Notice that % matches zero or more characters – the search will still return the members who’s last names begin with “F”.

Regular Expressions
If you want to do even more advanced searches you can use regular expressions. Regular expressions provide a concise and flexible means for identifying strings of text of interest, such as particular characters, words, or patterns of characters. Regular expressions are written in a formal language that can be interpreted by a regular expression processor, a program that either serves as a parser generator or examines text and identifies parts that match the provided specification. They can be very complex or very simple – returning results such as those above, or employed in an advanced data processing suite. Regular expressions are not SQL based, they are used in almost every developed programming language available. 

Regex based searches are generally very efficient and can be optimized for speed by an advanced programmer. As with most programming paradigms, there are many ways to employ regex strings to attack any one problem. There are “best-practices” to follow when using regular expressions, but in general you need to become familiar with the technology and syntax before you start optimizing statements. 

As exemplified by this XKCD comic, regular expressions are a very powerful tool that you will make you wonder what you ever did without them. Now, it’s not absolutely required that you start using regular expressions right now; in fact, it’s probably better for you to get accustomed to the SQL syntax before delving into this more complex topic. That being said, I’ll show you a few quick examples of what regex searches can do for you! 

Let’s start with the example above. We wanted to find all members whose last names started with the letter “F”. To make this into a regular expression based search, we’d simply type the following. 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname REGEXP “^F”; 

or to be more explicit, 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname REGEXP “^F.+$”;

If we break down the search string “^F” we have two components, the letter we are searching by and some other character “^”. This character just tells the regex engine to start looking at the beginning of the word. If we would omit the “^” we would get all results that had an “F” anywhere in the lastname column. 

The second example adds a few more characters that explicitly define our search as “starting at the beginning of the last name, search for an F and any other characters until the end.” The dollar sign “$” is a tag for the end of the string. “.+” just represents any number of any characters. The “.” specifier matches any character, including numbers and whitespaces, while the “+” symbol says to apply the previous specifier to any number of characters.

 This is a very basic example of regular expressions and the syntax used in more advanced SQL queries. I’ll be posting a simple table guide later so you don’t have to wade through an entire post to get the information you need! Let me know your thoughts in the comments!

Labels: Databases, Lesson, MySQL, queries, regular expressions, relational databases, SQL

A database structure composed of more than one flat file (2-dimensional arrays) that can be transformed to form new combinations because of relations between the data in the records, in contrast to hierarchical and network database structures.

For example, in an earlier post I discussed the Members table of the content management system used at DanShope.com. The Members table contains all of the information provided by any given user, associated to a primary key, ID. Other tables such as the Projects table references this as MemberID so that we can tie specific projects to a particular member without repeating all of the member data in each project record.

In the same way, each project has its own ID that is referenced by the Pages table as ProjectID. Now we can have lots of pages that all point to one project, which points to one member. Any one member can have lots of projects that each contains lots of pages. This is an example of a one-to-many relationship, where the one MemberID is related to many projects, and each ProjectID is related to many pages.

Getting back to the MyLibrary example, we might also add another table called Members and a few columns to our existing tables, (ID, MemberID, SignedOut). The ID column should be configured as an Unsigned Integer (only positive whole values), Auto_Increment (each new record has an ID 1+ the last ID), and Primary Key (Each ID is used only once). To add a column to an existing table we use the following

mysql> ALTER TABLE Books ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

I also specified this column as “NOT NULL” which means a value is always required. Since this is an auto_increment field, we WILL always have a value, automatically. MemberID will also be an unsigned integer so that it matches the ID column we will create for the Members table. However, it should not be auto_increment since we will need to specify this relation manually.

When we need to sign a book out of the library, the book record is updated to set the SignedOut field to true and the MemberID field to the ID of the member receiving the book. We can do this using the following syntax:

mysql> UPDATE Books SET MemberID = ‘1’, SignedOut =’true’ WHERE ID=’2’;

To use the UPDATE syntax we must already know which record we are updating using the WHERE clause. For other types of queries we can use nested or joined statements, such as getting the names of all the members who currently have books out.

mysql> SELECT Books.Title, Members.Name FROM Books LEFT JOIN Members ON Books.MemberID = Members.ID WHERE Books.SignedOut=’true’;

Here we are doing something pretty magical. I’m able to pull intermixed data from two seemingly unrelated tables, Members and Books. Indeed, by searching through the Books table for records that are signed out, pulling the associated MemberID, and comparing it to the ID in Members, I can get the Member’s name or any other pertinent information. You can see that if we added a date due column I could even test which members had books overdue and automatically compile a list for the librarian.

There are different types of JOINs based on what you are trying to accomplish and which SQL engine you are using. In this example we used a LEFT (INNER) join; there are also RIGHT (OUTER) joins. Left joins just mean that we will return every single unique record from the table listed leftmost (Books) and any records that match it from the right table (Members). If we did a RIGHT join we would get a list of all members whether they had books signed out or not.

Oh, how far we’ve come from our simple SELECT * FROM Books statement just moments ago. Now we are really starting to see the power of SQL and how properly constructed statements can do the heavy lifting of our data once we have it inserted.

Getting the data in place isn’t that difficult, and for a dedicated application you would hard code the statements instead of manually typing them every time. Even with these hardcoded statements, the data that doesn’t stay the same (anything after the equals sign) could remain dynamic. “Hardcoded” could either be a string or set of strings we store inside our application, or they could be SQL Prepared Statements, a really nifty feature we will discuss later. They both have the same end result (getting our data), but prepared statements have some neat benefits in the realms of security and database efficiency.

There’s a lot to be said about relational databases, indeed entire volumes have been published about the subject and it is still an area of constant research and refinement. I’ll post more examples later on that utilize data connections between tables that allow us to export valuable statistics and other data that would be time-consuming to correlate without the magic of SQL.

Labels: Databases, INSERT, JOIN, MySQL, relational databases, SELECT, SQL, UPDATE

In the last few topics I’ve been talking about these information storage repositories called databases, but what are they really? We’ll talk about SQL databases as applied to MySQL examples, but understand that the fundamental principles of databases apply to all the engines, whether you’re using SQL Server, PostgreSQL, Oracle, SQLlite, MySQL, or some other variant.

Merriam-Webster’s defines a database as:

 “a collection of data organized especially for rapid search and retrieval (as by computer)”

If you open any given database file you’ll see a bunch of plaintext (human-readable) characters, and some large chunks of numbers. If the database is encrypted for security purposes, you won’t be able to read it like this. From this we can see that the actual data storage isn’t really what’s special; it’s the SQL engine that interprets this data.

Data is stored in tables, or sets of similar data. Inside a table is a set of columns, just like in a spreadsheet. Let’s look at an example of this data structure. Say we have a database, MyLibrary. Inside MyLibrary you can imagine we might have tables for Books, Magazines, Movies, and Music. The books table would have columns for (Title, Author Last, Author First, Genre, Publisher, ISBN, Call Letters, etc.). When we insert data into the books table, we simply add a new row. Each of the other tables (Magazines, Movies, and Music) would have similar columns to store and sort the relevant data.

So how do we actually create our databases? Once you have your selected client installed, you need to construct a CREATE TABLE statement that will form the database table and columns we want. For our books table, we would execute the following:

mysql> CREATE TABLE Books (Title TEXT, AuthorLast VARCHAR(30), AuthorFirst VARCHAR(30), Genre VARCHAR(50), Publisher TEXT, ISBN VARCHAR(13), CallLetters VARCHAR(3) );

Let’s break the statement down and look at what each part means. The “mysql>” part isn’t something you would type – that’s the prompt in the MySQL Command Line window (graphic). The words after each of our columns are format specifiers that set the data type of each column. All of the data we are storing right now is text-based (TEXT and VARCHAR). TEXT columns can store large amounts of text data, such as news articles or essays. VARCHAR columns are fixed-width, meaning you must specify the maximum number of characters that can be stored. For the Author fields, I assumed that most names are not going to be over 30 characters long. The ISBN is a much more valid assumption since the maximum ISBN is indeed 13 characters long. If you try to insert data that is longer than the field width it will simply be truncated to length. There are several other datatypes we can store such as INT (numbers), DECIMAL (numbers with decimal places), and BLOB (binary data, such as pictures).

To interact with your database you can use either a GUI (graphical user interface) based system where you click and drag elements around to insert, update, and delete data, or a command line tool. Both are equally good methods of working with data, but you will learn a lot more if you use the command line tool. It might look a little bit intimidating at first, but if you follow the syntax given here everything should work just fine. To insert data we need to use the following syntax:

mysql> INSERT INTO Books (Title, AuthorLast, AuthorFirst, Genre, Publisher, ISBN, CallLetters) VALUES (‘My First Book’, ‘Brown, ‘Joe’, ‘Romance’, ‘Tommy Nelson Publishers’, ‘0194820184928’, ‘BRO’);

After we specify the table name (Books) we must send a list of columns we want to insert data into. In this example we were inserting data into every single column, so we could have omitted the section between Books ... VALUES. However, in general we need to specify these columns since generally we don’t know all of the information for a particular record at time of insertion.

Once we have some data added, we can use a SQL Query to retrieve records from our database. A record is just a set of data about a unique record. If I want to see all of the books I own, I would execute the following query:

mysql> SELECT * FROM Books;

We start with “SELECT”, which just tells SQL that we will be returning records. Later we’ll look at other words that start SQL statement like DELETE, UPDATE, and INSERT. The “*” is a wildcard character. In this case it means “return all data about this record” or “return all columns of data for this record”. In place of the star we could list a specific subset of the columns or just specify our own order such as (Title, Author Last, Genre) to only return three columns of data. “FROM Books” just tells the database engine that we are trying to get data out of the Table named “Books.” This could have just has easily been Magazine, Movie, or Music, as long as the columns listed in the statement appear in that table. For example, we couldn’t perform a search for a column named “Call Letters” on a Music table if that table didn’t have its own column named “Call Letters”. The semicolon “;” is just a terminating character that tells MySQL to execute the preceding statement.

Now say we want to get a list of books by a specific author. This is where databases start to get really cool – they’re great at sorting your data very quickly. If we want to look up books by Joe Brown, we would construct a query like

mysql> SELECT * FROM Books WHERE AuthorLast = ‘Brown’;

This looks pretty much like our last query, but we’ve added a WHERE clause. The WHERE clause allows us to filter our data so we only retrieve those records we are interested in. You can construct advanced WHERE clauses, such as filtering only books by Joe Brown in the Romance genre published after December 21^st, 1995. That WHERE clause would use “AND” between each statement, such as

… WHERE AuthorLast =’Brown’ AND Genre=’Romance’ AND PublishDate > ‘1995-21-12’;

Another useful filtering uses “OR” between statements. We can use OR to select books by multiple authors or genres, for example. A sample query for using WHERE and OR might look like

… WHERE AuthorLast =’Brown’ OR AuthorLast=’Martin’ OR AuthorLast=’Samson’;

Now you have learned what a database is, how to create a table, store some data, and pull it out with intelligent queries. Next time we’ll talk about what it means to have a so-called “relational” database and how to exploit a fuller feature set of the MySQL database engine.

Labels: Databases, Lesson, MySQL, PHP, SQL