John's Bread Crumbs: LAMP - Part 3 - Retrieve all records

My notes on how to create a LAMP form using PDO and MySQL:

Part 3: Retrieve all records

This is part 3 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Create an index file:

Create an index.html file for convenience. Contents:

<html>
<head>
<title>Main Menu</title>
</head>
<body>
 
<a href="display_all.php">Display all</a> records</a>
 
<a href="lookup.php">Look up</a> a record</a>
 
<a href="form1.php">Insert</a> a record</a>
 
</body>
</html>

Create a PHP file to display all records:

Contents of display_all.php:

<?php // display_all.php

echo <<<_END

<html>
<head>
<title>Display_All Test</title>
</head>
<body>
 

_END;

echo ' ';

// Retrieve database connection info
require_once '/var/forms/login_reader.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];

// Now try to connect to the database and retrieve records

try {

// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.

$statement = $pdo-> prepare( "

 select Lastname, Firstname
 from Form1
 order by Lastname asc, Firstname asc
 LIMIT 20

 " );

// Now that the SQL statement is prepared, execute it

$statement->execute();

// If we found matching records, prepare to display them

if ($statement->rowCount() > 0)
{
// Display each record
foreach ($statement as $row)
{
echo $row["Lastname"];
echo ", ";
echo $row["Firstname"];
echo " ";
}
echo " ";
echo "<hr>";
}

// What do we do if no matching records are found?
else

{
echo 'No records found. ';
}

}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

echo ' ';
echo ' ';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo ' ';

echo '</html>';
?>

Thursday, June 21, 2018

LAMP - Part 3 - Retrieve all records