2Webpage to a Database_did_mat2_1 variant

  • pptx
  • 09.05.2020
Публикация на сайте для учителей

Публикация педагогических разработок

Бесплатное участие. Свидетельство автора сразу.
Мгновенные 10 документов в портфолио.

Иконка файла материала 2Webpage to a Database_did_mat2_1 variant.pptx

Linking a Webpage to a Database

10.3.3.3 link a webpage to a database

SQL statement

SELECT * FROM customers

Go and obtain from the database
every field
FROM the
customers table

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

// 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...

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.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 Surname FROM customers

Retrieves only the Surname field from the table customers

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

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

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

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.

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.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...

$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"] . "

";
}
?>