- Add author
- Add source
- Add timeline entry
- Beef up the descriptive tags
- Rename the file
- Move file to SSD
Amy Database Notes
Add to this database
Site Purpose
This site is a Digital Library of all the source material for my writing life. No longer will I be wracking my brain trying to remember where I saw something, or digging through a file system trying to find it again. Imagine an author's file cabinet of source material and writing notes. Mine is a database.
This database serves two functions:
- Catalog of sources
- Timeline of source content
All source text is searchable in the database. All source documents are cataloged in the database and stored in my vault.
Site Origin
This site is my web-based upgrade from my 2016 Microsoft Access database called "Mose". The Access database was an upgrade from my 2008 Microsoft Word document called "Mose", which held my original notes for "Mose" in date order with sources cited as footnotes.
Microsoft Access allowed me to store my data in Tables, create easy Forms to enter and update data, and also create Reports to display the data in unique views for unique functions. Most importantly, I could view my data on a filterable timeline. I could store entire books in that database, where I could not in a MS Word document.
However, Access did not allow me to host my reports online for public use. It also kept all the data and structure files in one huge .mdb file, which does not allow for growth or safe backups.
This MySQL database is built as a website so that I can search and display the data however I want to. After much research and college courses, I decided to use these uber-popular, open-source products to build my site:
| Function | Product | Description |
|---|---|---|
| Database Server | MySQL | Pulls the linear stored data into tables, which can be manipulated with the MySQL command language. |
| Database Interface | phpMyAdmin | Web-based portal to display MySQL tables and commands in a browser. |
| Web Server | Apache | Must be actively running to display PHP files correctly. |
| Web Design Language | PHP | When web files have the .php extension, browsers refer to the public PHP command libary to render the page. PHP differs from HTML in that it includes commands to access and display data from a database. |
| Local host package | XAMPP | A downloadable packaging containing the MySQL and PHP programming languages, the Apache and MySQL servers, and the phpMyAdmin interface. XAMPP and both servers must be running for the site to work. This drains a full laptop battery in 30 minutes, so it must be plugged in. |
| Code editor | Visual Studio (VS) Code | Code editor by Microsoft with more helpful features than Notepad++ (like a color picker) and much simpler than NetBeans. VS Code mimicks the files it finds in Windows Explorer. |
Site Location
To work with XAMPP on my local server, I must keep the site files in XAMPP's "htdocs" directory. If I ever want to move the site, I can tell XAMPP or the new server where the files are.
Site Structure
To keep each page of code simple, I separated the visible page from the database code page. This division is maintained by storing all visible pages in the "pages" directory, and all code pages stored in the "run_code" directory. The "run_code" directory simulates WordPress's "incluces" folder. The "_run" pages on this site are pulled in by the "include" command on their parent visible page. The visible pages are in a folder called Pages. Each visible page's code is in another php page by the same name, with the word "_run" appended to the file name. The code pages are in
| Pages & Parts | Code Page | PHP | SQL |
|---|---|---|---|
| pages/amyreadme.php | n/a | ||
| pages/leftnav.php | n/a | ||
| pages/sidebar.php | not used? | ||
| All code pages include | run_code/database.php | Connect to the database:
$connection = mysqli_connect($db_server, $db_user, $db_password, $db_name) |
|
| pages/projects.php | run_code/projects_run.php | Count the projects:
$result_count = mysqli_query($connection, $sql_count); while ($row = mysqli_fetch_assoc($result_count)) {echo...} |
SELECT COUNT(*) AS count FROM projects; |
| List the projects:
$result_list = mysqli_query($connection, $sql_list); while ($row = mysqli_fetch_assoc($result_list)) {echo...} |
SELECT * FROM projects; | ||
| pages/project.php | Display project details:
$result = mysqli_query($connection, $sql); $row = mysqli_fetch_assoc($result); echo {...} |
SELECT * FROM projects WHERE project_id= $_GET[project_id]; | |
| pages/project_add.php | run_code/project_add_run.php | Add a project:
if ($_SERVER["REQUEST_METHOD"] == "POST") {$project_title = $_POST["project_title"]}; mysqli_query($connection, $sql); |
INSERT INTO projects (project_title) VALUES ('$project_title'); |
| pages/authors.php | run_code/authors_run.php | Count all authors:
$result_count = mysqli_query($connection, $sql_count); while ($row = mysqli_fetch_assoc($result_count)) {echo...} |
SELECT COUNT(*) AS count FROM authors; |
| List all authors:
$result_list = mysqli_query($connection, $sql_list); while ($row = mysqli_fetch_assoc($result_list)) {echo...} |
SELECT * FROM authors ORDER BY author_name;"; | ||
| run_code/author_add_run.php | if ($_SERVER["REQUEST_METHOD"] == "POST") {$author_name = $_POST["author_name"];}
mysqli_query($connection, $sql); |
INSERT INTO authors (author_name) VALUES ('$author_name'); | |
| pages/author.php | run_code/author_run.php | $result = mysqli_query($connection, $sql);
while ($row = mysqli_fetch_assoc($result)) {echo...} |
SELECT * FROM sources JOIN authors ON sources.author_id = authors.author_id WHERE sources.author_id= $author_id; |
| pages/sources.php | run_code/sources_run.php | while ($row = mysqli_fetch_assoc($result_count)) {echo...};
while ($row = mysqli_fetch_assoc($result_list)) {$source_id = $row['source_id'];echo |
SELECT * FROM sources JOIN authors ON sources.author_id = authors.author_id ORDER BY sources.date_published;
SELECT COUNT(*) as count FROM sources; |
| pages/sources_by_author.php | run_code/sources_by_author_run.php | $result = mysqli_query($connection, $sql);
$authors = array(); foreach ($result as $row) {...} if (array_key_exists($author_id, $authors)) {array_push($authors[$author_id], $row);} foreach($authors as $arrayOfSourceRows) {...} |
Josh helped me nest a query here. I still don't understand it.
[SELECT * FROM sources LEFT OUTER JOIN authors ON sources.author_id = authors.author_id] |
| pages/source.php | run_code/source_run.php | $result_list = mysqli_query($connection, $sql_list);
$result_count = mysqli_num_rows($result_list); echo $result_count . " timeline entries"; while ($row = mysqli_fetch_assoc($result_list)) {echo...} |
SELECT * FROM sources JOIN authors ON sources.author_id = authors.author_id WHERE source_id=$source_id; |
| pages/source_add.php | run_code/source_add_run.php | ||
| pages/source_update.php | run_code/source_update_run.php | ||
| pages/timeline.php | run_code/timeline_run.php | ||
| pages/timeline_filtered.php | run_code/timeline_filtered_run.php | ||
| pages/timeline_entry.php | run_code/timeline_entry_run.php |
Site Functionality
| Source Catalog | Timeline |
|---|---|
| Sources can be books, magazines, articles, web pages, emails, brochures, databases, etc. All the sources I collect are digitized and stored in separate PDF files. This database catalogs them and links to them. I can click on a source title in my catalog and see all of the plain text from that source. I can click on its external link and see the PDF version of that source. I suppose this is the same functionality of Windows Explorer. However, I want all my materials to be in one view to save me from brain fragmentation. |
All source material is chopped into datestamped chunks called Timeline Entries. They can be sorted by date, or sorted by the order they appear in their original document. |
Site Code
Each visible page that needs to access the database is supported by one or more code pages. I think WordPress keeps all the supporting functions in one file called the Functions file. I keep them separate and name them to match the page they support.
Get Data
The PHP command to pull data is mySQLi with the arguments of the connection variable and the SQL variable. You store the result in a variable to use in the next step:
$result=mySQLi_query(connection,sql);
Display Data
The PHP command to display data is a loop through the result of the query. You store the output in a variable $row:
while ($row = mysqli_fetch_assoc($result)) {echo...}