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

As promised here is a quick guide to some of the most frequently used commands in any SQL environment. You can find a more detailed description in the introduction to relational databases. 

Commands are not case-sensitive - they do not need to be capitalized. It's common practice to use all caps for commands so that dynamic data (table names, inserted values) can be noticed with ease.

• CREATE Command - is used to create a database/table.
  
• SELECT Command - is used to retrieve data from the database.
  
• DELETE Command - is used to delete data from the database.
  
• INSERT Command - is used to insert data into a database.
  
• UPDATE Command - is used to update the data in a table.
  
• DROP Command - is used to delete or drop the database/table.
  

 Syntax for Query Commands

CREATE Command
The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:

mysql> CREATE TABLE tablename;

Example:

mysql> CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0' ;

SELECT Command
The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:

Syntax:

mysql> SELECT field_names FROM tablename;

Example:

mysql> SELECT * FROM tblstudent;

DELETE Command
The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:

mysql> DELETE * FROM tablename WHERE condition;

Example:

mysql> DELETE * FROM tblstudent WHERE fldstudid=2";

INSERT Command
The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:

mysql> INSERT INTO tablename(fieldname1,fieldname2..) VALUES(value1,value2,...) ;

Example:

mysql> INSERT INTO Tblstudent(fldstudName,fldstudmark) VALUES(Baskar,75) ;

UPDATE Command
The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:

mysql> UPDATE Tablename SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber;

Example:

mysql> UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2;

DROP Command
The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:

mysql> DROP tablename;

Example:

mysql> DROP tblstudent;

Labels: command syntax, Databases, Lesson, MySQL, relational databases