Obtaining User or Website Data

Databases and SQL
- Databases have become the foundation of many web pages.
- Databases use the Structured Query Language (SQL) for
  - Creation
  - Access
  - Modification.
- This is a deep field, with people earning a PhD in database.
- Databases are table based
  - A table is an array of records.
  - A table generally has a series of columns, called fields
  - Each field has a unique name.
  - ```
  CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       userName VARCAR(20),
       firstName VARCHAR(40),
       lastName  VARCHAR(40),
       password  VARCHAR(40),
       accesTime DATE,
       balance   FLOAT,
       ...
  );
  
  DROP TABLE users; 
```
- Data is added to a table with the insert command
  - ```
  INSERT INTO users (userName, password, balance) VALUES 
      ("UserBob", "bobsPass", 0.0); 
```
- Data is searched with a SELECT statement and updated with a UPDATE
  - ```
  SELECT balance FROM users WHERE userName ="Bob" and password = "bobPass";
  UPDATE users SET balance=0.0 WHERE userName = "Bob"; 
```
- There is much more but this will do.

PHP and Databases

A common method of using databases on the web is via PHP
This has a library that allows easy access to a database.
Queries are built as normal,

But are submitted through a procedure call

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstName, lastName FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstName"]. " " . $row["lastName"]. "
";
  }
} else {
  echo "0 results";
}
$conn->close();

Finally Interacting with html

The following code could be used with a web page

$input_uname = $_GET['firstName'];
$input_pwd = $_GET['password'];
$hashed_pwd = sha1($input_pwd);
...
$sql = "SELECT id, firstName, balance, password
        FROM users
        WHERE firstName='$input_uname'
              and password='$hashed_pwd'";
$result = $conn -> query($sql);