Before you can do anything with your database, you should create a table and set the corresponding fields in it.
Creating a table in PHPMyAdmin is simple. Just type the name, select the number of fields and click the Go button. You will then be taken to a setup screen where you should create the fields for the database.
Alternatively, you can run a MySQL query, which will create the table. The format is:
CREATE TABLE tablename (Fields)
The fields are defined as follows:
fieldname type(length) extra info,
The fields are separated by comma.
For example, if you wish to create a table called Members with 3 fields in it - FirstName, LastName and Age, you should execute the following query:
CREATE TABLE Members(FirstName varchar(15),LastName varchar(15),Age int);
Connect to The MySQL Database
In this Tutorial You Will Find out How to Connect to your MySQL Database
You should establish a connection to the MySQL database. This is an extremely important step because if your script cannot connect to its database, your queries to the database will fail.
A good practice when using databases is to set the username, the password and the database name values at the beginning of the script code. If you need to change them later, it will be an easy task.
$username="your_username";$password="your_password";$database="your_database";
You should replace "your_username", "your_password" and "your_database" with the MySQL username, password and database that will be used by your script.
At this point you may be wondering if it is a security risk to keep your password in the file. You don't need to worry because the PHP source code is processed by the server before being sent to the browser. So the visitor will not see the script's code in the page source.
Next you should connect your PHP script to the database. This can be done with the mysql_connect PHP function:
mysql_connect(localhost,$username,$password);
This line tells PHP to connect to the MySQL database server at 'localhost' (localhost is the MySQL server which usually runs on the same physical server as your script).
After the connection is established you should select the database you wish to use. This should be a database to which your username has access to. This can be completed through the following command:
@mysql_select_db($database) or die( "Unable to select database");
It tells PHP to select the database stored in the variable $database (in our case it will select the database "your_database"). If the script cannot connect it will stop executing and will show the error message:
Unable to select database
The 'or die' part is useful as it provides debugging functionality. However, it is not essential.
Another important PHP function is:
mysql_close();
This is a very important function as it closes the connection to the database server. Your script will still run if you do not include this function. And too many open MySQL connections can cause problems for your account. This it is a good practice to close the MySQL connection once all the queries are executed.
You have connected to the server and selected the database you want to work with. You can start querying the database now.
How to Query Your MySQL Database
Tutorial on How to Execute Commands on Your MySQL Server
There are at least two ways to query a database. One is to enter the command in PHP.
Another way is to define the command as a variable.
In this part of the tutorial we will show the first way. The command will look like this:
mysql_query($query);
The command can be repeated over and over again in the source code. All you need to do is to change the variable.
Here is the complete code that should be used to create a MySQL table in PHP:
<?php$user="username";$password="password";$database="database"; mysql_connect(localhost,$user,$password);@mysql_select_db($database) or die( "Unable to select database");$query="CREATE TABLE tablename(id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,field1-name varchar(20) NOT NULL,field2-name varchar(20) NOT NULL,field3-name varchar(20) NOT NULL,field4-name varchar(30) NOT NULL, field5-name varchar(30)NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";mysql_query($query);mysql_close();?>
Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above.
The next query should fill in the table. Here is a sample one:
$query = "INSERT INTO tablename VALUES ('','$field1-name','$field2-name'
,'$field3-name','$field4-name','$field5-name')";
You can't insert more values than the number of fields you have created with the first query.
How to Display MySQL Table Data Tutorial
Tutorial on How to Display MySQL Data
After you have created the table and entered the data, you will probably need to display it. This is usually done using basic HTML code which invokes a PHP script.
We will start the example from the beginning. We will populate a new database table with data.
The following HTML code will collect the data from the text boxes and pass it to the PHP script:
<form action="insert.php" method="post"> Value1: <input type="text" name="field1-name" /> Value2: <input type="text" name="field2-name" /> Value3: <input type="text" name="field3-name" /> Value4: <input type="text" name="field4-name" /> Value5: <input type="text" name="field5-name" /> <input type="Submit" /></form>
The next thing you need is a new PHP script which will enter the data in the database.
<?php$username="username";
$password="password";$database="your_database";
$field1-name=$_POST['Value1'];
$field2-name=$_POST['Value2'];
$field3-name=$_POST['Value3'];
$field4-name=$_POST['Value4'];
$field5-name=$_POST['Value5'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO tablename VALUES('','$field1-name','$field2-name',
'$field3-name','$field4-name','$field5-name')";mysql_query($query);mysql_close();?>
This script should be saved as insert.php so that it can be called by the HTML form.
Now that you have at least one record in your database, you may want to know how you can output this data using PHP.
The first command you will need to use is the SELECT FROM MySQL statement:
SELECT * FROM tablename
This is a basic MySQL query which will tell the script to select all the records from the tablename table. After the query execution the result will be assigned to a variable:
$query="SELECT * FROM tablename";$result=mysql_query($query);
The whole content of the table is now included in a PHP array with the name $result. Before you can output this data you should change each piece into a separate variable. There are two stages.
The first one is counting the rows. Before you can go through the data in your result variable, you should know the number of the database rows. You could, of course, just type this into your code but it is not a very good solution as the script code will have to be changed every time a new row is added. Instead you can use the command:
$num=mysql_numrows($result);
The $num value will be the number of rows stored in $result. This will be used in a loop to get all the data and display it on the screen.
The second stage is to set up the loop. It will take each row of the result and print the data stored there. In the code below, $i is the number of times the loop runs. In this way all the records are displayed.
$i=0;while ($i < $num) {CODE$i++;}
This is a basic PHP loop and will execute the code the correct number of times. Each time $i will be incremented by one. This is useful, as $i will tell the script which line of the results should be read. As the first line in MySQL output is 0, this will work correctly.
The final part of the output script is to assign each piece of data to its own variable:
$variable=mysql_result($result,$i,"fieldname");
So to take each individual piece of data in our database we would use the following:
$field1-name=mysql_result($result,$i,"field1-name"); $field2-name=mysql_result($result,$i,"field2-name"); $field3-name=mysql_result($result,$i,"field3-name"); $field4-name=mysql_result($result,$i,"field4-name"); $field5-name=mysql_result($result,$i,"field5-name");
You do not need to get the ID field because there is no use for it in the output page.
You can now write a full script to output the data. In this script the data is not formatted when it is printed:
<?php$username="username";$password="password";
$database="your_database";mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tablename";$result=mysql_query($query);
$num=mysql_numrows($result);mysql_close();
echo "<b>
<center>Database Output</center>
</b>
<br>
<br>";
$i=0;while ($i < $num) {$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");
echo "<b>
$field1-name $field2-name2</b>
<br>
$field3-name<br>
$field4-name<br>
$field5-name<hr>
<br>";$i++;}?>
This outputs a list of all the values stored in the database. This will give you a very basic output. It is not useful for a working website. Instead, it would be better if you could format it into a table and display the information in it. Doing the formatting is not complicated. All you need to do is use HTML to print the result by including the variables in the correct spaces. The easiest way to do this is by closing your PHP tag and entering HTML normally. When you reach a variable position, include it as follows:
<? echo $variablename; ?>
in the correct position in your code.
You can also use the PHP loop to repeat the appropriate code and include it as part of a larger table. The final output is:
<html>
<body>
<?php$username="username";$password="password";$database="your_database";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tablename";$result=mysql_query($query);
$num=mysql_numrows($result);mysql_close();?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<td>
<font face="Arial, Helvetica, sans-serif">Value1</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value2</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value3</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value4</font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif">Value5</font>
</td>
</tr>
<?php$i=0;while ($i < $num) {$f1=mysql_result($result,$i,"field1");
$f2=mysql_result($result,$i,"field2");$f3=mysql_result($result,$i,"field3");
$f4=mysql_result($result,$i,"field4");$f5=mysql_result($result,$i,"field5");?>
<tr>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font>
</td>
<td>
<font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font>
</td>
</tr>
<?php$i++;}?>
</body>
</html>
This code will print out table content and add an extra row for each record in the database, formatting the data as it is printed.
How to Select Individual Records From MySQL Table
Learn How to Select Records From Your MySQL Tables
As well as showing the whole database, PHP can be used to select individual records, or records which match certain criteria. To do this you must use a variation of the SELECT query. To display the whole table use the query:
SELECT * FROM tablename
If we just want to select records which have value=1 in the field1-name row we would use the following query:
SELECT * FROM tablename WHERE field1-name='1'
In the same way you could select records based on any field in the database. You can also search in more fields by adding more:
field='value'
sections into the query.
One of the most important things about using PHP and MySQL is to have a PHP/MySQL specialized host. Here are some of the things we at SiteGround are proud of:
- We have long experience in providing technical support for PHP/MySQL-based web sites. Thanks to it our servers are perfectly optimized to offer the best overall performance for most PHP/MySQL applications.
- We offer FREE installation of many PHP/MySQL applications including CMS systems, forums, galleries, blogs, shopping carts and more.
- We provide several versions of PHP (PHP 4.4, PHP 5.0, PHP 5.1, PHP 5.2, PHP 5.2 with Suhosin, PHP 5.3) on a per-folder basis.
- We support both MySQL 4 and MySQL 5. We provide unlimited MySQL databases.
- We develop comprehensive tutorials for the most popular PHP/MySQL scripts and a great variety of articles in our Knowledge Base.
Our PHP hosting package is the best offer on the market - it has the lowest price for the quality and features it includes. Sign up now for our Professional PHP Hosting!
No comments:
Post a Comment