www.hope.ac.uk faculty of sciences and social sciences hope php & mysql stewart blakeway fml 213...
Post on 19-Dec-2015
213 views
TRANSCRIPT
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
PHP & MySQL
Stewart Blakeway
FML 213
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
What will we cover
• Basic SQL commands– Inserting Records– Selecting Records– Filtering Records
• Some formatting of controls and displaying of data
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Why
• As part of your assessment you will be expected to
1. Create a link to the SQL Server
2. Select a database
3. Talk to the database (retrieve, add, append, etc)
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Data Types
• MySQL uses all the standard numeric data types.
• Some you will have come across– INT -2147483648 to 2147483647 – TINYING -128 to 127 or 0 to 255– SMALLINT -32768 to 32767 or 0 to 65535– MEDIUMINT -8388608 to 8388607 or 0 to 16777215– BIGINT -9223372036854775808 to
9223372036854775807 or 0 to
18446744073709551615 – FLOAT -10,2 up to 24 places– DOUBLE -16,4 up to 53 places
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Date and Time Types
• DATE - A date in YYYY-MM-DD format• DATETIME - A combination of both the date and the
time, in YYYY-MM-DD & HH:MM:SS
• TIMESTAMP - Similar to DATETIME with hyphens an colons omitted
• TIME - A time in format HH:MM:SS• YEAR(x) - The year, the length is specified
by x.
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
String Types
CHAR (x) Fixed lengthVARCHAR (x) Variable lengthBLOB or TEXT Max of 65535 charsTINYBLOB or Max of 255 charsTINYTEXTMEDIUMBLOB or Max of 255 charsMEDIUMTEXT LONGBLOB or Max of 255 charsLONGTEXTENUM A list with a
max 65535 values
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
MySQL
• In order to complete your assignment you will need to create a database
• The database will be empty, the structure you will need to define
• The database and the tables should be named appropriately
• The fields within the database should be of a suitable type
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Talking to the DATBASE
1. Create a connection with the SQL Server
2. Select the appropriate database
3. Construct the SQL Query
4. Execute the SQL Query
Have you created the DATABASE yet? Although you could use SQL statements to create the database and the tables within the database, a utility called phpMyAdmin offers an GUI to do the same task.
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
1. The Connection
mysql_connect($host,$user,$password)
• host is the SQL server address
• user is the username to login to the database
• password is the password associated with the username
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
1. The Connection Syntax
• Storing and testing the connection
$conn = mysql_connect (“localhost",“root","") or die(mysql_error());
echo $conn;
• Should echo out: Resource id #1 or Resource id #2
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
http://hopelive.hope.ac.uk/computing/
2. Selecting the database
• There could be many databases on the SQL server. You need to ensure you select the correct one:
mysql_select_db(database,connection)
• database is the name of the database
• connection is the connection parameters to connect to the SQL server as seen in the previous slide
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
2. Selecting the database Syntax
• There could be many databases on the SQL server. You need to ensure you select the correct one:
mysql_select_db(“student",$conn) or die(mysql_error());
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
3. Constructing the SQL Query
• To interact with the database you construct standard MYSQL statements
$sql = “CREATE TABLE tablename (
fieldname1 datatype,
fieldname2 datatype
)”;
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
4. Executing the SQL Query
$sql = "CREATE TABLE student(
studentId int not null primary key,
surname varchar (25),
firstname varchar (25)
)";
mysql_query ($sql, $conn);
String Variable
PHP command torun the SQL
The variable thatcontains the SQL
The variable thatcontains the connectionsettings
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Retrieving Error Messages
• To ensure that the SQL works you can retrieve error messages and display them!
$result = mysql_query ($sql, $conn) or die(mysql_error());
echo $result;die is optional for all mysql statements
As is mysql_error
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
http://hopelive.hope.ac.uk/computing/
So far!
• We have connected to the SQL Server• We have selected the correct database• We have created a table called student with
3 fields.• studentId• surname• firstname
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
All together
$conn = mysql_connect (“localhost",“root","");
mysql_select_db(“student",$conn);
$sql = "CREATE TABLE student(
studentId int not null primary key,
surname varchar (25),
firstname varchar (25)
)";
mysql_query ($sql, $conn);
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Inserting DATA
The next step is to insert data into your table.
$sql = "INSERT INTO student VALUES (
‘100100100’,
‘Blakeway’,
‘Stewart’
)";
mysql_query ($sql,$conn) or die(mysql_error());
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Unlikely!
• It is very unlikely that you will enter predetermined data into a database
• This is very useful for testing purposes• More likely you will need to obtain the data
via the user
• How do we achieve this?
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
2 Methods
• Create the form in html and send the data to another file – we have all been doing this already
• Create the form in php and send the data to the same file – it is easier to work with one file rather than switching between two
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
http://hopelive.hope.ac.uk/computing/
if ($_POST[viewed] != "yes")
{
echo “<form method=\"POST\“ action=\"$SERVER[PHP_SELF]\">
<input type=\"hidden\“ name=\"viewed\" value=\"yes\">
</form>”;
}
else{
}
Alternative method. Both the get data and the do data are contained within the one file
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Using Forms
if ($_POST[op]!="viewed"){
echo "<form id=\"form1\" name=\"form1\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">Student ID Number<input type=\"text\" name=\"studID\" /> <br />Student Forename
<input type=\"text\" name=\"studFN\" /> <br />Student Surname
<input type=\"text\" name=\"studSN\" /> <br /><input type=\"hidden\" name=\"op\" value=\"viewed\">
<input type=\"submit\" name=\"submit\" value=\"Add Record\"></form>";
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Processing data from formselse // if it has then add the data{
//set sql string using values as indicated by the form$sql = "INSERT INTO tblstudent VALUES ('$_POST[studID]','$_POST[studSN]','$_POST[studFN]')";
if (mysql_query($sql,$conn)) // is it valid?{echo "Record Added!";}else // if not{echo "Record not Added!";}
}
Why not use die(mysql_error()) ?
Can you spot the error?
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Retrieving Data
• At some point you will want to retrieve the data from the database. Immediately after you insert data for the first time would be a good time!
$sql = "SELECT * FROM student“;
$result = mysql_query ($sql,$conn);
• Where does the data go?Into a RESOURCE
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Getting the data from the RESOURCE
$newArray = mysql_fetch_array($result);
$newArray = array (
“studentId” => “05004343”,
“surname” => “Blakeway”,
“firstname” => “Stewart”
)
The RESOURCE
PHP Function to fetch data from the
RESOURCE
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Moving through multiple records
• Dealing with more than one record!
while ($newArray = mysql_fetch_array($result))
{
$sn = $newArray[‘surname’];
echo $sn . “<br />”;
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Combo Boxes
• As you can see from the last slide each surname is displayed on screen
• What if you wanted to put the names in a big list for the user to select one?
• This is fairly straightforward once it has been thought through
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Overall structure of the page!
if ($_POST[viewed] != "yes")
{ // display the form
// allow user to choose a record
}
else
{
// display what was selected
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Forms!
<form>
<select name=“list”><option value=“surname 1”>
<option value=“surname 2”>
<option value=“surname …”>
</select>
</form>How many surnameswill there be?
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
3 Steps
1. First bit of the form
2. THE LOOPING (SURNAMES or whatever)
3. The last bit of the form
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
The first bit
echo "<form method=\"POST\“ action=\"$SERVER[PHP_SELF]\">
Select a Record to View
<select name=\"sel_SN\">
<option value=\"\">-- Select One --</option>";
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
The complicated bit
while ($newArray = mysql_fetch_array($result))
{
$sn = $newArray[‘surname’];
echo "<option value=\"$sn\">
$sn</option>";
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
The end bit
echo ="
</select>
<input type=\"hidden\" name=\"viewed\" value=\"yes\">
<input type=\"submit\" name=\"submit\" value=\"View Selected Entry\">
</form>";
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Overall structure of the page!
if ($_POST[viewed] != "yes")
{ // display the form
}
else
{
// display what was selected
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Displaying the specific recordor the ELSE section
1. Create a connection to the SQL Server
2. Select the required database
3. Construct the SQL
4. Execute the SQL
5. Extract the required data
6. Display the record
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
3. Constructing the SQL
• Now we have specific criteria for data that needs to be SELECTED from our record set
$sql = "SELECT * FROM student";
$sql = "SELECT * FROM student WHERE surname = '$_POST[sel_SN]'";
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
4. Executing the SQL
$result = mysql_query ($sql, $conn);
A variable to hold the resultsof executingthe SQL
The PHP command to executean SQL statement
The variable containing the connectionsettings
The variable containing SQL statement
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
5. Extracting the required data from the results
while ($newArray = mysql_fetch_array($result))
{
$studID = $newArray[‘studentId’];
$studFN = $newArray[‘firstname’];
$studSN = $newArray[‘surname’];
}
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
6. Displaying the Record
echo $studFN.$studSN.$studID;
or
echo “<table><tr><td>Id Number</td><td>Forename</td><td>surname</td><tr>
<tr><td>$studID</td><td>$studFN</td><td>$studSN</td></tr></table>”;
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
What have we covered?
1. Create a connection to the SQL Server
2. Select the required database
3. Construct the SQL
4. Execute the SQL
5. Extract the required data
6. Display the record
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Class Exercise (time permitting)
• Create a function called nowAdd that will add the data obtained from a form to a database called dbCourse.
• Posted name attributes are: fName, sName• Database table is: tblStudent• SQL server is located at: localhost• SQL Username is: tutor • SQL Password is: mypw
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Spot the errors
$sql = “CREATE TABLE tablename (
fieldname1 varchar(30),
fieldname2 varchar(30),
)
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
Spot the errors
while ($newArray == mysql_fetch($result))
{
$studID => $newArray[‘studentID’];
$studFN => $newArray[‘firstname’];
$studSN => $newArray[‘surname’];
};
www.hope.ac.uk Faculty of Sciences and Social Sciences
HO
PE
What next?
• Independent Study:
http://www.w3schools.com/sql/default.asp
• Begin database design and implementation for assessment