How to Create a Website Using PHP & MySQL
PHP and MySQL are common technologies to use within a website, and the key skills required are not difficult to learn. Even if you have no programming knowledge, you can learn how to use PHP and MySQL by focusing on one task at a time and building on your skills as you go along. The details of your MySQL database and PHP code will depend partly on what the purpose of your website is, but there are basic steps that will apply whatever your goals happen to be.
Other People Are Reading
Instructions
-
-
1
Design and build your MySQL database. Determine what tables you want your database to contain, as well as what columns these tables will have. If your Web host provides the phpMyAdmin interface, you can build your database without writing any SQL code. If you do not have access to this tool, you can create your database using an SQL script run through the MySQL system within your Web hosting account. The following is a sample SQL table script:
CREATE TABLE product
( product_id int,
product_title varchar(25),
product_desc varchar(100) )
-
2
Connect to your database within your website pages. Create each PHP page by opening a new file in a text editor and saving it with ".php" extension, such as "mypage.php", by entering this example code:
<html><head></head><body>
<?php
//connect and choose the database
mysql_connect("localhost", "insert_username", "insert_password");
mysql_select_db("insert_database");
?>
</body></html>
Edit the code to suit your database name, username and password. The code also includes the outline of an HTML page, with the PHP code contained within it.
-
-
3
Query your database to access the data within it. After your connection code and before the closing "?>" PHP tag, insert a database query as follows:
//build the query as a string
$product_query = "SELECT * from product";
//execute the query and get results
$product_result = mysql_query($product_query);
//loop through the result
while($product_row = mysql_fetch_array($product_result))
{
//data output goes here
}
Each time the loop iterates, your code can access a new row in the product table. Alter the code to suit your own database.
-
4
Create HTML structures to display your data within your website. To display the contents of a database table, you need to include the data within HTML, as in this example -- inside the while loop, after "data output goes here":
echo "<div>";
echo $product_row['product_title']."<br/>";
echo $product_row['product_desc'];
echo "</div>";
This code simply outputs the value of the title and description columns in the product table as HTML elements. Upload your PHP page to your server and browse to it in a Web browser to test that it works.
-
5
Release server resources following your database query processing. When your PHP pages are viewed, the code is run on the server as a program. When you query a database, the results are held in memory, so it is more efficient to let the server know you are finished with these resources once your code has executed:
mysql_free_result($product_result);
This practice is particularly useful if you have scripts containing multiple queries, one after another.
-
1
Tips & Warnings
Try using Entity Relationship modeling to create a more efficient and reliable database design.
Avoid database designs in which the same data items are stored in more than one place. This reduces efficiency.
References
Resources
- Photo Credit Comstock/Comstock/Getty Images
