Using PHP and MySQL to Develop a Simple CMS – Version 1


In this article I’ll try to describe how to develop a very simple Content Management System (CMS). I’ve chosen PHP as the server-side scripting language and MySQL as the database management system purely because I think they are fairly easy to use and they do the job very well.

I won’t spend any time describing CMSs, what they are, or why you should or should not use them as there are plenty of excellent articles on this site that describe them perfectly well. I’ll just explain one way of developing one.

This CMS consists of a single web page (index.php) that can have its contents updated by use of a standard form (updatePage.htm). The contents entered via the form are stored in a database, and are accessed and displayed by the web page. Although this CMS is too simple to be of any real use, it could be used as the starting point for a real life CMS solution. In subsequent articles I’ll look at various ways to extend the CMS to make it more useful.

There are four files in this project:

  • cms.sql
  • updatePage.htm
  • updatePage.php
  • index.php


This file creates a database called cms, and creates a table in that database called page. It also loads some initial data into the table. You only need to use this file once.


This web page contains a simple form that can be used to enter the contents displayed by index.php.


This is the form handler – the script that processes the data (entered in updatePage.htm) and inserts it into the database table (page).


This is the web page that displays the data held in the database table.



2. USE cms;

3. CREATE table page (

4. pageID integer auto_increment,

5. contents text,

6. primary key (pageID)

7. );

8. insert into page (pageID, contents) values (‘1’, ‘dummy text’);

Line 1 creates a database called cms in the MySQL database management system.

Line 2 tells MySQL to use the database for the subsequent commands.

Line 3 creates a table in the database.

Line 4 creates a column called pageID, which will contain integers, and which will be automatically incremented as new records are added to the table. As we only have one web page (index.php) in our imaginary website, we will only have one record and therefore one integer: 1. If we added additional pages to the table, they would be automatically numbered (2, 3, 4, etc).

Line 5 creates a second column called contents, which will contain text. This is where the editable contents displayed by index.php will be stored.

Line 6 sets pageID as the primary key, which you can think of as a reference for the table. As we only have one table, which will contain only one record, we won’t make any use of the key. I’ve included it though because it’s good practice to do so.

Line 7 simply closes the bit of code that was started in line 3.

Line 8 inserts some initial data into the table: 1 as the first (and only) pageID, and ‘dummy text’ as the contents of the first record.


(Note that for display considerations, I’ve inserted spaces into the HTML tag names, otherwise they would be processed as HTML code.)



3. Really Simple CMS



6. Really Simple CMS


8. Enter page content:





This is just standard HTML, which probably doesn’t really need explaining. All it does is present a form, the contents of which are sent to updatePage.php when the ‘Update Page’ button is clicked.



This is the form handler, that’s to say, the script that processes the data entered into the form (in updatePage.htm).

Line 1 signifies the start of a PHP script.

Line 2 requests the contents that were posted from the form. We could have written $contents=$_POST[‘contents’]; instead if we had wanted to.

Line 3 connects to the MySQL database server, setting up the host name, which I’ve assumed to be localhost, the database user, which I’ve assumed to be root, and the password needed to connect to the database. I have no idea what this would be for your system so I’ve just written the word password.

Line 4 updates the page table in the CMS database with the new contents.

Line 5 closes the database connection.

Line 6 closes the PHP script.




3. Home Page


5. Home Page




This is the web page that displays the contents from the database. It’s called index.php rather than index.htm because the web page contains PHP code. If the page was called index.htm, the PHP preprocessor, which is part of the web server, would not know that the page contained PHP code, and would therefore not try to process the script part of the page (lines 6 to 13). This would cause the script itself to be displayed in the browser rather than the HTML generated by the script.

Most of the lines in this web page are pretty straight forward and don’t need explaining. Lines 6 to 13 contain the PHP script that extracts the contents from the database and displays (echos) it in the browser.

Installing/Running the CMS

To use the CMS you need to copy the files onto your web server into the area allocated for web pages. Your web server needs to support PHP and MySQL; if it doesn’t, the CMS won’t work.

You also need to use the correct database connection names and passwords (those used in the mysql_connect lines in the PHP scripts).

Exactly how you run the cms.sql file to set up the database and database table will vary from web server to web server so it’s difficult to give precise instructions here. If you have a phpMyAdmin icon or something similar in your web servers control/administration panel you should be able to use that.

Once you’ve set up the database and table, you can simply browse to the updatePage.htm web page and update the database contents. You can then browse to the index.php page to view the updates.


Source link

SEARCH Function and FIND Function in Microsoft Excel


There are two very similar functions in Excel to look for data inside of cells matching parameters that you dictate: SEARCH and FIND. There are so similar, in fact, that one wonders why have two separate functions that perform virtually the identical results and are identical in the construct of the formula. This article will discuss he one, basic difference.

SEARCH Introduction

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function. The format for this function is:


If, for example, the word “alphabet” was in cell C2, and your model needed the location of the letter “a” in that cell, you would use the formula =SEARCH(“a”,C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the location of “b” in the word, the formula would be =SEARCH(“b”,C2,1), and the result would be 6. You can also use search on strings of characters. If, for example, cell F2 contains 1023-#555-A123, the formula =SEARCH(“A12”,F2,1) would yield the 11 as an answer.

FIND Introduction

The FIND function is another way to find a character or string within another cell, and it will return the value associated with the starting place, just like the SEARCH function. The format for this function is:


Using the same example as before, the location of the letter “a” in cell C2 would be discovered using =FIND(“a”,C2,1), and the result would be 1. Looking for “b” in cell C2 would be accomplished be =FIND(“b”,C2,1), resulting in the number 6. Finally, continuing on the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND(“A12”,F2,1) would yield the 11 as an answer. As you can see, up to this point, both methods would give you the same results.

Note: You probably quickly recognized that there are two a’s in the word located in cell C2. By stating the starting point in each of the formulas as 1, we will pick up the first instance of the letter “a”. If we needed to choose the next instance, we could merely have the “start_num” part of the formula to be 2, thus skipping the first instance of the letter and resulting in an answer of 5.

Main Differences

The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. Thus, if you used the formula =SEARCH(“A”,C2,1) (note the capital “A”), the result would still be 1, as in the case before. If you were to use the formula =FIND(“A”,C2,1), you would get #VALUE!. FIND is case sensitive and there is no “A” in the word “alphabet”.

Another difference is that SEARCH allows for the use of wildcards whereas FIND does not. In this context, a question mark will look for an exact phrase or series of characters in a cell, and an asterisk will look for the beginning of the series of characters right before the asterisk. For example, the formula =SEARCH(“a?p”,C2,1) in our alphabet example would yield an answer of 1, as it is looking for an exact grouping of the letter “a” with anything next to it with a “p” immediately after. As this is in the beginning of the word, the value returned is 1. Continuing with the alphabet example, the formula =SEARCH(“h*t”,C2,1) would yield a value of 4. In this instance, the wildcard “*” can represent any number of characters in between the “h” and the “t” as long as there is a string beginning and ending with the two letters you use in the formula. If the formula was =SEARCH(“h*q”,C2,1), you would get #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you would likely err on the side of using SEARCH. Instances where this may not be the case might involve searches involving specific SKUs or names of employees. In my experience, SEARCH has been more helpful in specific financial modeling exercises, but it is helpful to understand the differences in usage and results as you work through your own modeling projects.


Source link

Creating Dynamic Website Content with PHP – MySQL


Fresh website content for your visitors can be of real benefit when attempting to generate repeat traffic. Most webmasters, however, just don’t have enough spare time to frequently update or rebuild their pages manually. If your web site hosting company provides free access to PHP and MySQL, this article will show you how to combine those two open source tools and replace a portion of your websites’ static content with frequently changing dynamic content.

Why do you need dynamic content for your website?

Static pages on a website eventually become “stale” and visitor traffic can fall significantly over time. The drop in traffic can be attributed to these primary factors:

1) The reluctance of search engines to include and display your potentially “out of date” pages in their search results,

2) The finite number of other subject related websites that would be willing to link to your information on a specific topic, and

3) Visitors that learn to view your static website with a “been there, done that” attitude.

Creating and maintaining a web site requires a significant investment in time and resources. Loosing repeat visitors diminishes the value of your investment. Without repeat traffic it is virtually impossible for a website to be a continuing success.

How can you add dynamic content without having to purchase expensive software?

One proven (and easy to implement) method of creating dynamic content for your website is by rotating information on key, higher traffic web pages using PHP with a MySQL database. Rotating content can take the form of a series of rotating articles, a rotating group of product listings, or even a simple “thought for the day”. What is important is that your clients and visiting search engines find new and interesting information each time they visit your website.

As an example of dynamic content creation, we will build a system that rotates information about a group of products on the main page of a hypothetical retail sales web site that markets widgets. Our goal is to present information about a different type or model of widget available for purchase whenever a consumer visits the shopping web site.

Step One: Create a content table to hold your widget data.

There are a couple of options for storing the data to be displayed in your dynamic content rotation. The first option would be to create a new database, or perhaps simply add a table in an existing product database that will hold the information that you wish to display.

Let’s take five theoretical widget products and design a table as follows:


| item | product |


| 1 | Plastic Widgets |

| 2 | Metal Widgets |

| 3 | Wooden Widgets |

| 4 | Rubber Widgets |

| 5 | Stone Widgets |


1-a) Create your table with the following SQL statement:

CREATE TABLE `content_table` (

`item` int(4) NOT NULL auto_increment,

`product` varchar(10) NOT NULL default ”,

KEY `item` (`item`)


This table contains two fields. The first is an item number and the second is a description field that will hold the product name and features. Note: You can add fields to your actual table including: an image URL field, shopping cart direct purchase URL field, product page field, etc.

1-b) Insert the example data into your new table as follows:

INSERT INTO `content_table ` VALUES (1, ‘ Plastic Widgets’);

INSERT INTO `content_table ` VALUES (2, ‘ Metal Widgets’);

INSERT INTO `content_table ` VALUES (3, ‘ Wooden Widgets’);

INSERT INTO `content_table ` VALUES (4, ‘ Rubber Widgets’);

INSERT INTO `content_table ` VALUES (5, ‘ Stone Widgets’);

Once you have completed these two steps you will have a table compete with data to be shown on your website.

Another option would be to utilize your existing product table. If there are hundreds of different models and styles of widgets already in one of the tables in your database, you could utilize the same structure we are learning now to connect directly to that table and display the already existing data.

Step two: Working with your new table:

For dynamic content displays to function there must be a mechanism in place that instructs your web page as to which item should be shown to the visitor. These mechanisms vary in complexity from extremely simple commands to the more complicated use of cookies or IP tracking to determine which item should be displayed.

For this tutorial, we will utilize one of the most effective mechanisms and perhaps the easiest to incorporate. This is the use of a random number generator for deciding which item will be shown.

To create a random number generator using PHP you must first calculate the total number of possible items that you want the system to choose from. In this example we had five items so the maximum number of choices will be 5. The reason we need this number is to limit the random numbers being delivered. If we have five items, we want the number generator to only give us a result of between 1 and 5.

We must now create a variable for our PHP code that will hold our new randomly generated item number as follows:

$mynumber = rand(1, 5);

This little snippet of code will act as the mechanism to “select” a widget product item at random from the five provided in the content table that we created.

If we created 100 different items for your dynamic display instead of just five, you would simply change the “rand (1, 5)” part of the code to reflect the different maximum number. In this case we would change it to “rand (1, 100)” so that the random number generator gives us back a number somewhere between one and one hundred.

We are now ready to extract the randomly selected item’s information from your table so that it can be displayed on your webpage.

You can now connect to your database and query your table to find the data for the item that matches the random number you created, as follows:

$query_content = “SELECT * FROM content_table WHERE item = $mynumber “;

Step three: Displaying your data:

When displaying your data it is important to maintain consistency in presentation size. It is preferable to create a table of specified dimensions (such as “width=400”) and display your results within this table. In this way the page proportions do not have to change with each new item (which can be very confusing for visitors).

Simply display the results just as if these where any other MySQL query using the echo command:

echo $query_content [‘ product ‘];

Every time your page is loaded a different widget product will be selected at random for display on that page.

What else can you do with your dynamic content?

The only limits are within your imagination. By adding a title and meta description tags to your content table, you can alternate the title and search engine description for that page. You can also utilize this system to promote affiliate programs or sponsorship opportunities by rotating affiliate links and banners.

The proper use of dynamic content can bring your website back into favor with search engines and encourage your visitors to return frequently to see what is new.


Source link