Database Connection
Selecting Data from the Database
Now I have some data in my database (I added a few more lines using the form), I want to display them on the page. Below the form code in index.php, I added the below…
<ul>
<?php
$sql = mysql_query("SELECT * FROM form_test");
while ($row = mysql_fetch_row($sql)) {
echo "<li>$row[0] $row[1]</li>";
}
?>
</ul>
This has now produced a list of all the entrys that are currently held within the database. I would now like to build that list into a menu that will take the user to an individual page to display the content on it’s own. In retrospect, had I planned to do this from the start, I would have added another column in the table called ‘title’ and used that for the menu. However, I didn’t so the row ID will have to do and just imagine it’s the title!!
<ul>
<?php
$sql = mysql_query("SELECT * FROM form_test");
while ($row = mysql_fetch_row($sql)) {
echo "<li><a href=\"results.php?id=$row[0]\">$row[0]</li>";
}
?>
</ul>
I now have a list on the screen with just the ID of the row going from 1 to 6, each as a hyperlink pointing at ‘results.php?id=’ and the id number. Fantastic! We’re getting somewhere! To build results.php, I’m going to use the $_GET[id] string, but before I do that I’m going to put it into it’s own variable of $id should I need to use it again on the page.
<?php
$id = $_GET[id];
$sql = mysql_query("SELECT * FROM form_test WHERE id=$id");
while ($row = mysql_fetch_row($sql)) {
echo "<h1>$id</h1><p>$row[1]</p>";
}
?>
Excellent! Now to work out what we’re going to do with this new knowledge…
First things First; Connecting to the Database
Ok, now we have written out the rough plan, I need to start learning PHP in order to carry it out. The first thing I intend to do to practice is to create a form with a text input to get data into the database and another page to display that data.
The first thing I did was to create a database in phpmyadmin called test, with a table called form_test with two fields in that; id, and text. The id will auto increment with each new value and the text field is just that - a free text field. SQL to create this is:
CREATE TABLE form_test ( id int(11) NOT NULL auto_increment, `text` text collate latin1_general_ci NOT NULL, PRIMARY KEY (id) )
So now we have a database to play with, how do I connect to it using php?? Following the instructions at php mysql tutorial I created a file called db_config.php as follows:
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'test';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
?>
Going through the above,
- $dbhost - this is where your database is stored. Some hosting companies will either give you the ip address of the database, or it could just simply be ‘localhost’ as it is with mine (I’m doing this on my local machine).
- $dbuser - the username of the database. When using on a live site, never use the username of ‘root’ as this has full privelages, create a new username.
- $dbpass - the password for your chosen username
- $dbname - the name of the database you created.
Now I have the database connection all set up and raring to go, I want to see if I can create a query to put some data into the table. I created a blank HTML page, included the above file in the first line, and then wrote an INSET query…
<?php
include 'db_config.php';
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>
PHP Form Test
</title>
<link rel="stylesheet" type="text/css" href="styles/screen.css">
</head>
<body>
<h1>Hello World!!</h1>
<?php
mysql_query ("INSERT INTO form_test (text) VALUES ('If this works this will be the first entry into the database!!')");
?>
</body>
</html>
I had no idea if this was going to work as I’ve never written a page that connects to a database before! So here goes…
It worked!! The image on the left is a screen grab of phpMyAdmin before I ran the above page, and the image on the left is after.
Fine, so thats great but it’s all well and good having the data being entered into the database from within the php code itself, but it’s not exactly the ideal solution to a CMS - we’re going to need a form to enter the data into the database. For now, I’ll leave that until the next post…