This is our third installment of the CMS instant edit script. Here we will be talking about our last two languages “PHP & MySQL”. PHP is a server side language that preform’s more complex and secure server side functions such as creating and editing files on the server or storing and retrieving sensitive data from a database. MySQL is a well uses and popular database that works almost hand and in hand with PHP kind of a match maid in heaven sort of thing.
If you are not familiar with the two server side operations than I suggest you read more about the then at Tizag.com before trying to fully understand this last tutorial.
NOTE: This tutorial assumes you will already have a database and table setup in your MySQL. Here is the SQL to create this table.
CREATE TABLE `cms_demo` ( `id` tinyint(255) NOT NULL default '0', `content` longtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Our last goals for our instant edit is to save and retrive information to and from our MySQL database. To do this we must speak to the database with the means of a server side language. PHP is a server side language and in fact speaks most clearly to MySQL than other server side languages such as .asp or aspx
In our last tutorial we created two AJAX functions that open and save information from our database via secure HTTP requests. These two functions both called upon two different server side files. Before we even get into creating these two files we must first talk about how a server side language begins to even speak to a database.
Before we can speak to the database we must first connect to it. The most secure way to do this is to have a third party file that connects to MySQL and get included into both “openFromDatabase.php” and “saveToDatabase.php”
Make a blank PHP document like so:
Now lets add the portion that connects to our MySQL. Make your document look like this:
Ok here we have 4 variables that we use to connect to MySQL.
- $dbhost = your web server host name or IP address
- $dbname = MySQL is made up of databases that contain an infinite amount of tables here we declare what the name of the database we want to connect is
- $dbuser = MySQL is typically setup with a admin username in place here you must provide that admin username such as “root”
- $dbpass = Lastly MySQL must be provided a password to go along with the admin username in order to fully connect
Great the next few lines uses php’s mysql commands and use our variables to connect to your MySQL database. Once you have finished setting up this connection script save it out as a file called “db_cnt.php”.
Now that we have our connection script we can start to write our saveToDatabase.php and openFromDatabase.php files. Start a new blank PHP and add the following:
Ok here we use the PHP function called “include” to include our “db_cnt.php” and open a connection to our database. Then we create a new variable called “$results” and equal it to a mysql_query to our database table called “cms_demo”. This portion here “SELECT * FROM cms_demo” is stating to grab all records from cms_demo data table.
This “mysql_fetch_array” function will use our “$result” query to loop through our data table and locate a particular $row or rather “column”. To do this we setup a third variable called $responseText and a forth variable called $row. Our “$row” variable points to our data table column by name “content” then sets it to our “$responseText” variable.
Save this file as “openFromDatabase.php” and start a new blank PHP document that looks like the following:
Here we again add the use the PHP function “include” to connect to our database. Next we setup a variable called “$currentEdit” and we point it to another PHP function called “mysql_real_escape_string”. The “mysql_real_escape_string” basically runs the data being sent to our database through a stripping process that removes any harmful characters one might use to attack our database for what ever reason. To run our data through this stripping process we have to include our data being sent from our AJAX post function. We use the “$_POST[‘currentEdit’]” to capture the posted data from our AJAX function “currentEdit”.
Next we call another “mysql_query” but this time we are going to use an “UPDATE” to update our data table with the most current edits the user preform’s. To do this we call the “UPDATE” function then point to our data table “cms_demo” then use the “SET” function to insert our stripped data into the column named “content”. For this tutorial and data table we will only have one record in our database that holds our data so we use a “WHERE” function to narrow down our location to an id of 0. If you have multiple records in your data table you can change this “WHERE” id number to what ever row you would in fact like to instant edit.
Save this file out as “save2database.php” and guess what folks you are done!
I really hope this gave my readers some useful insight into the world of internet application developing as well as some great inspiration to run with.