Linking a Webpage to a Database
Linking a Webpage to a Database
10.3.3.3 link a webpage to a database
10.3.3.3 link a webpage to a database
SQL statement SELECT * FROM customers
SQL statement
SELECT * FROM customers
Go and obtain from the database
every field
FROM the
customers table
Separating the database connection
Separating the database connection
It is worth separating the database connectivity from our scripts and placing it in a separate file.
It provides a convenient means of moving your scripts from one database platform to another.
Example: Separating the database connection //
Example: Separating the database connection
// File: database2.php
$strLocation = "Home";
//$strLocation = "Work";
if ($strLocation == "Home") {
$dbLocalhost = mysql_connect("localhost", "root", "")
or die("Could not connect: " . mysql_error());
mysql_select_db("glassesrus", $dbLocalhost)
or die("Could not find database: " . mysql_error());
} else {
$dbLocalhost = mysql_connect("localhost", "username", "password")
or die("Could not connect: " . mysql_error());
mysql_select_db("anotherdatabase", $dbLocalhost)
or die("Could not find database: " . mysql_error());
}
?>
$strLocation could be easily switched between ‘Home’ or ‘Work’
Viewing a whole record To view the whole record returned from mysql_query(), we need another function
Viewing a whole record
To view the whole record returned from mysql_query(), we need another function...
resourceRecords – resource identifier returned from mysql_query().
it returns an array containing the database record.
array = mysql_fetch_row(resourceRecords)
Example: Displaying all customer records require_once("database2
Example: Displaying all customer records
require_once("database2.php");
$dbRecords = mysql_query("SELECT * FROM customers", $dbLocalhost)
or die("Problem reading table: " . mysql_error());
while ($arrRecord = mysql_fetch_row($dbRecords)) {
echo "
" . $arrRecord[0] . " ";
echo $arrRecord[1] . " ";
echo $arrRecord[2] . " ";
echo $arrRecord[3] . "
}
?>
The function returns false when the last record is returned; thus, stopping the loop.
Note, however, that the fields are referred to by using numbers – not very easy to read and mistakes can be introduced.
Limiting the records returned SELECT
Limiting the records returned
SELECT Surname FROM customers
Retrieves only the Surname field from the table customers
Limiting the records returned SELECT *
Limiting the records returned
SELECT * FROM customers LIMIT 3,4
Select a certain number of records form a table
3 is the starting row
4 is the number of records to be selected after the starting row
Searching for matching records
Searching for matching records
SELECT * FROM customers WHERE Title=‘Mr’
The WHERE attribute specifies what to search for within the database records.
in this example, only records which have a title of ‘Mr’ will be returned.
Searching for matching records
Searching for matching records
SELECT * FROM customers WHERE Title=‘Mr’ OR Title=‘Mrs’
The WHERE attribute specifies what to search for within the database records.
in this example, only records which have a title of ‘Mr’ or ‘Mrs’ will be returned.
we can also use AND and OR to formulate more sophisticated conditions.
Searching for matching records
Searching for matching records
SELECT * FROM customers WHERE Title=‘Mr’ AND Surname=‘Smith’ OR Title=‘Mrs’
The WHERE attribute specifies what to search for within the database records.
in this example, only records which have a surname of ‘Smith and title of ‘Mr’ or the title of ‘Mrs’ will be returned.
we can also use AND and OR to formulate more sophisticated conditions.
Sorting records The ORDER BY attribute can be used to sort the order in which records are obtained
Sorting records
The ORDER BY attribute can be used to sort the order in which records are obtained.
the ORDER BY attribute is followed by the data field on which to sort the record
DESC or ASC – from high to low, or from low to high
SELECT * FROM cutomers ORDER BY Surname DESC
Example15-12.php
Accessing Multiple Tables // File: example15-13
Accessing Multiple Tables
// File: example15-13.php
require_once("database2.php");
$dbRecords = mysql_query("SELECT * FROM customers WHERE Title = 'Mrs'", $dbLocalhost)
or die("Problem reading table: " . mysql_error());
echo "
Customers:
";while ($arrRecords = mysql_fetch_array($dbRecords)) {
echo "
" . $arrRecords["Id"] . " ";
echo $arrRecords["Title"] . " ";
echo $arrRecords["Surname"] . " ";
echo $arrRecords["Firstname"] . "
}
//...continued...
Accessing Multiple Tables //continuation
Accessing Multiple Tables
//continuation...
$dbRecords = mysql_query("SELECT * FROM products WHERE Name = 'Wine Glass'", $dbLocalhost)
or die("Problem reading table: " . mysql_error());
echo "
Products:
";while ($arrRecords = mysql_fetch_array($dbRecords)) {
echo "
" . $arrRecords["Id"] . " ";
echo $arrRecords["Name"] . " ";
echo $arrRecords["Description"] . " ";
echo $arrRecords["Quantity"] . " ";
echo $arrRecords["Cost"] . "
}
?>
© ООО «Знанио»
