Simple website with PHP and MySQL

**ChimP** · 25-07-08, 02:55 AM

Hello.

I think I'll be the first to post here, with a simple tutorial to make a PHP-MySQL-based website.

So here's our config file, called "config.php":

PHP Code:


<?php

$sql_host = "localhost";
$sql_user = "root";
$sql_pass = "P@ssw0rd";
$sql_db   = "test";

?>

Then we'll call it in index.php:

PHP Code:


<?php

include_once("config.php");

?>

Now we have a config file, and included it into our main file.
The next thing we're going to do, is to connect to MySQL. I know there's no tables to use yet, but that will come. ^^

Your code should look like this now:

PHP Code:


<?php

include_once("config.php");

mysql_connect($sql_host, $sql_user, $sql_pass) or die("Could not connect to MySQL!");
mysql_select_db($sql_db) or die("Could not select database!");

?>

If you haven't got any error messages, we're able to continue.
Now we'll create our tables, and add some example data.

Execute this code to MySQL:

Code:

CREATE TABLE `content` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
);
INSERT INTO content VALUES (1, 'Test 1', 'This is some example text of the first test-page.');
INSERT INTO content VALUES (2, 'Test 2', 'This is some example text of the second test-page.');

Now we'll start to grab our data.

PHP Code:


<?php
// Get our config vars.
include_once("config.php");

// Connect to MySQL and select the database to use.
mysql_connect($sql_host, $sql_user, $sql_pass) or die("Could not connect to MySQL!");
mysql_select_db($sql_db) or die("Could not select database!");

// Make sure the id we get is valid, so we don't get some invalid queries
if(isset($_GET['id']) && !empty($_GET['id']) && $_GET['id'] > 0) {
    $id = intval( (int) $_GET['id'] );
} else {
    // Else just use id 1
    $id = 1;
}

// Select the column, and escape the id for more security
$sql = "SELECT * FROM content WHERE id = '" . mysql_escape_string($id) . "'";

// Query the database
$q = mysql_query($sql);

?>

Now we got the data inside $q. The thing we need to do now, is to get the data out to our website. And that's what I'm going to do now.

PHP Code:


<?php
// Get our config vars.
include_once("config.php");

// Connect to MySQL and select the database to use.
mysql_connect($sql_host, $sql_user, $sql_pass) or die("Could not connect to MySQL!");
mysql_select_db($sql_db) or die("Could not select database!");

// Make sure the id we get is valid, so we don't get some invalid queries
if(isset($_GET['id']) && !empty($_GET['id']) && $_GET['id'] > 0) {
    $id = intval( (int) $_GET['id'] );
} else {
    // Else just use id 1
    $id = 1;
}

// Select the column, and escape the id for more security
$sql = "SELECT title, text FROM content WHERE id = '" . mysql_escape_string($id) . "'";

// Query the database
$q = mysql_query($sql);

// Check for a valid page id
if(mysql_num_rows($q) != 1) {
    $title = "404 - Not found";
    $text  = "The requested page could not be found.";
} else {
    // Store the data into $title and $text
    list($title, $text) = mysql_fetch_array($q);
}

// Free memory from the query
mysql_free_result($q);

// Close connection to MySQL
mysql_close();

?>

And here we go! Now we got the data from MySQL, freed the memory, and closed the connection.
Now we just need to output it.

Here's the data inside some basic HTML:

PHP Code:


<html>
<head>
<title>My First PHP-MySQL-Website</title>
</head>
<body>
<table width="100%" height="100%">
    <tr>
        <td align="center" valign="middle">
            <table width="600">
                <tr>
                    <td><h1><?php echo $title; ?></h1></td>
                </tr>
                <tr>
                    <td><?php echo $text; ?></td>
                </tr>
            </table>
        </td>
    </tr>
</table>
</body>
</html>

And here it is, all put together:

PHP Code:


<?php
// Get our config vars.
include_once("config.php");

// Connect to MySQL and select the database to use.
mysql_connect($sql_host, $sql_user, $sql_pass) or die("Could not connect to MySQL!");
mysql_select_db($sql_db) or die("Could not select database!");

// Make sure the id we get is valid, so we don't get some invalid queries
if(isset($_GET['id']) && !empty($_GET['id']) && $_GET['id'] > 0) {
    $id = intval( (int) $_GET['id'] );
} else {
    // Else just use id 1
    $id = 1;
}

// Select the column, and escape the id for more security
$sql = "SELECT title, text FROM content WHERE id = '" . mysql_escape_string($id) . "'";

// Query the database
$q = mysql_query($sql);

// Check for a valid page id
if(mysql_num_rows($q) != 1) {
    $title = "404 - Not found";
    $text  = "The requested page could not be found.";
} else {
    // Store the data into $title and $text
    list($title, $text) = mysql_fetch_array($q);
}

// Free memory from the query
mysql_free_result($q);

// Close connection to MySQL
mysql_close();

?>

<html>
<head>
<title>My First PHP-MySQL-Website</title>
</head>
<body>
<table width="100%" height="100%">
    <tr>
        <td align="center" valign="middle">
            <table width="600">
                <tr>
                    <td><h1><?php echo $title; ?></h1></td>
                </tr>
                <tr>
                    <td><?php echo $text; ?></td>
                </tr>
            </table>
        </td>
    </tr>
</table>
</body>
</html>

Done! You now have a very simple PHP-MySQL-Website.

You add more pages through MySQL, as I did earlier in this tutorial.

You change page through the URL's query-string, e.g "index.php?id=2", "index.php?id=7", "index.php?id=727", and so on.

Please leave comments/suggestions, then I might add more to this soon.

-ChimP