Transcript phpmyadmin - Bath College Moodle

Creating a simple database
This shows you how to set up a
database using PHPMyAdmin
(installed with WAMP)
Stack System
• In order to develop a web page which runs off a back end data base you
will need to have a stack system in place, e.g.
PHP
MySQL
Apache
Windows 7
scripting language
database
web server
operating system
• It is possible to create a stack on a standalone PC or server by installing
WAMP Server (freely available as a download); WAMP works on top of
Windows. WAMP has Apache web server, MySQL database, and PHP
Download WAMP
• Download WAMP server (www.wampserver.com/en/)and install. By
default it places a folder www inside the wamp folder on the C: drive
Make sure WAMP is running
• There should be a green W icon in the notification tray in the
lower right of the screen. A green icon means the WAMP
server is running.
• If it is red it is turned off.
• If it is orange, there may be a problem (e.g. another server
already running, an application using the port).
• You can also run WAMP server by choosing Programs >
WampServer > Start WampServer
phpMyAdmin
• Open a web browser and type localhost (the default name for a home
web server URL address). You should come up with a screen:
phpMyAdmin
• Click phpmyadmin
• The phpmyadmin database
screen should appear
• The first time you use it,
the default username is
root, with no password
• Enter the username root
and click Go
phpMyAdmin
• The MySQL database should open.
• Click the databases tab
• Enter simpledata for the name of the new database and click Create
phpMyAdmin
• Decide on the database structure: it is usually best to sketch this.
• Data is contained in a table called feedback
• Use the sketch below (an example):
• The example table has 3 columns, each column representing a field name
• The rows are the records representing the data (examples shown)
phpMyAdmin
• A list of databases appears with simpledata in the list
• Create a table by clicking the name of the database (simpledata)
phpMyAdmin
• Type a name for the table: feedback
• Enter 3 as the number of columns (fields)
• Click Go (on the far right of the window)
phpMyAdmin
• The table structure should appear like below:
• The 3 columns are now represented by the 3 rows now
phpMyAdmin
• Enter the names for the first column. This represents the fields
• Enter the Type and Length/Values, then click Save (lower right):
phpMyAdmin
Some common types include:
• INT = Integer (a number without a decimal point)
• CHAR = Characters (can hold text up to a specific length)
• VARCHAR = Variable Length Characters (a text field that is not a fixed
width).
• TEXT = For holding large amounts of text.
• DATE = Can only hold dates.
• DATETIME = Can hold both a date and a time.
phpMyAdmin
• You should see the name feedback as the name of the table, and
simpledata as the name of the database
• To add data click Insert along the row of tabs
phpMyAdmin
• Add some values, e.g.
• Click Go
phpMyAdmin
• Set one of the rows as a primary key. Click Primary on the row name
• This sets the name as the primary key. The primary key of a table
uniquely identifies each record in the table. Every database table should
have one or more columns designated as the primary key.
• When click, the primary key looks greyed out
phpMyAdmin
• Click Browse to view the data in the table:
Accessing the database using PHP
• The next step can be done in Dreamweaver
Accessing the database using PHP
• Before we can access data in a database, we must open a connection to
the MySQL server.
• In PHP, this is done with the mysqli_connect() function.
Syntax:
mysqli_connect(host,username,password,dbname);
The details you are using (from our initial plan):
simpledata
mysqli_connect(“locahost”,”root”,””,”simpledata”);
Accessing the database using PHP
• Create a new PHP file, e.g. in Dreamweaver.
• Enter some PHP script. The following opens the connection to the
server and database simpledata.php
<?php
// Create connection
$con=mysqli_connect("localhost","root","","simpledata");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " .
mysqli_connect_error();
}
?>
• An If statement checks for any errors and displays an error message if
the connection fails.
• No error messages mean it works!
Accessing the database using PHP
• PHP script created in Dreamweaver.
Presenting a database using PHP
•
•
The SELECT statement is used to select data from a database:
Add the following PHP
<?php
$result = mysqli_query($con,"SELECT * FROM feedback");
while($row = mysqli_fetch_array($result))
{
echo $row['name'] . " " . $row['rating'];
echo "<br>";
}
mysqli_close($con);
?>
• The data from the table feedback is returned by the
mysqli_query() function and stored in the $result variable.
• The mysqli_fetch_array() function returns the first row from
the table as an array variable &row.
• The while loop allows mysqli_fetch_array() to return the next
row in the recordset. The while loop loops through all the rows.
• To echo the value of each row, we use the PHP $row array
variable ($row['FirstName'] and $row['LastName']).
Presenting a database using PHP
• The source code and live view will look similar to this
Preview web page in browser
• You have used MySQL (Structured Query Language) with PHP to access
a database.
• If you open the page in a browser and view source (right click the web
page) you should see something like this:
• Notice your code entered in Dreamweaver is not shown to the end user
• This helps with security, as the database details are not shown
Add HTML tags (elements)
• When viewing source the HTML is very basic.
• Try adding HTML elements (tags), e.g. <HTML>, <BODY>, <TITLE>,
<HEAD>, etc.
• \n is an escape character and allows line breaks in the code
Edit Database
• Add new records to the database in PHPmyAdmin
• Test the output in Dreamweaver
Edit Database
• Click Browse to see added rows:
Back to Dreamweaver
• Refresh the page in Dreamweaver
Editing MySQL
• You can change the PHP and edit the MySQL code to be more selective
in the process.
• For more info on MySQL, check
http://www.w3schools.com/php/php_mysql_intro.asp
E.g. to select from database only records where the name is “Sam Lowry”
change the line
$result = mysqli_query($con,"SELECT * FROM feedback");
To
$result = mysqli_query($con,"SELECT * FROM feedback WHERE
name=‘Sam Lowry'");
Editing MySQL
MySQL
• This is the basics of using MySQL.
• Many websites use MySQL, including Google search engine.
• MySQL is freely downloadable, light and powerful, and simple to use
once you know it more.
• Like PHP there are a lot of help forums and web pages providing support
and guidance.
• Many Content Management Systems (CMS), e.g. WordPress, use PHP
with MySQL
• Try finding out more about MySQL. Where else is it used?