[php coding]
[sweeper / xibalba]
[demogroup homepage in PHP w/ MySQL]
[skill level: intermediate - advanced]
Hello again, and welcome to the PHP Coding Article about creating a homepage for a demogroup. In this article, I'll try to introduce you to the steps needed to create a php-driven homepage for a demogroup. Of course, this article may be used for other kind of homepages, but I thought this is a demoscene mag and therefore, I could focus this on the demoscene a bit. :) I won't go into the graphical design of the page, it's up to you how you want it to be designed.
The scripts I provide here are free to use for everyone, but I don't give any guarantee that they really work and behave the way they're meant to. I neither say that they're perfect or fool-proof. Use them at your own risk!
.oO(Let's start the show)
When creating a homepage, you need to think of what you want to add to it. Usually, you might create something like the following sections:
- news
- members
- productions
- links
And after all, you want it to be easy to administer, so you don't have to change tons of lines of code if you only want to add a new news item, add a production or update the links list. To achieve this, we will be splitting the homepage (from now on site) up into 2 areas. The first one being the normal site, the second one being the administration site. We don't want the whole world having access to the administration part, thus we will secure it. But more on this later on.
.oO(the news)
I'm starting with the news thing. First, you need to think of what you want the news to look like. I've chosen something like the following for myself:
new article out / 06.01.2001
YAY! I've written an Article for Hugi
which you'll read in one of the next
issues of Hugi.
- sweeper
You see that we'll prolly have the following fields in our database table: title, date, text, editor.
I'll also add a field to the database I can say if I want the news item to be displayed or not. I'll call it 'display'. The SQL string to create the table is the following:
CREATE TABLE news (
id int(11) DEFAULT '0' NOT NULL auto_increment,
display set('0','1') DEFAULT '0',
title tinytext NOT NULL,
text text NOT NULL,
date date DEFAULT '0000-00-00' NOT NULL,
PRIMARY KEY (id)
);
Thus, the table will look like this:
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | PRI | 0 | auto_increment | |
| display | set('0','1') | YES | 0 | ||
| title | tinytext | NULL | |||
| text | text | NULL | |||
| date | date | 0000-00-00 |
Now that we have the news table, we'll start to create the script to insert, delete and modify the data in the database. Correctly, we'll use one script only to do everything. For this we'll use $PHP_SELF which points to, like the variable name says, itself. We pass a variable called 'type' in which we state what we want to do. All you need to know about the script is going to be written inside the comments of the script. Read the script carefully, so you understand what's going on.
--- admin_news.php ---
<html>
<head>
<title>News</title>
</head>
<body>
<?
////////////////////////////////////////
// Function to connect to MySQL
////////////////////////////////////////
// This is a function we'll call later on to connect to mysql. So when we
// want to connect to mysql, we simply call db_connect(), and the function
// will try to establish a connection to the database. If it fails, it will
// abort the script, and print out 'unable to connect to MySQL'.
function db_connect() {
$database = 'sweeper'; // the name of the database
$table = 'news'; // the table name (obsolete)
$host = 'localhost'; // the hostname where MySQL lies
$user = 'sweeper'; // the MySQL-user
$pass = 'topsecret'; // and his pass
// Connect to mysql
$connection = mysql_connect($host, $user, $pass) or die("unable to connect to MySQL");
return $connection;
}
// We want the main links to be always displayed
echo "<a href='$PHP_SELF?type=add'>Add News</a> -
<a href='$PHP_SELF?type=del'>Delete News</a> -
<a href='$PHP_SELF?type=update'>Change News</a><br>";
// First we check if we want to add a news item. If this is true, we don't
// need any MySQL connectivity, but only plain HTML for some inputs.
////////////////////////////////////////
// Adding a News Item
////////////////////////////////////////
if ($type == "add") {
// We'll send the input of the form to the same script again
?>
<!-- Again, we send everything to the script. -->
<!-- Thus we don't need to split everything up in several files. -->
<form method="post"><action="<? echo $PHP_SELF ?>">
<!-- The first form is for the title of the news item, it will be -->
<!-- available as $title once we've posted it. This is what the -->
<!-- tag name="title" is for. -->
Title: <input type="text" name="title" size="20"><br>
<!-- The same for the date. Note that the date has to be in form -->
<!-- YYYY-MM-dd otherwise it won't be inserted into the database. -->
<!-- It could be made otherwise with drop down boxes, one for each -->
<!-- part of the month, but this is up to you to code :) -->
Date: <input type="text" name="date" size="8"> <br>
<!-- If you want the news item to be displayed, check the -->
<!-- checkbox here, otherwise uncheck it -->
Display: <input type="checkbox" name="display"><br>
<!-- This is the main News Item, enter your text here. It'll be up -->
<!-- to you to ensure that you don't have any html chars in it that -->
<!-- could mess up your site design... I won't provide everything -->
<!-- here =) -->
News Text: <textarea name="text"></textarea><br>
<!-- In this hidden field, we tell the script what it has to do. -->
<!-- Once posted, the script "knows" that it has to add the -->
<!-- variables into the database. -->
<input type="hidden" name="state" value="addnews">
<!-- Guess what =) Yep.. click the button, and the script tries -->
<!-- to insert the data into the database -->
<input type="submit" value="Add Item">
</form>
<?
}
////////////////////////////////////////
// Deleting a News Item
////////////////////////////////////////
if ($type == "del") {
// Here we use the function we created earlier. We'll need it on some
// other points too. If you take a look at the function, it returns
// the link identifier to MySQL (return $connection in the function).
// We could also assign it to another variable than $connection here
// but I usually do it this way to keep track of my variables
$connection = db_connect();
// With this SQL Query, we select the fields id and title from the
// database. 'order by date' means that we want the output to be
// sorted by date.
$sql = "select id, title from news order by date";
// Now we execute the query. "sweeper" is the database on the host.
$result = mysql_db_query("sweeper",$sql,$connection);
// Now we read out the records we fetched through the above query.
// For each record that gets returned we print the html code below
while ($r = mysql_fetch_array($result)) {
// Once again, we link to ourselves, plus we pass the state delnews to
// the script, which means that we want to delete an entry. We also pass
// its id, which is stored in the array in $r, at the first position.
// If you take a look at the query again, the first field we select is
// 'id', so 'id' is also the first field in the array. We assign the
// array inside the brackets of the while loop.
// The second element of the array contains the title of the news item.
// Instead of accessing the array through the numeric offset, we could
// also use $r["id"] and / or $r["title"], eg. the field names.
echo "Delete item with title: <a href='$PHP_SELF?state=delnews&id=$r[0]'>
$r[1]</a><br>";
}
// We close the connection to MySQL again here. We don't want MySQL to be
// filled up with connections.
mysql_close($connection);
}
////////////////////////////////////////
// Selecting a News Item to update
////////////////////////////////////////
// This function is quite the same as the above one, with the difference that
// we pass the state updatenews instead of delnews.
if ($type == "update") {
$connection = db_connect();
$sql = "select id, title from news order by date";
$result = mysql_db_query("sweeper",$sql,$connection);
while ($r = mysql_fetch_array($result)) {
echo "Update item with title:
<a href='$PHP_SELF?type=updatenews&id=$r[0]'>$r[1]</a><br>";
}
mysql_close($connection);
}
////////////////////////////////////////
// Updating a News Item
////////////////////////////////////////
if ($type == "updatenews") {
$connection = db_connect();
// In this query, we fetch all fields that are stored for the record
// with the id $id.
$sql = "select * from news where id=$id";
$result = mysql_db_query("sweeper",$sql,$connection);
$r = mysql_fetch_array($result);
?>
<form method="post"><action="<? echo $PHP_SELF ?>">
<!-- As you see, I've used the names of the fields to access the arrays, thus
<!-- making it clear which value we display where -->
Title: <input type="text" name="title" size="20" value="<? echo $r["title"] ?>"><br>
Date: <input type="text" name="date" size="8" value="<? echo $r["date"] ?>"> <br>
<!-- For the display value, we use the option in HTML to make a checkbox be -->
<!-- checked or not.
<!-- eg. if in the database the field 'display' contains '1', this means that the -->
<!-- news item is meant to be displayed, so we check the checkbox. Otherwise -->
<!-- we won't print 'checked' and the checkbox will be unchecked. -->
Display: <input type="checkbox" name="display" <? if ( $r["display"] == "1" )
{ echo "checked"; } ?>><br>
News Text: <textarea name="text"><? echo $r["text"] ?></textarea><br>
<!-- Again, we pass some hidden fields. We pass the ID so we know which record -->
<!-- in the database to update. We also unset the variable type here. -->
<input type="hidden" name="id" value="<? echo $r["id"] ?>">
<input type="hidden" name="type" value="">
<input type="hidden" name="state" value="updateitem">
<input type="submit" value="Update Item">
</form>
<?
mysql_close($connection);
}
////////////////////////////////////////////////////////
// Determine whether we want to insert, update or delete
////////////////////////////////////////////////////////
// Here, we decide which case is true. The variable $state either contains
// addnews, delnews, updateitem or nothing. If it doesn't contain anything,
// nothing will happen, and we'll just print out the rest of the HTML code
// otherwise the queries are created.
switch ($state) {
case "addnews":
// When the checkbox to display the news item was checked, the variable
// $display will be set, so we set it to 1, if the variable wasn't set,
// we'll set it to 0.
if (isset($display)) { $display = 1; } else { $display = 0; }
// With this query, we insert all values into the database.
// We leave the first field empty, which is the id. MySQL automatically
// inserts the next free id into the database. (Remember, we've set the
// field 'id' to be the primary key, and to be auto_increment.)
$sql ="insert into news values('','$display','$title','$text','$date')";
// This variable is used further down, in it, we say that we want to do
// some MySQL stuff.
$do_sql = "1";
break;
case "delnews":
// We want to delete the record which contains the id with the
// value of $id
$sql = "delete from news where id=$id";
$do_sql = "1";
break;
case "updateitem":
if (isset($display)) { $display = 1; } else { $display = 0; }
// Now we want to update a record. Thus we tell MySQL which fields we
// want to change. even when only one field was changed, we want to
// update all fields. It's sensless to test which variable was set and
// thus only updating this variable. Again, we have a where clause
// which restricts the update to one single record.
$sql = "update news set display='$display', title='$title', text='$text',
date='$date' where id=$id";
$do_sql = "1";
break;
}
////////////////////////////////////////////
// Insert, Update or Delete into/from table
////////////////////////////////////////////
// We've set this variable above in the switch control-structure,
// so if it contains '1', we want to do some MySQL...
if ($do_sql = "1") {
$connection = db_connect();
$result = mysql_db_query("sweeper",$sql,$connection);
mysql_close($connection);
}
?>
</body>
</html>
--- end of file ---
That's it, the first script is done. The next script will be to display the news items. This will be way less complex than the above one, as it only consists of one query, and then displaying everything.
--- news.php ---
<html>
<head>
<title>News</title>
</head>
<body>
<?
// Here, we don't need the function to connect to MySQL, as we simply want to
// read the records, and nothing else.
$database = 'sweeper'; // the name of the database
$table = 'news'; // the table name
$host = 'localhost'; // the hostname where MySQL lies
$user = 'sweeper'; // the MySQL-user
$pass = 'topsecret'; // and his pass
// connect to mysql
$connection = mysql_connect($host, $user, $pass) or die("unable to connect to MySQL");
// Now we prepare the query. Remember that we have a the field 'display' in
// which we say whether we want a news item to be displayed or not.
// The SQL strings may seem *very* confusing when you take a first look at it,
// but they are really simple. First, we select all fields from the table
// 'news' (select * from $table). We only want to display the news items, where
// the field display contains '1', we don't want to print out the other ones.
// Then we want the output to be ordered by date, the newest ones first, the
// oldest ones last. In case there are two news items with the same date, we
// assume that the one with the higher ID is newer, so we order by id with
// second priority. Finally we only want to limit it to the 10 lastmost entries,
// in order not to fill our page with tons of news only.
$sql = "select * from $table where display='1' order by date,id desc limit 10";
$result = mysql_db_query($database, $sql, $connection);
while ($r = mysql_fetch_array($result)) {
echo "$r['title'] / $r['date']<br>$r['text']<br><br>";
}
mysql_close($connection);
?>
</body>
</html>
--- end of file ---
Yup, that's it, nothing more, nothing less.. Of course you can put everything into a nicely formatted table, it's still up to you. =)
The next part of this tutorial will be about the members. It's going to be at quite an equal level as this bit here, but with some special things... So if you got this far, you'll surely also understand the next part, which is going to be the next article, due to size.
If you want to send me any feedback, feel free to email me at sweeper@horus.ch.
sweeper / xibalba
sweeper@horus.ch / www.xibalba.ch